问题描述
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
1) The SQL
-------------- 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 ----------------------------------------------
2) Build stats and the resulting plan (slow)
------------------------------------------------------------------------------
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 ---------------------------------------------
3) Deleted STATS and the resulting plan (fast)
---------------------------------------------------------------------------------------------
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 ---------------------------------
复制 专家解答
虽然拥有最新的统计数据通常会产生更好的计划，但它并不能保证你已经发现了这一点。如果您的表上没有统计信息，则优化器将退回到动态采样。你可以通过便条看到这个
在快速计划中。这对统计数据进行了 “迷你收集”，这比完整的统计数据收集要不那么彻底。通常这不太准确。但在你的情况下会更好。
https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer
因此，要弄清楚为什么完整的统计数据给出了 “错误” 的计划，首先要做的就是找出它的估计有多好。你可以通过生成一个execution计划。这包括每个步骤处理的实际行。有关如何执行此操作的说明，请参见:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution
如果估计的行与实际 (或更多) 相差一个数量级，则很有可能您有错误的计划。所以你需要开始缩小为什么会发生这种情况。有很多事情可能会导致这种情况。直方图是常见的罪魁祸首。因此，在收集统计数据后检查是否有任何数据:
你也有针对LDGR_BAL_F的复杂谓词。并且此表从全扫描 (在慢速计划中) 切换到索引 (在快速计划中)。因此，可能值得研究是否在LEDGER_BALANCES_F10索引帮助中的列上创建扩展统计信息。
https://blogs.oracle.com/optimizer/entry/extended_statistics
- dynamic sampling used for this statement (level=2)复制
在快速计划中。这对统计数据进行了 “迷你收集”，这比完整的统计数据收集要不那么彻底。通常这不太准确。但在你的情况下会更好。
https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer
因此，要弄清楚为什么完整的统计数据给出了 “错误” 的计划，首先要做的就是找出它的估计有多好。你可以通过生成一个execution计划。这包括每个步骤处理的实际行。有关如何执行此操作的说明，请参见:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution
如果估计的行与实际 (或更多) 相差一个数量级，则很有可能您有错误的计划。所以你需要开始缩小为什么会发生这种情况。有很多事情可能会导致这种情况。直方图是常见的罪魁祸首。因此，在收集统计数据后检查是否有任何数据:
select * from sys.user_tab_col_statistics where histogram <> 'NONE';复制
你也有针对LDGR_BAL_F的复杂谓词。并且此表从全扫描 (在慢速计划中) 切换到索引 (在快速计划中)。因此，可能值得研究是否在LEDGER_BALANCES_F10索引帮助中的列上创建扩展统计信息。
https://blogs.oracle.com/optimizer/entry/extended_statistics
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
708次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
636次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
548次阅读
2025-04-20 10:07:02
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
495次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
490次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
488次阅读
2025-04-22 00:13:51
一页概览：Oracle GoldenGate
甲骨文云技术
471次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
419次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
373次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
360次阅读
2025-05-05 19:28:36
