Mohamed Houri’s Oracle Notes

November 21, 2019

Incremental, histogram and fix control

Filed under: Statistics — hourim @ 5:35 pm

Due to my zeal to understand the curious comportment of the non-usage of histogram in a partitioned table under incremental mode, I decided to write this 4th part article in the series of blog posts on wrong cardinality estimation due to the non-utilization of histogram during query optimization:

  •  Part I where I wrongly put the blame of wrong cardinality estimation on corrupted histogram
  •  Part II where I examined via a real-life example the impact of this wrong cardinality estimation on the query performance
  •  Part III where I showed that, in fact, the wrong cardinality estimation is due to the incremental mode which, under certain circumstances, forces Oracle to ignore the usage of histogram

In this 4th Part, I would like to share with you a couple of ways you can use to make Oracle not ignoring the usage of histogram while keeping the incremental mode up (TRUE).

1. Fix Control 13583722

In Part III I mentioned that the dbms_stats trace file shows the presence of the 13583722 fix control

DBMS_STATS:          Need Actual Values (DSC_EAVS)                    
DBMS_STATS:          Partition: XZ_JOB_T_12                           
DBMS_STATS:          Histogram Type: HYBRID Data Type: 2              
DBMS_STATS:          Histogram Flags: 4 Histogram Gathering Flags: 10 
DBMS_STATS:          Incremental: TRUE Fix Control 13583722: 1        

The definition of this fix control is :

SQL> select  bugno, description from V$SYSTEM_FIX_CONTROL where bugno = '13583722';

     BUGNO DESCRIPTION
---------- ----------------------------------------------------------------
  13583722 preserve partition level histograms & actual values for incremen

Let’s see what happens if I disable this fix control?

But first, here’s below the cardinality estimation before modifying the fix control :

select /*+ full(a) */
         count(1)
     from
        XZ_JOB a
     where EMPLID>= 'XZ990501004'
     and   EMPLID<= 'XZ990562000'; 

  COUNT(1)
----------
     44724

Plan hash value: 2916242030
----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        |     7 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB |     7 |    42 |    42 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - storage("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000')
       filter("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000')

This wrong cardinality estimation of 7 instead of 44K, is due to Oracle ignoring the usage of histogram when visiting partition n°42

@getPartTab
compute sum label 'Total num_rows' of num_rows on report
break   on report
set verify off
select 
    table_name
   ,partition_name     pname
   ,partition_position ppos
   ,num_rows
from    
  (select
       table_name
      ,partition_name      
      ,partition_position  	
      ,num_rows	   
   from
      all_tab_partitions
  where 
    table_owner = upper('&owner')
  and
    table_name = upper('&table_name')
);

Enter value for owner: sysadm
Enter value for table_name: XZ_JOB

Enter value for owner: sysadm
Enter value for table_name: XZ_JOB

TABLE_NAME   PNAME             PPOS   NUM_ROWS 
------------ --------------- ------ ---------- 
XZ_JOB       XZ_JOB_T_1           1     721565 
XZ_JOB       XZ_JOB_T_2           2     213356 
XZ_JOB       XZ_JOB_T_3           3      35906 
../..
XZ_JOB       XZ_JOB_T_42         42      46919  -->
XZ_JOB       XZ_JOB_T_43         43      39403 
XZ_JOB       XZ_JOB_T_48         48     110980 
XZ_JOB       XZ_JOB_T_49         49    1342747 
                                      ----------
Total num_rows                         5118455

@PartHist
SQL> select
       partition_name
      ,num_distinct
      ,density
      ,histogram
       ,notes
    from
        all_part_col_statistics
    where owner    = 'SYSADM'
    and table_name = 'XZ_JOB'
    and column_name = 'EMPLID';

PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM  NOTES
--------------- ------------ ---------- ---------- ---------------------
XZ_JOB_T_1             14954    ,000067 HYBRID
XZ_JOB_T_2              5141    ,000035 HYBRID
XZ_JOB_T_3              1059    ,000941 HYBRID     HIST_FOR_INCREM_STATS
../..
XZ_JOB_T_42             6372     ,00008 HYBRID     HIST_FOR_INCREM_STATS -->
XZ_JOB_T_43             6071    ,000165 HYBRID     HIST_FOR_INCREM_STATS
XZ_JOB_T_48            21812    ,000046 HYBRID     HIST_FOR_INCREM_STATS
XZ_JOB_T_49           243840    ,000004 HYBRID     HIST_FOR_INCREM_STATS

49 rows selected.

E-Rows = partition num_rows/ NDV = 46919/6372 = 7.36~7

And here’s the new cardinality estimation after the fix control change

SQL> alter session set "_fix_control" = '13583722:0';

SQL> exec dbms_stats.delete_table_stats
          (ownname=>'SYSADM',tabname=>'XZ_JOB',partname=>'XZ_JOB_T_42');

SQL> exec dbms_stats.gather_table_stats
        (ownname=>'SYSADM',tabname=>'XZ_JOB',          
         partname=>'XZ_JOB_T_42',granularity=>'PARTITION');

@PartHist
PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM NOTES
--------------- ------------ ---------- --------- ----------------------
XZ_JOB_T_1             14954    ,000067 HYBRID
XZ_JOB_T_2              5141    ,000035 HYBRID
XZ_JOB_T_3              1059    ,000941 HYBRID    HIST_FOR_INCREM_STATS
../..
XZ_JOB_T_42             6372    ,000082 HYBRID
XZ_JOB_T_43             6071    ,000165 HYBRID    HIST_FOR_INCREM_STATS
XZ_JOB_T_48            21812    ,000046 HYBRID    HIST_FOR_INCREM_STATS
XZ_JOB_T_49           243840    ,000004 HYBRID

49 rows selected.

select /*+ full(a) */
         count(1)
     from
        XZ_JOB a
     where EMPLID>= 'XZ990501004'
     and   EMPLID<= 'XZ990562000'
  COUNT(1)
----------
     44724

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        | 44649 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB | 44649 |    42 |    42 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - storage(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))
       filter(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))

As you can see the fix control 13583722 has made Oracle not ignoring the usage of histogram when visiting partition n°42 (Notes column is null) which explains why the new cardinality estimation is excellent.

2. Fix Control 16726844

As far as fix control goes I decided to check whether I can find any other fix control that might be related to this incremental-histogram issue:

SQL> select bugno, value, description
     from v$system_fix_control
     where description like '%incremen%';

     BUGNO      VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------
  10175079          1 increment kafcoref to simulate obsolete fropqop list
  13583722          1 preserve partition level histograms & actual values for incremen
  16726844          1 consider histogram request in incremental stats gathering

The fix control 16726844 seems to be also related to the usage of histogram when incremental mode is set to TRUE. Let’s change its value and check again (before changing the fix control I have of course reproduced the wrong cardinality estimation of 7):

SQL> alter session set "_fix_control" = '16726844:0';

SQL> exec dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>'XZ_JOB',partname=>'XZ_JOB_T_42');

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'XZ_JOB', partname=>'XZ_JOB_T_42',granularity=>'PARTITION');

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        | 44598 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB | 44598 |    42 |    42 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------
   3 - storage(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))
       filter(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000')))

Indeed, this fix control has also made Oracle using the histogram when visiting partition n°42.

3. For columns size 254 EMPLID

The last option I have checked and used to workaround this issue of not using histogram in presence of incremental mode is to force histograms to be kept by manually specifying bucket size 254:

  method_opt => 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 EMPLID'
SQL> exec dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>'XZ_JOB');

SQL> BEGIN 
        dbms_stats.gather_table_stats
        (ownname=>'SYSADM',tabname=>'XZ_JOB'
         ,method_opt => 'for all columns size auto, for columns size 254 emplid'
	 ,degree => 4);
     END;
     /

SQL> @GlobHist

TABLE_NAME   COLUMN_NAME   HISTOGRAM       NOTES
------------ ------------- --------------- ------------
XZ_JOB       EMPLID        HYBRID          INCREMENTAL

SQL> @PartHist

PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM       NOTES
--------------- ------------ ---------- --------------- -----------
XZ_JOB_T_1             14954    ,000067 HYBRID
XZ_JOB_T_10             1232    ,000805 HYBRID
XZ_JOB_T_11             1191    ,000832 HYBRID
XZ_JOB_T_12             1153    ,000861 HYBRID
XZ_JOB_T_13             1187    ,000837 HYBRID
XZ_JOB_T_14             1390    ,000703 HYBRID
XZ_JOB_T_15             1733    ,000555 HYBRID
XZ_JOB_T_16             1950    ,000476 HYBRID
XZ_JOB_T_17             1842    ,000512 HYBRID
XZ_JOB_T_18             1584    ,000611 HYBRID
XZ_JOB_T_19             1714     ,00056 HYBRID
XZ_JOB_T_2              5141    ,000031 HYBRID
XZ_JOB_T_20             1410    ,000695 HYBRID
XZ_JOB_T_21             1460    ,000671 HYBRID
XZ_JOB_T_22             1899    ,000493 HYBRID
XZ_JOB_T_23             1916    ,000489 HYBRID
XZ_JOB_T_24             1568    ,000618 HYBRID
XZ_JOB_T_25             1550    ,000627 HYBRID
XZ_JOB_T_26             1697    ,000568 HYBRID
XZ_JOB_T_27             1637    ,000593 HYBRID
XZ_JOB_T_28             1528    ,000639 HYBRID
XZ_JOB_T_29             1629    ,000595 HYBRID
XZ_JOB_T_3              1059    ,000941 HYBRID
XZ_JOB_T_30             1479    ,000662 HYBRID
XZ_JOB_T_31             1480    ,000661 HYBRID
XZ_JOB_T_32             1839    ,000514 HYBRID
XZ_JOB_T_33             3083    ,000319 HYBRID
XZ_JOB_T_34             7756    ,000028 HYBRID
XZ_JOB_T_35             2789    ,000359 HYBRID
XZ_JOB_T_36             3669    ,000273 HYBRID
XZ_JOB_T_37             7947    ,000071 HYBRID
XZ_JOB_T_38             3277    ,000305 HYBRID
XZ_JOB_T_39             8466    ,000118 HYBRID
XZ_JOB_T_4               960     ,00104 HYBRID
XZ_JOB_T_40             7836    ,000044 HYBRID
XZ_JOB_T_41             4873    ,000162 HYBRID
XZ_JOB_T_42             6372    ,000077 HYBRID
XZ_JOB_T_43             6071    ,000165 HYBRID
XZ_JOB_T_44             9642    ,000104 HYBRID
XZ_JOB_T_45             9121     ,00011 HYBRID
XZ_JOB_T_46             8028    ,000125 HYBRID
XZ_JOB_T_47             5635    ,000177 HYBRID
XZ_JOB_T_48            21812    ,000046 HYBRID
XZ_JOB_T_49           243840    ,000004 HYBRID
XZ_JOB_T_5              1764    ,000541 HYBRID
XZ_JOB_T_6              1379    ,000712 HYBRID
XZ_JOB_T_7              1050    ,000949 HYBRID
XZ_JOB_T_8              1016    ,000982 HYBRID
XZ_JOB_T_9              1032    ,000965 HYBRID
                
49 rows selected.
SQL> select /*+ full(a) */
  2           count(1)
  3       FROM
  4          XZ_JOB a
  5       where EMPLID>= 'XZ990501004'
  6       and   EMPLID<= 'XZ990562000'; 
  COUNT(1)
----------
     44724

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        | 44645 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB | 44645 |    42 |    42 |
----------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_fix_control' '18302923:1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - storage(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))
       filter(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))

Again, changing the method_opt parameter value so that histogram on the EMPLID column is forced has made Oracle not only create the HYBRID histogram but to use them during query optimization even when the incremental mode is set to TRUE.

4. Bottom-line

Understandably, when incremental mode is set to TRUE Oracle might ignore the usage of histogram during query optimization. To avoid wrong cardinality estimation in such a situation you can use one of the following fixes:

  •  Set the preference incremental mode to FALSE (at table or global level)
  •  Unset the fix control 13583722
  •  Unset the fix control 16726844
  •  Force Oracle to create histogram on the desired column by manually specifying the bucket size 254:
     method_opt => 'for all columns size auto, for columns size 254 COL1' 

Leave a Comment »

No comments yet.

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: