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 ---------------------------------
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