Mohamed Houri’s Oracle Notes

November 14, 2019

Incremental and ruin

Filed under: Oracle — hourim @ 8:02 pm

It’s hardly a secret that many people are not fan of histograms. The main problem of them is that they are not very well documented, and they drive, under the hood, many other features like Adaptive Cursor Sharing. They also interact with gathering statistics for partitioned tables when the incremental preference is set to TRUE.

As 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 cardinality estimation when visiting a partitioned table. I couldn’t figure out exactly the root cause of this cardinality mis-estimation so that I opened an Oracle Service Request (SR). As an answer to my request, Oracle support send me a reference to my two articles mentioned above😊. While I was still waiting for a viable answer from the Oracle support, the same performance issue kicked in and I was asked to solve it as quickly as possible.

After a couple of thinking minutes, I decided to change the incremental preference of that partitioned table and regather its statistics as in the following:

SQL> exec dbms_stats.set_table_prefs('SYSADM', 'PS_XYZ', 'incremental', 'false');
SQL> exec dbms_stats.gather_table_stats('SYSADM', 'PS_XYZ',degree => 4);

This made the culprit query, identified by the SQL_ID 147gz7h1x7jkq, going from a non-ending 42,886 seconds

Global Information
------------------------------
 Status              :  EXECUTING                         
 Instance ID         :  1                                 
 Session             :  SYSADM (23:47269)                 
 SQL ID              :  147gz7h1x7jkq                     
 SQL Execution ID    :  16777219                          
 Execution Started   :  08/28/2019 21:19:19               
 First Refresh Time  :  08/28/2019 21:19:25               
 Last Refresh Time   :  08/29/2019 09:14:05               
 Duration            :  42886s                            
 

to about only 5 seconds for the worst case

SQL> @sqlstats
Enter value for sql_id: 147gz7h1x7jkq

     CHILD    INST_ID PLAN_HASH_VALUE   AVG_GETS LAST_ACTI            AVG_ETIME      EXECS
---------- ---------- --------------- ---------- ------------------- ---------- ----------
         0          1      3376574399      23452 29/08/2019 17:09:08   5,357016          1
         0          2       430435019       2961 29/08/2019 17:09:36   1,366556          1

The goal of this article is to explain the negative effect the incremental mode can have on the effective use of histogram during cardinality estimation.

By the way, if you are wondering why the same query has got two different execution plans (3376574399,430435019) at almost the same time, then bear in mind that this is the effect of a GTT table having a SESSION private statistics.

1.Few words about the Setup

This is a PeopleSoft software using Oracle 12cR1 under Exadata machine. The cardinality misestimation occurs when Oracle visits a range (or a range/list) partitioned table of 49 partitions. The partition key is of varchar2(11) data type having a uniform distribution for certain partitions, and a skewed distribution at others. The incremental preference of the partitioned table is set to TRUE.

Here’s an image of the partition key column (EMPLID) statistics when the performance issue occurred (incremental = TRUE) and after it has been fixed (incremental =FALSE) respectively (at global and partition level):

-- incremental = TRUE
SELECT
   table_name
  ,column_name
  ,notes
FROM
  all_tab_col_statistics
WHERE 
    owner       = 'SYSADM'
AND table_name  = 'PS_XYZ'
AND column_name = 'EMPLID';    
  
TABLE_NAME  COLUMN_NAME   HISTOGRAM       NOTES   
----------- ------------- --------------- ----------------------------------
PS_XYZ      EMPLID        HYBRID          INCREMENTAL HIST_FOR_INCREM_STATS        
SQL> select
         partition_name
        ,num_distinct
        ,density
        ,histogram
        ,notes
    from
        all_part_col_statistics
    where owner = 'SYSADM'
    and table_name = 'PS_XYZ'
    and column_name = 'EMPLID';


PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM       NOTES                 
--------------- ------------ ---------- --------------- ----------------------
PS_XYZ_T_1             19888     ,00005 HYBRID                                
PS_XYZ_T_10             1191    ,000831 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_11             1153    ,000861 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_12             1187    ,000835 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_13             1390    ,000707 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_14             1733    ,000552 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_15             1950    ,000479 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_16             1842    ,000516 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_17             1584    ,000611 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_18             1714     ,00056 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_19             1410    ,000697 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_2              1059    ,000941 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_20             1460    ,000672 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_21             1899    ,000526 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_22             1916     ,00049 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_23             1568    ,000621 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_24             1550    ,000625 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_25             1697    ,000568 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_26             1637     ,00059 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_27             1528    ,000639 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_28             1629    ,000596 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_29             1479    ,000663 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_3               960     ,00104 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_30             1480    ,000662 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_31             1839     ,00052 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_32             3007    ,000284 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_33             7693     ,00013 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_34             2776     ,00036 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_35             3653    ,000215 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_36             7940    ,000126 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_37             3277    ,000305 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_38             8461    ,000118 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_39             7855    ,000127 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_4              1764    ,000567 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_40             4878    ,000205 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_41             6361    ,000157 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_42             6064    ,000165 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_43             9641    ,000104 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_44             9142    ,000109 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_45             8043    ,000124 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_46             5664    ,000078 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_47            21980    ,000045 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_48             9550    ,000105 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_49           270752    ,000004 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_5              1379    ,000725 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_6              1050    ,000952 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_7              1016    ,000984 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_8              1032    ,000969 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_9              1232    ,000811 HYBRID          HIST_FOR_INCREM_STATS 

49 rows selected.
-- incremental = FALSE

TABLE_NAME  COLUMN_NAME  HISTOGRAM       NOTES                                
----------- ------------ --------------- ------
PS_XYZ      EMPLID       HYBRID

PARTITION_NAME   NUM_DISTINCT    DENSITY HISTOGRAM       NOTES   
--------------- ------------ ---------- --------------- ---------
PS_XYZ_T_1             19888     ,00005 HYBRID                   
PS_XYZ_T_10             1191 ,000839631 NONE                     
PS_XYZ_T_11             1153    ,000861 HYBRID                   
PS_XYZ_T_12             1187  ,00084246 NONE                     
PS_XYZ_T_13             1390 ,000719424 NONE                     
PS_XYZ_T_14             1733 ,000577034 NONE                     
PS_XYZ_T_15             1950    ,000476 HYBRID                   
PS_XYZ_T_16             1842    ,000515 HYBRID                   
PS_XYZ_T_17             1584 ,000631313 NONE                     
PS_XYZ_T_18             1714     ,00056 HYBRID                   
PS_XYZ_T_19             1410  ,00070922 NONE                     
PS_XYZ_T_2              1059 ,000944287 NONE                     
PS_XYZ_T_20             1460    ,000671 HYBRID                   
PS_XYZ_T_21             1899    ,000526 HYBRID                   
PS_XYZ_T_22             1916    ,000491 HYBRID                   
PS_XYZ_T_23             1568     ,00062 HYBRID                   
PS_XYZ_T_24             1550    ,000626 HYBRID                   
PS_XYZ_T_25             1697    ,000562 HYBRID                   
PS_XYZ_T_26             1637    ,000593 HYBRID                   
PS_XYZ_T_27             1528     ,00064 HYBRID                   
PS_XYZ_T_28             1629 ,000613874 NONE                     
PS_XYZ_T_29             1479    ,000663 HYBRID                   
PS_XYZ_T_3               960 ,001041667 NONE                     
PS_XYZ_T_30             1480    ,000661 HYBRID                   
PS_XYZ_T_31             1839    ,000518 HYBRID                   
PS_XYZ_T_32             3007    ,000288 HYBRID                   
PS_XYZ_T_33             7693     ,00013 HYBRID                   
PS_XYZ_T_34             2776 ,000360231 NONE                     
PS_XYZ_T_35             3653    ,000215 HYBRID                   
PS_XYZ_T_36             7940    ,000126 HYBRID                   
PS_XYZ_T_37             3277 ,000305157 NONE                     
PS_XYZ_T_38             8461    ,000118 HYBRID                   
PS_XYZ_T_39             7855    ,000127 HYBRID                   
PS_XYZ_T_4              1764    ,000567 HYBRID                   
PS_XYZ_T_40             4878 ,000205002 NONE                     
PS_XYZ_T_41             6361    ,000157 HYBRID                   
PS_XYZ_T_42             6064    ,000165 HYBRID                   
PS_XYZ_T_43             9641    ,000104 HYBRID                   
PS_XYZ_T_44             9142    ,000109 HYBRID                   
PS_XYZ_T_45             8043 ,000124332 NONE                     
PS_XYZ_T_46             5664    ,000076 HYBRID                   
PS_XYZ_T_47            21980    ,000045 HYBRID                   
PS_XYZ_T_48             9550    ,000105 HYBRID                   
PS_XYZ_T_49           270944    ,000004 HYBRID                   
PS_XYZ_T_5              1379 ,000725163 NONE                     
PS_XYZ_T_6              1050 ,000952381 NONE                     
PS_XYZ_T_7              1016    ,000984 HYBRID                   
PS_XYZ_T_8              1032 ,000968992 NONE                     
PS_XYZ_T_9              1232 ,000811688 NONE                     

49 rows selected.

As you can see, this is a weird non-common situation. There is a clear contradiction here. How could it be differently when we see that, switching the incremental mode from true to false, switches the same data distribution for the same column at the same partition (PS_JOB_T_10 for example) from skewed (HYBIRD) to non-skewed (NONE)?

In fact, if you look closer you will realize that, as far as query optimization is concerned, there is no contradiction in the following situation:

HYBRID  +  HIST_FOR_INCREM_STATS = NONE

Because, when the CBO sees that the HYBRID histogram is accompanied by the Notes HIST_FOR_INCREM_STATS, it knows exactly that this histogram will be ignored at query optimization time, which is equivalent to not having histogram at all. This is for example correct for partitions like PS_JOB_T_10, PS_JOB_T_12 and PS_JOB_T_13 where column values are evenly distributed. But the real threat resides in partitions like PS_JOB_T_11, PS_JOB_T_15 and PS_JOB_T_16 for example where we have the following situation:

HYBRID  +  HIST_FOR_INCREM_STATS != HYBRID

In this case, the column values are skewed but their histogram are ignored by the CBO during query optimization because of the HIST_FOR_INCREM_STATS Notes. This is exactly the root cause of my performance issue.

  • Regardless of whether the partition key is skewed or not it will have a histogram
  • But this histogram will be ignored both at partition (except partition n°1 in this case) and at global level during query optimization.

Therefore, it becomes clear for me that fixing my performance issue resides in making the CBO not ignoring the presence of histogram at both partition level and global level. And, disabling the incremental mode, is what I did to achieve this goal.

2. Why histograms are ignored?

In the SR I opened for the Oracle support I have supplied the trace file of a dbms_stats call under incremental mode. Something resembling to this (partition n°11 in TEST database contains non-skewed value for the EMPLID column):

SQL> exec dbms_stats.set_global_prefs ('TRACE', to_char (1+16));

SQL> begin
        dbms_stats.gather_table_stats('sysadm'
                                      ,'PS_XYZ'
                                      ,partname  =>'PS_XYZ_T_11'
                                      ,granularity =>'partition');
    end;
   /
SQL> exec dbms_stats.set_global_prefs('TRACE', null);
DBMS_STATS: Building Histogram for EMPLID                                                                          
DBMS_STATS:  bktnum=-1, nnv=50819, snnv=5511,604748383025713834989746016721880424, sndv=1191, est_ndv=1191, mnb=254
DBMS_STATS:  Trying hybrid histogram                                                                               
DBMS_STATS:  > cdn 5592, popFreq 0, popCnt 0, bktSize 22,0750988142292490118577075098814229249
             , bktSzFrc ,0750988142292490118577075098814229249     
                                                
DBMS_STATS:  Evaluating hybrid histogram:  cht.count 254, mnb 254, ssize 5592, min_ssize 2500, appr_ndv  TRUE
             ,ndv 1191, selNdv 0, selFreq 0, 
             pct 10,84555923647262975232686543618867329232, avg_bktsize 22, csr.hreq FALSE, normalize TRUE   
DBMS_STATS:   Checking range skew:                                                                                 
DBMS_STATS:      >> NOT SKEWED                           ----------------->                                                   
DBMS_STATS:  Not Discarding histogram (Incremental mode) ----------------->                                        
DBMS_STATS:   Histogram gathering flags: 4623     

As you can see, Oracle is aware that the EMPLID column has an even distribution in partition n°11 but it decides to do not discard the creation of histogram because of the incremental mode. Oracle, nevertheless, updates the corresponding Notes column to indicate that this histogram will not be used during cardinality estimation.

PARTITION_NAME   NUM_DISTINCT    DENSITY HISTOGRAM       NOTES
---------------- ------------ ---------- --------------- ----------------------
PS_XYZ_T_11              1191    ,000833 HYBRID          HIST_FOR_INCREM_STATS

If, I regather the same statistics for partition n°12 where the EMPLID is skewed (in TEST) I will have the following trace snippet:

DBMS_STATS: Building Histogram for EMPLID                                                                          
DBMS_STATS:  bktnum=-1, nnv=47961, snnv=5501,261730969760166840458811261730969758, sndv=1153, est_ndv=1153, mnb=254
DBMS_STATS:  Trying hybrid histogram                                                                               
DBMS_STATS:  > cdn 5443, popFreq 0, popCnt 0, bktSize 21,49407114624505928853754940711462450593, 
              bktSzFrc ,49407114624505928853754940711462450593                                    
DBMS_STATS:  Evaluating hybrid histogram:  cht.count 254, mnb 254, ssize 5443, min_ssize 2500, appr_ndv  TRUE
            ,ndv 1153, selNdv 0, selFreq 0
            ,pct 11,47028154327424400417101147028154327424, avg_bktsize 21, csr.hreq FALSE, normalize TRUE   
DBMS_STATS:   Checking range skew:                                                                                 
DBMS_STATS:      >> SKEWED                   ------------------------>                                                       
DBMS_STATS:   Histogram gathering flags: 527                                                                       
DBMS_STATS:  Accepting histogram             ------------------------>             

DBMS_STATS:          Need Actual Values (DSC_EAVS)                                                                 
DBMS_STATS:          Partition: PS_XYZ_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      

This time, Oracle has correctly created the histogram and has left the Notes column empty indicating that the histogram will be used by the CBO when computing cardinality estimation for partition n°12:

PARTITION_NAME    NUM_DISTINCT DENSITY HISTOGRAM          NOTES
---------------- ------------ -------- ----------------- ----------------------
PS_XYZ_T_11       1191        ,000833  HYBRID             HIST_FOR_INCREM_STATS
PS_XYZ_T_12       1153        ,000861  HYBRID

However, at global level, Oracle will still ignore the usage of histogram as the following proves:

SQL> SELECT
       table_name
       ,column_name
       ,histogram
       ,notes
    FROM
      all_tab_col_statistics
    WHERE
     owner = 'SYSADM'
     and table_name ='PS_XYZ'
     and column_name = 'EMPLID';

TABLE_NAME  COLUMN_NAME   HISTOGRAM       NOTES   
----------- ------------- --------------- ----------------------------------
PS_XYZ      EMPLID        HYBRID          INCREMENTAL HIST_FOR_INCREM_STATS        

I think that it suffices to have a single partition with that Note value (HIST_FOR_INCREM_STATS ) to see Oracle ignoring the usage of histogram at global level as well.

In passing, if you have exercised eyes you should have already remarked the control fix in the dbms_stats trace file

DBMS_STATS: Incremental: TRUE Fix Control 13583722: 1

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

DESCRIPTION
----------------------------------------------------------------
preserve partition level histograms & actual values for increment

For the sake of simplicity, I will postpone publishing my investigations on this fix_control to a separate article.

3. Possible reasons

The following references might help understanding the situation explained in this article and the possible reasons pushing Oracle to keep creating histograms but ignoring their usage during query optimisation process:

4. Bottom-line

As we have seen above, partition level histogram will not be discarded for tables with incremental mode set to TRUE. This happens regardless of whether the column values are skewed or evenly distributed. I think that, the reason behind this is that, under incremental mode, when global histogram aggregations are calculated, missing partition level histograms must be regathered at partition level with even distribution. Probably Oracle decided to keep these histograms at partition level to avoid doing the same thing two times under incremental mode. But it decides, at the same time, to pre-empt the CBO from using the histograms both at global and partition level during query optimization.

All in all, I think that disabling incremental mode is something one should envisage if confronted to a similar performance issue.

3 Comments »

  1. getting a reference to your own post is ultimate flattery 🙂
    I also had struggle with “Bug 19450139 – Slow gather table stats with incremental stats enabled”, which does not reproduce in 12.2. In 12.1 I also deactivated incremental stats and switched to full stats once a week.

    Comment by laurentschneider — November 15, 2019 @ 9:48 am | Reply

  2. Laurent,

    Getting referenced by Oracle Support is just the result of being the first or second google hit when we search for the word INCREMENTAL HIST_FOR_INCREM_STATS.

    I have a 4th Part article, the last one in this mini series about incremental mode and histogram, where I am showing a couple of workarounds allowing to keep the incremental mode on while the CBO will still be able to use histogram during query optimization.

    Best regards
    Mohamed

    Comment by hourim — November 15, 2019 @ 10:25 am | Reply

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

    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.

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: