Mohamed Houri’s Oracle Notes

July 26, 2019

Hybrid-Height Balanced corrupted histogram – Part I

Filed under: CBO — hourim @ 6:13 pm

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.

3 Comments »

  1. […] 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 […]

    Pingback by Hybrid-Height Balanced corrupted histogram – Part II | Mohamed Houri’s Oracle Notes — July 28, 2019 @ 7:58 am | Reply

  2. […] you might know, if you have read my two preceding articles here and here, I have been struggling with recurrent PeopleSoft performance issues because of a wrong […]

    Pingback by Incremental and ruin | Mohamed Houri’s Oracle Notes — November 14, 2019 @ 8:03 pm | Reply

  3. […]  Part I where I wrongly put the blame of wrong cardinality estimation on corrupted histogram […]

    Pingback by Incremental, histogram and fix control | Mohamed Houri’s Oracle Notes — November 21, 2019 @ 5:36 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: