Skip to Main Content
  • Questions
  • Query runs faster with stats deleted, slower with up to date stats

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, KHALID.

Asked: March 05, 2017 - 7:55 pm UTC

Last updated: March 08, 2017 - 3:18 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi team AskTOM,

Sorry, wasn't sure how to use livesql.oracle.com for my case.

I have a situation where (no stats as in dynamic sampling (11.2.04)) is producing a better plan (and faster elapsed time) than with gather_table_stats. Please see the 3 sections I have pasted below, delimited by dashed-lines "---------------".
Section 1 has the SQL:
Section 2 has the gather_table_stats statements and the resulting bad plan (45 seconds)
Section 3 has the delete_table_stats statements and the resulting (and surpising to me) better plan (5 seconds).

Any assistance in debugging my issue is duly appreciated:

Thanks,
-- kr

<b>1) The SQL</b>
-------------- the SQL ------------------------------------------
SELECT
       FIN_OBJ_CD_NM, FIN_OBJECT_CD, RPT_SECTION, MAJOR_GROUP_CD,
       MINOR_GROUP_CD,
       MAJOR_GROUP_NM,
       MINOR_GROUP_NM,
       FIN_OBJ_LEVEL_CD,
       FIN_OBJ_LEVEL_NM,
       FIN_REPORT_SORT_CD,
       NVL(COL_1_ACTUALS, 0.00) AS COL_1_ACTUALS,
       NVL(COL_2_ACTUALS, 0.00) AS COL_2_ACTUALS,
       NVL(COL_3_ACTUALS, 0.00) AS COL_3_ACTUALS,
       NVL(COL_4_ACTUALS, 0.00) AS COL_4_ACTUALS,
       NVL(COL_5_ACTUALS, 0.00) AS COL_5_ACTUALS,
       NVL(COL_6_ACTUALS, 0.00) AS COL_6_ACTUALS,
       NVL(COL_7_ACTUALS, 0.00) AS COL_7_ACTUALS,
       NVL(COL_8_ACTUALS, 0.00) AS COL_8_ACTUALS
  FROM (
      SELECT RPT_SECTION,
             MAJOR_GROUP_CD,
             MINOR_GROUP_CD,
             MAJOR_GROUP_NM,
             MINOR_GROUP_NM,
             FIN_OBJ_LEVEL_CD,
             FIN_OBJ_LEVEL_NM,
             FIN_OBJECT_CD,
             FIN_OBJ_CD_NM,
             FIN_REPORT_SORT_CD,
             SUBSTR(RPT_CATEGORY,1,1) AS RPT_CATEGORY_CD,
             SUM(ACTUALS) AS ACTUALS
        FROM (
            SELECT
                  'A' AS RPT_SECTION,
                  SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) AS MAJOR_GROUP_CD,
                  OBJ.FIN_CONS_OBJ_CD AS MINOR_GROUP_CD,
                  OBJ.FIN_CONS_OBJ_NM AS MINOR_GROUP_NM,
                  CASE
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'A' THEN '0AASSETS'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'D' THEN '0DLIABILITIES'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'G' THEN
                        '0GDEFERRED INFLOWS OF RESOURCES'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'J' THEN '0JNET POSITION'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'M' THEN '0MOPERATING REVENUES'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'P' THEN '0POPERATING EXPENSES'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'R' THEN
                        '0RTRANSFERS AMONG FUNDS - ADDITIONS (DEDUCTIONS)'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'U' THEN
                        '0UNONOPERATING REVENUES (EXPENSES)'
                    WHEN SUBSTR(OBJ.FIN_OBJ_SORT_CD_MD, 1,1) = 'X' THEN
                        '0XOTHER REVENUES (EXPENSES)'
                  END AS MAJOR_GROUP_NM,
                  OBJ.FIN_OBJ_LEVEL_CD,
                  OBJ.FIN_OBJ_LEVEL_NM,
                  LED.OBJECT_CD AS FIN_OBJECT_CD, OBJ.FIN_OBJ_CD_NM,
                  ACCT.FIN_HGH_ED_FUNC_CD,
                  ACCT.SUB_FUND_GRP_CD, ACCT.SUB_FUND_GRP_DESC,
                  ACCT_AC_YTD_AMT AS ACTUALS,
                  CASE
                    WHEN ACCT.SUB_FUND_GRP_CD IN ('401100', '401105', '401110', '402100', '402105', '402110', '403105', '403110', '404100') THEN '1Educ and Gen Unrestricted'
                    WHEN ACCT.SUB_FUND_GRP_CD IN ('402115', '402120') THEN '2Auxiliary Unrestricted'
                    WHEN ACCT.SUB_FUND_GRP_CD = '464100' THEN '3Loan'
                    WHEN ACCT.SUB_FUND_GRP_CD = '494100' THEN '4Agency'
                    WHEN ACCT.SUB_FUND_GRP_CD = '474100' THEN '5Endowment'
                    ELSE 'XBalOther'
                  END AS RPT_CATEGORY,
                  OBJ.FIN_OBJ_SORT_CD_MD AS FIN_REPORT_SORT_CD
              FROM KRAHIM.LDGR_BAL_F LED,
                   KRAHIM.OBJ_CODE_D OBJ,
                   KRAHIM.ACCOUNT_CAD ACCT
              WHERE ACCT.FIN_COA_CD = '01'
                AND LED.FISCAL_YEAR = 2017
                AND LED.FISCAL_PERIOD_CD = '09'
                AND LED.CHART_CD = ACCT.FIN_COA_CD
                AND LED.CHART_CD = '01'
                AND LED.ACCOUNT_NBR = ACCT.ACCOUNT_NBR
                AND LED.OBJECT_CD NOT LIKE '899%'
                AND LED.ACCOUNT_NBR NOT LIKE '0000%'
                AND LED.ACCT_FIN_OBJ_TYPE IN ('IN', 'EX', 'TI', 'TE', 'EE', 'ES', 'IC', 'FB')
                AND OBJ.FIN_OBJECT_CD = LED.OBJECT_CD
                AND OBJ.UNIV_FISCAL_YR = LED.FISCAL_YEAR
                AND OBJ.FIN_COA_CD = LED.CHART_CD
                AND OBJ.FIN_OBJ_ACTIVE_CD = 'Y'
        )
        GROUP BY
             MAJOR_GROUP_CD,
             MINOR_GROUP_CD,
             MAJOR_GROUP_NM,
             MINOR_GROUP_NM,
             FIN_OBJ_LEVEL_CD,
             FIN_OBJ_LEVEL_NM,
             FIN_OBJECT_CD,
             FIN_OBJ_CD_NM,
             FIN_REPORT_SORT_CD,
             SUBSTR(RPT_CATEGORY,1,1),
             RPT_SECTION
    ) PIVOT (SUM(ACTUALS) AS ACTUALS
        FOR (RPT_CATEGORY_CD)
        IN ( '1' COL_1,
             '2' COL_2,
             '3' COL_3,
             '4' COL_4,
             '5' COL_5,
             '6' COL_6,
             '7' COL_7,
             '8' COL_8 )
    )
  WHERE COL_1_ACTUALS != 0
     OR COL_2_ACTUALS != 0
     OR COL_3_ACTUALS != 0
     OR COL_4_ACTUALS != 0
     OR COL_5_ACTUALS != 0
     OR COL_6_ACTUALS != 0
     OR COL_7_ACTUALS != 0
     OR COL_8_ACTUALS != 0
  ORDER BY
    MAJOR_GROUP_CD,
    FIN_REPORT_SORT_CD,
    MINOR_GROUP_CD,
    FIN_OBJ_LEVEL_CD,
    FIN_OBJ_LEVEL_NM,
    FIN_OBJECT_CD ASC
/

----------------------- End SQL ----------------------------------------------


<b>2) Build stats and the resulting plan (slow)</b>
------------------------------------------------------------------------------

Approximate Elapsed Time: 45 seconds

begin
   dbms_stats.gather_table_stats('KRAHIM','LDGR_BAL_F', NO_INVALIDATE=>False);
   dbms_stats.gather_table_stats('KRAHIM','OBJ_CODE_D', NO_INVALIDATE=>False);
   dbms_stats.gather_table_stats('KRAHIM','ACCOUNT_CAD', NO_INVALIDATE=>False);
end;
/
Plan hash value: 2621028777                                                                                                            


----------------------------------------------------------------------------------------------------------------                       
| Id  | Operation                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                       
----------------------------------------------------------------------------------------------------------------                       
|   0 | SELECT STATEMENT                  |                    | 87781 |    11M|       |   133K  (1)| 00:42:59 |                       
|*  1 |  FILTER                           |                    |       |       |       |            |          |                       
|   2 |   SORT GROUP BY PIVOT             |                    | 87781 |    11M|    41M|   133K  (1)| 00:42:59 |                       
|   3 |    VIEW                           |                    |   260K|    33M|       |   130K  (1)| 00:41:59 |                       
|   4 |     HASH GROUP BY                 |                    |   260K|    34M|    38M|   130K  (1)| 00:41:59 |                       
|*  5 |      HASH JOIN                    |                    |   260K|    34M|       |   123K  (1)| 00:39:50 |                       
|*  6 |       TABLE ACCESS FULL           | ACCOUNT_CAD        | 39141 |   688K|       |  1268   (1)| 00:00:25 |                       
|*  7 |       HASH JOIN                   |                    |   238K|    27M|       |   122K  (1)| 00:39:25 |                       
|*  8 |        TABLE ACCESS BY INDEX ROWID| OBJ_CODE_D         |   895 | 82340 |       |   196   (0)| 00:00:04 |                       
|*  9 |         INDEX RANGE SCAN          | CA_OBJECT_CODE_D04 |   914 |       |       |     5   (0)| 00:00:01 |                       
|* 10 |        TABLE ACCESS FULL          | LDGR_BAL_F         |   253K|  7416K|       |   122K  (1)| 00:39:21 |                       
----------------------------------------------------------------------------------------------------------------                       
Predicate Information (identified by operation id):                                                                                    
---------------------------------------------------                                                                                    
                                                                                                                                       
   1 - filter((SUM(CASE  WHEN ("RPT_CATEGORY_CD"='1') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN                                        
              ("RPT_CATEGORY_CD"='2') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='3') THEN                           
              "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='4') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN                   
              ("RPT_CATEGORY_CD"='5') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='6') THEN                           
              "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='7') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN                   
              ("RPT_CATEGORY_CD"='8') THEN "ACTUALS" END )<>0))                                                                        
   5 - access("LED"."CHART_CD"="ACCT"."FIN_COA_CD" AND "LED"."ACCOUNT_NBR"="ACCT"."ACCOUNT_NBR")                                       
   6 - filter("ACCT"."FIN_COA_CD"='01')                                                                                                
   7 - access("OBJ"."FIN_OBJECT_CD"="LED"."OBJECT_CD" AND "OBJ"."UNIV_FISCAL_YR"="LED"."FISCAL_YEAR"                                   
              AND "OBJ"."FIN_COA_CD"="LED"."CHART_CD")                                                                                 
   8 - filter("OBJ"."FIN_OBJ_ACTIVE_CD"='Y')                                                                                           
   9 - access("OBJ"."UNIV_FISCAL_YR"=2017 AND "OBJ"."FIN_COA_CD"='01')                                                                 
  10 - filter("LED"."FISCAL_PERIOD_CD"='09' AND "LED"."FISCAL_YEAR"=2017 AND "LED"."CHART_CD"='01' AND                                 
              ("LED"."ACCT_FIN_OBJ_TYPE"='EE' OR "LED"."ACCT_FIN_OBJ_TYPE"='ES' OR "LED"."ACCT_FIN_OBJ_TYPE"='EX' OR                   
              "LED"."ACCT_FIN_OBJ_TYPE"='FB' OR "LED"."ACCT_FIN_OBJ_TYPE"='IC' OR "LED"."ACCT_FIN_OBJ_TYPE"='IN' OR                    
              "LED"."ACCT_FIN_OBJ_TYPE"='TE' OR "LED"."ACCT_FIN_OBJ_TYPE"='TI') AND "LED"."OBJECT_CD" NOT LIKE '899%'            
              AND "LED"."ACCOUNT_NBR" NOT LIKE '0000%')

-------------------------- END PLAN WITH STATS ---------------------------------------------



<b> 3) Deleted STATS and the resulting plan (fast) </b>
---------------------------------------------------------------------------------------------

Approximate Elapsed Time: < 5 seconds (average)

Plan hash value: 3757553759                                                                                                            
---------------------------------------------------------------------------------------------------------                              
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                              
---------------------------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                  |                     |    62 | 11842 |  3622   (1)| 00:01:10 |                              
|*  1 |  FILTER                           |                     |       |       |            |          |                              
|   2 |   SORT GROUP BY PIVOT             |                     |    62 | 11842 |  3622   (1)| 00:01:10 |                              
|   3 |    VIEW                           |                     |   134K|    24M|  3617   (1)| 00:01:10 |                              
|   4 |     HASH GROUP BY                 |                     |   134K|    27M|  3617   (1)| 00:01:10 |                              
|*  5 |      HASH JOIN                    |                     |   134K|    27M|  3612   (1)| 00:01:10 |                              
|*  6 |       TABLE ACCESS BY INDEX ROWID | OBJ_CODE_D          |   345 | 55545 |   148   (0)| 00:00:03 |                              
|*  7 |        INDEX RANGE SCAN           | CA_OBJECT_CODE_D04  |   345 |       |     3   (0)| 00:00:01 |                              
|*  8 |       HASH JOIN                   |                     |   111K|  6187K|  3463   (1)| 00:01:07 |                              
|*  9 |        TABLE ACCESS FULL          | ACCOUNT_CAD         | 43603 |   553K|  1268   (1)| 00:00:25 |                              
|* 10 |        TABLE ACCESS BY INDEX ROWID| LDGR_BAL_F          |   111K|  4776K|  2194   (1)| 00:00:43 |                              
|* 11 |         INDEX RANGE SCAN          | LEDGER_BALANCES_F10 |  5558 |       |   446   (1)| 00:00:09 |                              
---------------------------------------------------------------------------------------------------------                              

Predicate Information (identified by operation id):                                                                                    
---------------------------------------------------                                                                                    
                                                                                                                                       
   1 - filter((SUM(CASE  WHEN ("RPT_CATEGORY_CD"='1') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN                                        
              ("RPT_CATEGORY_CD"='2') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='3') THEN                           
              "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='4') THEN "ACTUALS" END )<>0 OR SUM(CASE                         
               WHEN ("RPT_CATEGORY_CD"='5') THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='6')                          
              THEN "ACTUALS" END )<>0 OR SUM(CASE  WHEN ("RPT_CATEGORY_CD"='7') THEN "ACTUALS" END )<>0 OR                             
              SUM(CASE  WHEN ("RPT_CATEGORY_CD"='8') THEN "ACTUALS" END )<>0))                                                         
   5 - access("OBJ"."FIN_OBJECT_CD"="LED"."OBJECT_CD" AND                                                                              
              "OBJ"."UNIV_FISCAL_YR"="LED"."FISCAL_YEAR" AND "OBJ"."FIN_COA_CD"="LED"."CHART_CD")                                      
   6 - filter("OBJ"."FIN_OBJ_ACTIVE_CD"='Y')                                                                                           
   7 - access("OBJ"."UNIV_FISCAL_YR"=2017 AND "OBJ"."FIN_COA_CD"='01')                                                                 
   8 - access("LED"."CHART_CD"="ACCT"."FIN_COA_CD" AND "LED"."ACCOUNT_NBR"="ACCT"."ACCOUNT_NBR")                                       
   9 - filter("ACCT"."FIN_COA_CD"='01')                                                                                                
  10 - filter(("LED"."ACCT_FIN_OBJ_TYPE"='EE' OR "LED"."ACCT_FIN_OBJ_TYPE"='ES' OR                                                     
              "LED"."ACCT_FIN_OBJ_TYPE"='EX' OR "LED"."ACCT_FIN_OBJ_TYPE"='FB' OR                                                      
              "LED"."ACCT_FIN_OBJ_TYPE"='IC' OR "LED"."ACCT_FIN_OBJ_TYPE"='IN' OR                                                      
              "LED"."ACCT_FIN_OBJ_TYPE"='TE' OR "LED"."ACCT_FIN_OBJ_TYPE"='TI') AND "LED"."OBJECT_CD" NOT LIKE          
              '899%')                                                                                                                  
  11 - access("LED"."FISCAL_YEAR"=2017 AND "LED"."FISCAL_PERIOD_CD"='09' AND                                                           
              "LED"."CHART_CD"='01')                                                                                                   
       filter("LED"."ACCOUNT_NBR" NOT LIKE '0000%')                                                                                    
                                                                                                                                       
Note                                                                                                                                   
-----                                                                                                                                  
   - dynamic sampling used for this statement (level=2)  

------------------------- END no stats and the good plan ---------------------------------










and Chris said...

Although having up-to-date stats will generally produce better plans, it doesn't guarantee it as you've spotted. If you have no stats on your tables, the optimizer will fall back on dynamic sampling. You can see this by the note

- dynamic sampling used for this statement (level=2)


in the fast plan. This does a "mini gather" of stats which is less thorough than full stats gathering. Usually this is less accurate. But in your case works out for the better.

https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer

So to figure out why full stats give the "wrong" plan, the first thing to do is find out how good its estimates are. You do this by generating an execution plan. This includes the actual rows each step processes. For instructions on how to do this, see:

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

In cases where the estimated rows is an order of magnitude different to the actual (or more), there's a good chance you have the wrong plan. So you need to start narrowing down why this is happening. There are many things that could cause this. Histograms are a common culprit though. So check if you have any after you gather stats:

select * from sys.user_tab_col_statistics
where  histogram <> 'NONE';


You also have complex predicates against LDGR_BAL_F. And this table switches from a full scan (in the slow plan) to an index (in the fast plan). So it may be worth investigating whether creating extended stats on the columns in the LEDGER_BALANCES_F10 index help.

https://blogs.oracle.com/optimizer/entry/extended_statistics

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what you suspected, led me to the solution

KHALID RAHIM, March 07, 2017 - 12:28 pm UTC

Hi Chris,

Both your suspiscions were correct:
1) histograms could be the culprit and 2) the combination of joins is messing up the cardinality estimate (therefore use extended stats)

Actually I had to do both 1) get rid of histograms (not sure why, there are no binds here) and 2) do extended stats. I had a case similar to what's described in this article, by your new askTom contributor ( https://blogs.oracle.com/optimizer/entry/extended_statistics ) in my case the CHART_CD column was heavily skewed (chart_cd='01' for 90% or more of the rows.

Therefore, this combination worked for me, I got the good plan
/*** extended stats ***/
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('KRAHIM', 'LDGR_BAL_F', '(FISCAL_YEAR,FISCAL_PERIOD_CD, CHART_CD)') FROM DUAL;

begin
dbms_stats.gather_table_stats('KRAHIM','LDGR_BAL_F', method_opt => 'for all columns size 1',
cascade=>True, degree=>8, NO_INVALIDATE=>False);
dbms_stats.gather_table_stats('KRAHIM','OBJ_CODE_D', method_opt => 'for all columns size 1',
cascade=>True, degree=>8, NO_INVALIDATE=>False);
dbms_stats.gather_table_stats('KRAHIM','ACCOUNT_CAD', method_opt => 'for all columns size 1',
cascade=>True, degree=>8, NO_INVALIDATE=>False);
end;
/

Last but not the least, I had no idea that SQL-Developer execution plan gives me more info than the SQL*PLUS plan until I read your article. BTW, I do use an execution plan (/*+ GATHER_PLAN_STATISTICS */) and noticed that the estimates were off. But I like what SQL*Developer does, and will be using it more for perf diagnosis.

Thanks very much, it helped me a great deal,
-- kr
Chris Saxon
March 07, 2017 - 2:41 pm UTC

Glad to hear you got it sorted! :D

FYI: you don't have to have binds for the optimizer to use a histogram. It can use them on literal values too.

what I meant was ...

KHALID RAHIM, March 08, 2017 - 5:27 am UTC

Chris,

So, what I was trying to say was more like ...
I keep hearing that histograms are known to cause unexpected outcomes when dealing with binds,
see what this top data sleuth has to say,
http://allthingsoracle.com/histograms-part-1-why/
I feel like he was talking about my case except I don't use binds, and also:
https://hoopercharles.wordpress.com/2011/01/29/histograms-and-bind-variables-but-why/

but I don't have any binds in my SQL, and yet I got the good plan only when I canceled
the histograms with (size 1), in addition to extended-stats

Thanks,
-- kr

Chris Saxon
March 08, 2017 - 3:18 pm UTC

They can still cause problems with literals if you have more than 254 values in the column. In this case it switches to a height-balanced histogram.

This can cause problems if you have values that are "not quite popular" or "only just popular". i.e. those that fill most of a bucket but not the endpoint or slightly larger than one bucket, but still cover two endpoints respectively. This can lead to the optimizer under or overestimating the likelihood of these values.

And the threats Jonathan mentions for frequency histograms in the article you've linked:

They can be very unstable when sampled
You have to collect them at the right moment


also apply to height-balanced histograms.

In 12c you can increase this up to 2,048 values and it'll go to either a top-N or hybrid histogram instead. These improve the issues somewhat, but can still lead to mis-estimates.

http://docs.oracle.com/database/122/TGSQL/histograms.htm#TGSQL366

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.