Global Temporary Table private statistics

Abstract

There is one Oracle running system launching simultaneously 49 sessions doing the same business for distinct partitions and distinct ranges of employees so that there will never be any collision in this process. However, this application uses bind variables and is, therefore, confronted to the classical performance threat of sharing the same execution plan between a set of bind variables not necessarily dealing with the same volume of data. This application was upgrading from 11gR2 to 12cR1. I was then asked to find a solution so that execution plans will not be shared between those 49 sessions.

I know that Adaptive Cursor Sharing has been implemented for such kind of situations. But I know also that the transition from a bind sensitive to a bind aware cursor depends on a combination of executions done at different bind variable values which makes it very difficult to guaranty each execution will have its proper optimized execution plan. So, I immediately ruled out this option. However, when I looked at one of those shared execution plans, I realized that those 49 sessions are extensively using global temporary table (GTT). This observation gave me an idea which reveals later to be very useful: use the cursor invalidation introduced by the new 12c GTT SESSION PRIVATE Statistics to force the CBO hard parsing a new execution plan for each of the 49 sessions.

Session PRIVATE statistics

Very often the Cost Based Optimizer does a wrong cardinality estimation when visiting Global Temporary Tables (GTT). This is particularly true when multiple sessions are visiting the same GTT but using different number of rows per session. In this case, sharing GTT statistics between sessions leads, generally, to a performance pain. Dynamic sampling at its default value might also reveal to be not sufficient to help the CBO in this situation. The application locks the statistics of the 11gR2 temporary tables used during the 49 sessions, so that it becomes impossible to see one session propagates its temporary table statistics to all other sessions. But this was before 12c. Starting from 12c, Oracle introduces a global statistics preference which makes possible to have session-private statistics for temporary tables:

SQL> @getPrefs
Enter value for preference: GLOBAL_TEMP_TABLE_STATS 
Enter value for tablename: 
PREFS 
-------- 
SESSION

Therefore, I decided to:

• Let each session has its proper GTT private SESSION statistics
• Use the cursor invalidation due to this GTT private statistics to avoid sharing execution plan between sessions

I started by unlocking the statistics of a couple of GTT tables involved in the 49 sessions DML queries as follows:

  SQL> execute dbms_stats.unlock_table_stats('SYSADM',T1_GTT_TEMP);

Parsing issue

Once I have implemented this change (unlock GTT statistics and use the default SESSION global parameter) I immediately realized that the 49 sessions were not anymore suffering from a performance issue due to execution plan sharing.But, as you might suspect, this doesn’t come free of charge. Indeed, I have spotted out new wait events appearing near the TOP 10 foreground wait events:

• cursor: pin S wait on X 
• library cache lock

These two wait events are symptoms of parsing effect.

While the 49 sessions were still running I issued the following query to get the SQL_ID suffering from this parsing issue:

SQL> select 
         sql_id
	 ,count(1) 
     from 
	  gv$active_session_history 
     where sample_time between to_date('05022019 15:29:00', 'ddmmyyyy hh24:mi:ss') 
	                      and to_date('05022019 16:00:00', 'ddmmyyyy hh24:mi:ss') 
     and event = 'cursor: pin S wait on X' 
     group by sql_id order by 2 desc fetch first 5 rows only
	
SQL_ID        COUNT(1) 
------------- ---------- 
5wgr71p4aj10v   28204
6fas07f6nptcu    6415 
                 5748 
fy64nh1g4ucxp    3063 
9rhtk5vh3qkjg    2394

I then tried to check why the above red bolded SQL_ID is being hard parsed so many times

SQL> @gv$sql 
      Enter value for sql_id: 5wgr71p4aj10v 
	  
	  SQL_ID 		CHILD_NUMBER TO_CHAR(P.LAST_ACTI EXECUTIONS END_FETCH  INVALIDATIONS OBJECT_STATUS
	  ------------- ------------ ------------------- ---------- ---------- ------------- -------------- 
	  5wgr71p4aj10v 0            05/02/2019 15:39:30         0        0               8 VALID 
	  5wgr71p4aj10v 1            05/02/2019 15:36:49         1        1               9 INVALID_UNAUTH 
	  5wgr71p4aj10v 2            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 3            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 4            05/02/2019 15:39:30         0        0               7 VALID 
	  5wgr71p4aj10v 5            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 6            05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 7            05/02/2019 15:39:30         0        0               5 VALID
	  5wgr71p4aj10v 8            05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 9            05/02/2019 15:39:30         0        0               5 VALID 
	  5wgr71p4aj10v 10           05/02/2019 15:36:10         1        1               6 INVALID_UNAUTH 
	  5wgr71p4aj10v 11           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 12           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 13           05/02/2019 15:39:30         0        0               5 VALID
	  5wgr71p4aj10v 14           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 15           05/02/2019 15:37:18         1        1               5 INVALID_UNAUTH 
	  5wgr71p4aj10v 17           05/02/2019 15:39:29         0        0               3 VALID 
	  5wgr71p4aj10v 18           05/02/2019 15:39:30         0        0               3 VALID 
	  5wgr71p4aj10v 19           05/02/2019 15:39:30         0        0               2 VALID 
	  5wgr71p4aj10v 20           05/02/2019 15:39:30         0        0               2 VALID 
	  5wgr71p4aj10v 21           05/02/2019 15:39:30         0        0               2 VALID 

Notice the high number of invalidated cursors (several in about 1 min). The INVALID_UNAUTH status means that the underlying child cursor will not be shared the next time and will be aged out when there is a stress on the library cache.

The reason of this frequent invalidation is shown here below:

SQL> @nonshared 
     Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

	 Enter value for 1: 5wgr71p4aj10v 
	 SQL_ID           : 5wgr71p4aj10v 
	 DDRESS           : 0000000308AB25B8 
	 CHILD_ADDRESS    : 00000002FCDABB40 
	 CHILD_NUMBER     : 0 
	 REASON           :<reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0 
	 ----------------- 
	 SQL_ID           : 5wgr71p4aj10v 
	 ADDRESS          : 0000000308AB25B8 
	 CHILD_ADDRESS    : 00000002FC680A48 
	 CHILD_NUMBER     : 1 
	 REASON           : <reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0
	 ----------------- 
	 ../.. 
	 SQL_ID           : 5wgr71p4aj10v 
	 ADDRESS          : 0000000308AB25B8
	 CHILD_ADDRESS    : 0000000317274F10 
	 CHILD_NUMBER     : 22 
	 REASON           : <reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0
	 ----------------- 

The Session Specific Cursor Session Mismatch reason indicates that Oracle has decided to invalidate the current cursor and to optimize a new execution plan because the current session is not allowed to use the GTT statistics of another session. It is crystal clear that when using SESSION PRIVATE statistics for GTT Oracle will not share the same child cursor that was optimized from another session. Each session will want to have its proper execution plan. it tries to pin the parent cursor and finds that it has been already pinned out by a preceding session. It then starts waiting on this famous wait event: cursor: pin S wait on X
It is important also to remember that Oracle gives us an information via the Note, at the bottom of the execution plan, when it is using a GTT private session statistics as shown below:

Note 
----- 
-- Global temporary table session private statistics used

Conclusion
This brief note shows, via a practical example taken from a running system, that we can use the 12c SESSION PRIVATE statistics to achieve two goals at least:

• Have each session with its proper GTT statistics without propagating them to another session
• Use the cursor invalidation side effect induced by the underlying code of the GTT SESSION PRIVATE statistics to avoid sharing execution plan between sessions

All things being equal you must balance between the performance improvement brought by this new GTT feature and the parsing side effect it introduces because of the underlying cursor invalidation. In my client case
the Library cache and Cursor Pin S wait on X wait events introduced by the SESSION PRIVATE statistics largely outweigh the performance penalty that comes when the 49 streams share the same GTT statistics.

Hybrid-Height Balanced corrupted histogram – Part II

Setting the Scene

At one client site, using PeopleSoft software for the employee’s payroll, I was asked to look at a weird performance issue occurring in an overnight batch. This batch launches 49 different Oracle sessions. Each session starts a bunch of DML queries. The data model has been engineered so that any session will treat a different range of employees. To accomplish this workload distribution, the main tables have been range/list partitioned. 49 range partitions with 58 list sub-partitions per partition. The range partition key identifies the employee id and is of varchar2(11) datatype. There is a skew in both the partition key values distribution inside a single partition and in the number of rows per partition for the whole table. The partition key has a HYBRID histogram as we are in 12cR1 under an Exadata machine.

The last time I did intervene for this batch, it was to find a solution so that execution plans will not be shared between the 49 sessions. Indeed, the skew of the number of rows(num_rows) in the range and list sub-partitions makes sharing the same execution plan a real performance threat. I will blog about how I achieved this goal in a couple of weeks or so.

In the current case the performance problem turned to be due to a wrong cardinality estimation because of corrupted Height-Balanced-Hybrid histogram as explained in part I of this mini-series on corrupted histogram. When visiting a single partition, the CBO underestimates the number of rows it will scan and ends up by using a dramatic NESTED LOOPS operation. But this was the case only for the single partitions having a corrupted histogram at its corresponding partition key. In this article I will show how I have fixed this HYBRID histogram corruption simply by deleting the statistics of the concerned partitions and regathering them.

The Scene in details

The part of the complex query where Oracle underestimates the cardinality of the single range partition resembles to this:

SQL> select /*+ full(a) */
        count(1)
     from
        PS_XX_YY_ZZZ_STAT a
     where EMPLID >= 'XX999501004'
     and EMPLID >== 'XX999562000';
 
COUNT(1)
---------
546461
 
Plan hash value: 2020349843
-------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Pstart| Pstop |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                   |      |       |       |
| 1 |  SORT AGGREGATE              |                   |    1 |       |       |
| 2 |   PARTITION RANGE SINGLE     |                   |   91 |    41 | 41    |
| 3 |    PARTITION LIST ALL        |                   |   91 |     1 | 58    |
|*4 |    TABLE ACCESS STORAGE FULL | PS_XX_YY_ZZZ_STAT |   91 |  2321 | 2378  |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))
    filter(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))

We can see that the cardinality estimation is wrong since Oracle estimates 91 rows while the actual generated rows are 546461. Let’s try to figure out why Oracle is underestimating this full table scan cardinality.
Since Oracle has identified a single partition (41) to visit it has then necessarily used the statistics of this partition when estimating the single column table selectivity. Below are listed the statistics (num_rows) at global and partition level for the range/list PS_XX_YY_ZZZ_STAT partitioned table:

SQL> @getPartTab
Enter value for owner: sysadm
Enter value for table_name: PS_XX_YY_ZZZ_STAT

TABLE_NAME            PNAME                   PPOS    SUBPARTITION_COUNT NUM_ROWS
-------------------- ------------------------ ------ ------------------ ----------
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_1    1               58         5811088
../..                                                         
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_41   41              58         568804      
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_42   42              58         527248
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_43   43              58         854141
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_44   44              58         809086
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_45   45              58         697071
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_46   46              58         484041
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_47   47              58         887009
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_48   48              58         259714
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_49   49              58         1025587
                                                                        ----------
Total num_rows                                                           38434347

As you can see the 41st partition has 568K rows while globally the whole table contains 38,4M rows. At this stage of the investigation we have got the first piece of the puzzle: num_rows (partition 41) = 568804
The second step is to check the EMPLID predicate column statistics at the 41st partition level:

col column_name    format a10
col partition_name format a35
col low_value      format a15
col high_value     format a15

SQL> select
       partition_name
      ,num_distinct
      ,utl_raw.cast_to_varchar2(low_value) low_value
      ,utl_raw.cast_to_varchar2(high_value) high_value
      ,density
      ,histogram
    from
      all_part_col_statistics
    where owner = 'SYSADM'
    and table_name = 'PS_XX_YY_ZZZ_STAT'
    and column_name = 'EMPLID'
    and partition_name in ('PS_XX_YY_ZZZ_STAT_T_41');

PARTITION_NAME             NUM_DISTINCT LOW_VALUE HIGH_VALUE            DENSITY    HISTOGRAM
------------------------- ------------ --------------- --------------- ---------- -----------
PS_XX_YY_ZZZ_STAT_T_41      6278         XX999498003    XX999561990     ,000159    HYBRID

We see that this column has a HYBRID histogram which, as you know, follows a different cardinality estimation formula depending on whether it is:
• A popular value
• A non-popular with an endpoint number
• A non-popular without an endpoint number

Let’s then check in what of the above three situations we are:

SQL> select
		partition_name
		,bucket_number
		,endpoint_actual_value
		,endpoint_repeat_count
		,bucket_size
		,case when Popularity > 0 then 'Pop'
		else 'Non-Pop'
		end Popularity
    from
		(select
			uth.partition_name
			,uth.bucket_number
			,uth.endpoint_actual_value
			,uth.endpoint_repeat_count
			,ucs.sample_size/ucs.num_buckets bucket_size
			,(uth.endpoint_repeat_count - ucs.sample_size/ucs.num_buckets) Popularity
		 from
		   all_part_histograms uth
		   ,all_part_col_statistics ucs
		 where
			uth.table_name = ucs.table_name
			and uth.column_name = ucs.column_name
			and uth.owner = ucs.owner
			and uth.owner = 'SYSADM'
			and uth.table_name = 'PS_XX_YY_ZZZ_STAT'
			and uth.partition_name in ('PS_XX_YY_ZZZ_STAT_T_41')
			and uth.column_name = 'EMPLID'
			and uth.endpoint_actual_value in ('XX999501004','XX999562000')
		)
    order by 
	partition_name, bucket_number;
	
no rows selected

It is a non-popular value without an endpoint number which then normally obeys to the following cardinality estimation formula:

   Card = num_rows * NewDensity = 568804 * .000159 = 90.439836

According to the above computed cardinality, you might think that Oracle has used the HYBRID histogram to estimate the wrong 90.43 ~ 91 cardinality. But, in fact, Oracle has completely ignored the histogram as proved by the corresponding 10053 trace file:

Column (#1): EMPLID(VARCHAR2) Part#: 0
AvgLen: 12 NDV: 267112 Nulls: 0 Density: 0.000004 Min: 0.000000 Max: 0.000000
Histogram: HtBal #Bkts: 5866 UncompBkts: 5866 EndPtVals: 0 ActualVal: yes

NOTE: ignoring histogram of column (PS_XX_YY_ZZZ_STAT.EMPLID) --->
used only for incremental stats maintenance -->

#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Column (#1): EMPLID(VARCHAR2) Part#: 0

AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Min: 0.000000 Max: 0.000000 Histogram: HtBal #Bkts: 5308 
UncompBkts: 5308 EndPtVals: 0 ActualVal: yes

NOTE: ignoring histogram of column (PS_XX_YY_ZZZ_STAT.EMPLID)
used only for incremental stats maintenance Column (#1): EMPLID(VARCHAR2) Part#: 40 AvgLen: 12 
NDV: 6278 Nulls: 0 Density: 0.000159
Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159
Table: PS_XX_YY_ZZZ_STAT Alias: A Card: Original: 568804.000000 Rounded: 91 
          Computed: 90.602740 Non Adjusted: 90.602740

Oracle is clearly showing that it has ignored the histogram for the column EMPLID and that it will use it only for incremental statistics maintenance.

Moreover, there is another oddity in the above 10053 trace file:

Histogram: HtBal #Bkts: 5308 UncompBkts: 5308 EndPtVals: 0 ActualVal: yes

See how Oracle CBO trace is looking at Height Balanced histogram while the statistics clearly shows that the EMPLID column has a HYBRID histogram?

I think that the histograms of this column at this partition are corrupted and therefore Oracle has ignored them and fall back to the cardinality estimation of a column without histogram:

Card = num_rows/NDV = 568804/6278 = 90.60273972 –-> exactly the value computed by Oracle

By the way we can know that Oracle will ignore the histogram, beforehand, just by selecting the NOTES column from
the all_part_col_statistics table as shown below:

SQL> select
	 partition_name
	,column_name
	,histogram
	,notes
    from
	all_part_col_statistics
    where owner = 'SYSADM'
     and table_name = 'PS_XX_YY_ZZZ_STAT'
     and column_name = 'EMPLID'
     and partition_name in ('PS_XX_YY_ZZZ_STAT_T_41');
		
PARTITION_NAME           COLUMN_NAME  HISTOGRAM       NOTES
------------------------ ----------- --------------- ---------------------
PS_XX_YY_ZZZ_STAT_T_41   EMPLID        YBRID          HIST_FOR_INCREM_STATS

The Notes indicates clearly that the HYBRID histogram for column EMPLID for partition n°41 will be used only for incremental statistics maintenance.

Working around the Scene

To get rid of this Height Balanced-HYBRID histogram cohabitation within the same column we need to delete and regather statistics in the concerned partition:

BEGIN
      dbms_stats.delete_table_stats(ownname  =>'SYSADM',tabname=>'PS_XX_YY_ZZZ_STAT',
                                    partname =>'PS_XX_YY_ZZZ_STAT_T_41');
									
      dbms_stats.gather_table_stats(ownname  =>'SYSADM',tabname=>'PS_XX_YY_ZZZ_STAT',
                                    partname =>'PS_XX_YY_ZZZ_STAT_T_41',
                                    granularity=>'PARTITION');
END ;
/

Querying again gives a perfect cardinality estimation

SQL> sELect /*+ full(a) */
		count(1)
	from
		PS_XX_YY_ZZZ_STAT a
	where EMPLID >= 'XX999501004'
	and EMPLID   <= 'XX999562000';
	
COUNT(1)
----------
546461

--------------------------------------------------------------------------------
| Id | Operation                   | Name               | Rows | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                    |      |       |       |
  SORT AGGREGATE                   |                    |    1 |       |       |
| 2 |   PARTITION RANGE SINGLE     |                    | 545K |     41|    41 |
| 3 |    PARTITION LIST ALL        |                    | 545K |      1|    58 |
|*4 |    TABLE ACCESS STORAGE FULL | PS_XX_YY_ZZZ_STAT  | 545K |   2321|  2378 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))
    filter(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))

And this time Oracle has clearly used the HYBRID histogram as proved by the corresponding 10053 trace file

Column (#1):
NewDensity:0.000159, OldDensity:0.000159 BktCnt:5560.000000, PopBktCnt:0.000000, PopValCnt:0, NDV:6278
Column (#1): EMPLID(VARCHAR2) Part#: 40
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes
Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159
Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes
Table: PS_XX_YY_ZZZ_STAT Alias: A
Card: Original: 568804 Rounded: 545382 Computed: 545381.630747 Non Adjusted: 545381.630747

This HYBRID histogram utilization is also backed by the new values of the Notes (null) columns:

SQL> select
		 partition_name
		,column_name
		,histogram
		,notes
     from
        all_part_col_statistics
    where owner = 'SYSADM'
    and table_name = 'PS_XX_YY_ZZZ_STAT'
    and column_name = 'EMPLID'
    and partition_name in ('PS_XX_YY_ZZZ_STAT_T_41');
	
PARTITION_NAME            COLUMN_NAM HISTOGRAM     NOTES
------------------------- ---------- ------------ ------
PS_XX_YY_ZZZ_STAT_T_41      EMPLID    HYBRID

The Scene Bottom-line

Oracle might ignore the presence of histogram when calculating the cardinality estimation of table/partition/sub-partition. This happens for corrupted histogram. You can check whether the histogram for a column at table/partition/sub-partition level will be used or not by the CBO simply by selecting the NOTES column from all_tab/part/subpart_col_statistics table.

Hybrid-Height Balanced corrupted histogram – Part I

Bad cardinality estimates lead to bad execution plans. If you understand why Oracle is misestimating object and operation cardinalities you can then address the performance problem properly. If you’re doubtful of that claim this article will show you how I have solved a performance issue by fixing a wrong single partition cardinality estimation due to a HEIGHT-BALANCED-HYBRID corrupted histogram of the partition key column. For the sake of simplicity, I will write a two-parts article. Part I explains how I have discovered this HEIGHT-BALANCED-HYBRID histogram corruption. It outlines also how it is easy to know, beforehand, that Oracle will ignore the usage of such histogram via a simple query. The second part examines the bad effect of this histogram corruption on the performance of a critical query and how I fixed it.

The Notes column

The column named NOTES in the following tables (database version is 12cR1):

• all_tab_col_statistics
• all_part_col_statistics
• all_subpart_col_statistics

is, nonchalantly, an important piece of information when trying to understand why Oracle has messed up its cardinality estimation.

Here’s below a list of partitions of a range-list sub partitioned table:

SELECT
   partition_name
   ,num_distinct
   ,sample_size
   ,density
   ,histogram
   ,notes
FROM
    all_part_col_statistics
WHERE
    owner = 'SYSADM'
AND table_name = 'RANGE_LIST_TAB'
AND column_name = 'EMPLID';

PARTITION_NAME             NUM_DISTINCT SAMPLE_SIZE DENSITY    HISTOGRAM NOTES
------------------------- ------------ ----------- ---------- --------- ------------------------
RANGE_LIST_TAB_T_1          18240       5396        ,000055     HYBRID
RANGE_LIST_TAB_T_10 		1180 		5472 		,000839 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_11 		1145 		5391 		,000865 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_12 		1167 		5572 		,000849 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_13 		1373 		5564 		,000715 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_14 		1720 		5520 		,000555 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_15 		1919 		5532 		,000486 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_16 		1821 		5454 		,000517 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_17 		1565 		5535 		,000618 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_18 		1690 		5377 		,000563 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_19 		1393 		5619 		,000704 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_2 			1045 		5560 		,000951 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_20 		1445 		5589 		,000676 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_21 		1874 		5401 		,000498 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_22			1895 		5332 		,000489 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_23 		1546 		5539 		,000627 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_24 		1532 		5449 		,000633 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_25 		1680 		5422 		,000568 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_26 		1623 		5511 		,000593 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_27 		1518 		5428 		,000639 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_28 		1624 		5674 		,000595 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_29 		1463 		5412 		,000665 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_3 			949 		5497 		,00105 		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_30 		1476 		5371 		,000658 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_31 		1824 		5519 		,000517 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_32 		3039 		5445 		,00024		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_33 		7647 		5372 		,000131 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_34 		2749 		5516 		,000287 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_35 		3622 		5398 		,000161 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_36 		7833 		5462 		,000128		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_37 		3233 		5622 		,000221 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_38 		8323 		5471 		,00012 		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_39 		7733 		5286 		,000129 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_4 			1745 		5588 		,000546 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_40 		4798 		5491 		,000052		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_41 		6278 		5560 		,000159 	HYBRID
RANGE_LIST_TAB_T_42 		5995 		5542 		,000167 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_43 		9502 		5538 		,000105 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_44 		9018 		5499 		,000111 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_45 		7941 		5544 		,000126 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_46 		5587 		5516 		,000179 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_47 		21670 		5484 		,000046 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_48 		9486 		5473 		,000105 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_49 		78008 		5385 		,000013 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_5 			1360 		5503 		,000722 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_6 			1040 		5583 		,000958 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_7 			998 		5450 		,000997 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_8 			1020 		5418 		,000975 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_9 			1219 		5441 		,000811 	HYBRID HIST_FOR_INCREM_STATS

49 rows selected.

What does this mean?

When Notes equals HIST_FOR_INCREM_STATS then it means that the HYBRID histogram for the EMPLID column will be ignored by the CBO during its cardinality estimation whenever this one will hit any of the 49 partitions except partition 1 and 41 where the Notes column is null.

HYBRID-Height Balanced corrupted histogram

Here’s below an illustration of what I’ve claimed above; first for partition 10 where histograms are ignored, followed by partition 41 where HYBRID histograms are used:

SQL> select /*+ full(a) */ count(1) from RANGE_LIST_TAB a where a.emplid = 'XY361125999';

COUNT(1)
----------
1414

---------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows | Pstart| Pstop  |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                 |                |      |       |        |
|  1 |   SORT AGGREGATE                 |                | 1    |       |        |
|  2 |    PARTITION RANGE SINGLE        |                | 534  |     10|     10 |
|  3 |      PARTITION LIST ALL          |                | 534  |     1 |     58 |
|* 4 |       TABLE ACCESS STORAGE FULL  | RANGE_LIST_TAB | 534  |   523 |    580 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("A"."EMPLID"='XY361125999')
    filter("A"."EMPLID"='XY361125999')

And a here’s below the important piece of the corresponding 10053 trace file

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for RANGE_LIST_TAB[A]
  
Column (#1): EMPLID(VARCHAR2) Part#: 0
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000839 Min: 0.000000 Max: 0.000000 

Histogram: HtBal #Bkts: 5472 UncompBkts: 5472 EndPtVals: 0 ActualVal: yes <------

NOTE: ignoring histogram of column (RANGE_LIST_TAB.EMPLID)   <------
      used only for incremental stats maintenance            <------

Column (#1): EMPLID(VARCHAR2) Part#: 9
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000847
Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000847
Table: RANGE_LIST_TAB Alias: A Card: Original: 630005 Rounded: 534 Computed: 533.902542 Non Adjusted: 533.902542

There are two important pieces of information in the above 10053 snippet code. The first one is:

Column (#1): EMPLID(VARCHAR2) Part#: 0
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000839 Min: 0.000000 Max: 0.000000 
Histogram: HtBal #Bkts: 5472 UncompBkts: 5472 EndPtVals: 0 ActualVal: yes

What the heck is this Height Balanced information (HtBal)? I’ve clearly shown above that the EMPLID column has a HYBRID histogram. Something is wrong with the histogram of this column which makes Oracle ignoring its usage confirmed by the second piece of information in the same 10053-trace file

NOTE: ignoring histogram of column (RANGE_LIST_TAB.EMPLID)
      used only for incremental stats maintenance

Since the histogram of EMPLID column has been ignored Oracle falls back to the classical single table selectivity without histogram:

  E-Rows = num_rows/num_distinct = 630005/1180 = 533,902542 –-> exact value in the 10053 trace file

Let’s now consider partition 41 where the Notes column is NULL:

SQL> select /*+ full(a) */ count(1) from RANGE_LIST_TAB a where a.emplid = 'XY178963531';

COUNT(1)
----------
1022
 ---------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows | Pstart| Pstop  |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                 |                |      |       |        |
|  1 |   SORT AGGREGATE                 |                |    1 |       |        |
|  2 |    PARTITION RANGE SINGLE        |                | 1330 |     41|     41 |
|  3 |      PARTITION LIST ALL          |                | 1330 |      1|     58 |
|* 4 |       TABLE ACCESS STORAGE FULL  | RANGE_LIST_TAB | 1330 |   2321|   2378 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("A"."EMPLID"='XY178963531')
    filter("A"."EMPLID"='XY178963531')

SINGLE TABLE ACCESS PATH
  Column (#1):
   NewDensity:0.000159, OldDensity:0.000159 BktCnt:5560.000000, PopBktCnt:0.000000, PopValCnt:0, 
  NDV:6278
Column (#1): EMPLID(VARCHAR2) Part#: 40
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes

Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes

Table: RANGE_LIST_TAB Alias: A Card: Original: 568804 Rounded: 1330 Computed: 1329.937410 
   Non Adjusted: 1329.937410 <-----

Oracle is, this time, using HYBRID histogram for the EMPLID value as shown in the above corresponding 10053 trace file. Let’s see if the partition key ‘XY178963531’ value is popular or not

SELECT
   partition_name
  ,bucket_number
  ,endpoint_actual_value
  ,endpoint_repeat_count
  , sample_size
  ,case when Popularity > 0 then 'Pop'
     else 'Non-Pop'
  end Popularity
FROM
	(select
		uth.partition_name
		,uth.bucket_number
		,uth.endpoint_actual_value
		,uth.endpoint_repeat_count
		,ucs.sample_size
		,(uth.endpoint_repeat_count - ucs.sample_size/ucs.num_buckets) Popularity
	from
	  all_part_histograms uth
	  ,all_part_col_statistics ucs
	where
	uth.table_name = ucs.table_name
	and uth.column_name = ucs.column_name
	and uth.owner = ucs.owner
	and uth.owner = 'SYSADM'
	and uth.table_name = 'RANGE_LIST_TAB'
	and uth.partition_name in ('RANGE_LIST_TAB_T_41')
	and uth.column_name = 'EMPLID'
	and uth.endpoint_actual_value = 'XY178963531'
	)
ORDER BY 
  partition_name, bucket_number;
  
PARTITION_NAME 			 BUCKET_NUMBER 	ENDPOINT_ACTUAL ENDPOINT_REPEAT_COUNT SAMPLE_SIZE POPULAR
------------------------ ------------- --------------- --------------------- ----------- -------
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5396        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5441        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5391        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5572        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5564        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5520        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5532        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5454        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5535        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5377        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5619        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5560        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5589        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5401        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5332        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5539        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5449        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5422        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5511        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5428        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5674        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5412        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5497        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5371        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5519        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5445        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5372        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5516        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5398        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5462        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5622        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5471        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5286        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5588        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5491        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5560        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5542        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5538        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5499        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5544        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5516        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5484        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5473        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5385        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5503        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5583        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5450        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5418        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5472        Non-Pop

‘XY178963531’ is a non-popular value with an endpoint number which resolves to the following cardinality estimation formula:

E-Rows = num_rows * greatest (NewDensity, ENDPOINT_REPEAT_COUNT/sample_size) 
E-Rows = 568804 * greatest (0.000159, 13/5560) = 1329,93741

This is exactly the cardinality computed by Oracle (visible in the 10053-trace file) which proves, again, that for partition 41 the HYBRID histogram has really been used.

How to proactively check for any corruption in histogram

Oracle, via this MOS Note 2483224.1 proposes the following query (which I have slightly updated) to proactively check for any corruption in histogram:

SQL> select
		c1.table_name,
		c1.column_name
    from
		all_histograms c1,
		all_histograms c2
    where 
	    c1.table_name = c2.table_name
		and c1.column_name = c2.column_name
		and c1.endpoint_number < c2.endpoint_number
		and c1.endpoint_value > c2.endpoint_value
		and c1.owner = c2.owner
		and c1.owner = 'SYSADM'
	group by
	  c1.table_name,
	  c1.column_name;

no rows selected

As you can see, this query is not correct since it shows no histogram corruption in my application.
If, instead, I use the following query it will show clearly that there are corrupted histograms or at least histograms that will be ignored by the CBO during its cardinality estimations:

SQL> SELECT
		 table_name
		,column_name
		,notes
	 FROM
		all_tab_col_statistics
	WHERE owner = 'SYSADM'
	  and histogram != 'NONE'
	  and trim(notes) = 'INCREMENTAL HIST_FOR_INCREM_STATS'
	GROUP BY
      table_name, column_name, notes
  ORDER BY
     table_name, column_name;
	 
TABLE_NAME 				COLUMN_NAME 	NOTES
-------------------- -------------- ----------------------------------
RANGE_LIST_TAB 			EMPLID 		INCREMENTAL HIST_FOR_INCREM_STATS
RANGE_LIST_TAB 			PRC_ORD_TS 	INCREMENTAL HIST_FOR_INCREM_STATS

Here we see that the table RANGE_LIST_TAB has two columns on which the histogram will be ignored. In addition, since RANGE_LIST_TAB is a partitioned table I can show this corrupted histogram information per partition using the following query as I did above in the first paragraph of this article:

SQL> SELECT
		 partition_name
		,column_name
		,histogram
		,notes
      FROM
		all_part_col_statistics
	WHERE owner = 'SYSADM'
		and table_name = 'RANGE_LIST_TAB'
		and column_name = 'EMPLID'
		and histogram != 'NONE'
		and trim(notes) = 'HIST_FOR_INCREM_STATS'
	ORDER BY
		partition_name, column_name;
		
PARTITION_NAME 				COLUMN_NAME 	HISTOGRAM 		NOTES
-------------------------- -------------- --------------- ----------------------
RANGE_LIST_TAB_T_10        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_11        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_12        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_13        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_14        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_15        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_16        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_17        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_18        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_19        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_2         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_20        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_21        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_22        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_23        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_24        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_25        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_26        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_27        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_28        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_29        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_3         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_30        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_31        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_32        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_33        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_34        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_35        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_36        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_37        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_38        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_39        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_4         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_40        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_42        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_43        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_44        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_45        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_46        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_47        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_48        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_49        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_5         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_6         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_7         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_8         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_9         EMPLID          HYBRID          HIST_FOR_INCREM_STATS

47 rows selected.

There are 47 partitions out of 49 in this RANGE_LIST_TAB table for which the HYBRID histogram for EMPLID column is corrupted. Partition 1 and 41 are not concerned by this corruption.
To work around this issue, you must delete and regather the statistics of the concerned partition. Let’s do the experiment for partition 42 for example:

BEGIN
  dbms_stats.delete_table_stats(ownname       =>'SYSADM'
                                ,tabname      =>'RANGE_LIST_TAB' ,partname =>'RANGE_LIST_TAB_T_42');
  dbms_stats.gather_table_stats(ownname       =>'SYSADM'
                                 ,tabname     =>'RANGE_LIST_TAB', partname =>'RANGE_LIST_TAB_T_42'
                                 ,granularity =>'PARTITION');
END ;
/

SQL> SELECT
		 partition_name
		,column_name
		,histogram
		,notes
     FROM
	     all_part_col_statistics
	WHERE owner = 'SYSADM'
	 and table_name = 'RANGE_LIST_TAB'
	 and column_name = 'EMPLID'
	 and histogram != 'NONE'
	 and trim(notes) = 'HIST_FOR_INCREM_STATS'
	ORDER BY
	   partition_name, column_name;
	   
PARTITION_NAME 				COLUMN_NAME 	HISTOGRAM 		NOTES
--------------------------- -------------- --------------- ------------------------
RANGE_LIST_TAB_T_10         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_11         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_12         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_13         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_14         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_15         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_16         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_17         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_18         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_19         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_2          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_20         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_21         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_22         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_23         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_24         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_25         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_26         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_27         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_28         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_29         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_3          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_30         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_31         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_32         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_33         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_34         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_35         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_36         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_37         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_38         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_39         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_4          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_40         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_43         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_44         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_45         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_46         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_47         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_48         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_49         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_5          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_6          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_7          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_8          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_9          EMPLID         HYBRID           HIST_FOR_INCREM_STATS

46 rows selected.

The number of partitions concerned by the corrupted histogram is now 46 and the partition 42 is not anymore among the list.

Summary
You should always show a tenacity in trying to understand why Oracle has messed up its cardinality estimation. You may end up by finding the root cause of the sup-optimal execution plan. In this case, I ended up by finding that the 12c HYBRID histograms can be ignored by the CBO when they are corrupted (presence of Height Balance histogram in the 10053-trace file while the column dictionary table is showing HYBRID histogram).
I’ve also found that we can know whether the histogram will be used or not by Oracle during its cardinality estimation, simply by looking at the value of the column Note from all_tab/part_col_statistics table.