[20250303]提示冲突问题.txt

来源：这里教程网时间：2026-03-03 21:44:34 作者：

[20250303]提示冲突问题.txt --//很久没做生产系统优化,春节前遇到的问题,一直没看,我当时也没有仔细看执行计划，以为是前几天修改awr收集保存时间参数导致的，直接 --//执行 exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>11520, interval=>60);，修改为原来的保存天数8天。 --//今天仔细检查发现问题语句还是存在，仔细检查才发现以前就遇到过，生产系统监控sql语句提示冲突问题,那些所谓的监控语句就是 --//垃圾.再写一篇说明问题: 1.环境: SYS@127.0.0.1:9105/xtdb/xtdb1> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.问题语句: SYS@127.0.0.1:9106/xtdb/xtdb2> @ sql_id 8z238764ng29v -- SQL_ID = 8z238764ng29v come from shared pool SELECT 'PERCENTAGES', 'Buffer Hit %' ROWINFO, ( 1 - ( (SELECT (e.VALUE - b.VALUE) FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE b.stat_name = e.stat_name AND b.stat_name IN ('physical reads') AND b.snap_id = 22238 AND e.snap_id = 22239 AND b.instance_number = 1 AND e.instance_number = 1 AND b.dbid = 1553461694 AND e.dbid = 1553461694) - (SELECT (e.VALUE - b.VALUE) FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE b.stat_name = e.stat_name AND b.stat_name IN ('physical reads direct') AND b.snap_id = 22238 AND e.snap_id = 22239 AND b.instance_number = 1 AND e.instance_number = 1 AND b.dbid = 1553461694 AND e.dbid = 1553461694) - NVL ( (SELECT (e.VALUE - b.VALUE) FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e WHERE b.stat_name = e.stat_name AND b.stat_name IN ('physical reads direct (lob)') AND b.snap_id = 22238 AND e.snap_id = 22239 AND b.instance_number = 1 AND e.instance_number = 1 AND b.dbid = 1553461694 AND e.dbid = 1553461694), 0 )) / 76783588) AS val FROM DUAL; --//做了格式化处理，原始代码就1行。 --//每10分钟执行1次，这些统计在1个小时内根本没有变化，这不是忽悠人吗？难道有问题我自己不会看吗？ --//那些所谓开发人员真的不会写sql语句,应该单独1次抽取相关值: b.stat_name IN ('physical reads direct (lob)','physical reads','physical reads direct') --//注:脚本里面76783588猜测应该是总逻辑读. SELECT snap_id,instance_number,VALUE, stat_name FROM DBA_HIST_SYSSTAT WHERE stat_name IN ('physical reads direct (lob)', 'physical reads', 'physical reads direct') AND snap_id IN (22238, 22239) AND instance_number = 1 AND dbid = 1553461694; --//然后通过结果集做处理,开始连sql连接都不需要,写出这样的代码我都替他们丢脸... --//我贴出我写的sql语句的执行计划部分,上面的问题sql语句执行计划太复杂，每次执行需要2X秒。 SQL_ID 7g3t3p7z4ycxg, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ snap_id,instance_number,VALUE, stat_name FROM DBA_HIST_SYSSTAT WHERE stat_name IN ('physical reads direct (lob)', 'physical reads', 'physical reads direct') AND snap_id IN (22238, 22239) AND instance_number = 1 AND dbid = 1553461694 Plan hash value: 1634470827 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 142 (100)| | | | 6 |00:00:00.01 | 8428 | | | | | 1 | NESTED LOOPS | | 1 | 1 | 89 | 142 (1)| 00:00:01 | | | 6 |00:00:00.01 | 8428 | | | | | 2 | NESTED LOOPS | | 1 | 1 | 89 | 142 (1)| 00:00:01 | | | 4072 |00:00:00.02 | 4597 | | | | |* 3 | HASH JOIN | | 1 | 1 | 43 | 140 (1)| 00:00:01 | | | 4072 |00:00:00.01 | 505 | 3289K| 3289K| 682K (0)| | 4 | JOIN FILTER CREATE | :BF0001 | 1 | 1 | 18 | 3 (0)| 00:00:01 | | | 2 |00:00:00.01 | 6 | | | | | 5 | PART JOIN FILTER CREATE | :BF0000 | 1 | 1 | 18 | 3 (0)| 00:00:01 | | | 2 |00:00:00.01 | 6 | | | | | 6 | INLIST ITERATOR | | 1 | | | | | | | 2 |00:00:00.01 | 6 | | | | |* 7 | TABLE ACCESS BY INDEX ROWID| WRM$_SNAPSHOT | 2 | 1 | 18 | 3 (0)| 00:00:01 | | | 2 |00:00:00.01 | 6 | | | | |* 8 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 2 | 1 | | 2 (0)| 00:00:01 | | | 2 |00:00:00.01 | 4 | 1025K| 1025K| | | 9 | JOIN FILTER USE | :BF0001 | 1 | 4072 | 99K| 137 (1)| 00:00:01 | | | 4072 |00:00:00.01 | 499 | | | | | 10 | PARTITION RANGE AND | | 1 | 4072 | 99K| 137 (1)| 00:00:01 |KEY(AP)|KEY(AP)| 4072 |00:00:00.01 | 499 | | | | |* 11 | TABLE ACCESS STORAGE FULL | WRH$_SYSSTAT | 1 | 4072 | 99K| 137 (1)| 00:00:01 |KEY(AP)|KEY(AP)| 4072 |00:00:00.01 | 499 | 1025K| 1025K| | |* 12 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 4072 | 1 | | 1 (0)| 00:00:01 | | | 4072 |00:00:00.01 | 4092 | 1025K| 1025K| | |* 13 | TABLE ACCESS BY INDEX ROWID | WRH$_STAT_NAME | 4072 | 1 | 46 | 2 (0)| 00:00:01 | | | 6 |00:00:00.01 | 3831 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5C160134 7 - SEL$5C160134 / SN@SEL$3 8 - SEL$5C160134 / SN@SEL$3 11 - SEL$5C160134 / S@SEL$3 12 - SEL$5C160134 / NM@SEL$3 13 - SEL$5C160134 / NM@SEL$3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_optimizer_extended_cursor_sharing' 'none') OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_optimizer_gather_stats_on_load' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_gather_feedback' 'false') OPT_PARAM('_optimizer_aggr_groupby_elim' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$5C160134") MERGE(@"SEL$335DD26A" >"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$335DD26A") MERGE(@"SEL$3" >"SEL$2") OUTLINE(@"SEL$2") OUTLINE(@"SEL$3") INDEX_RS_ASC(@"SEL$5C160134" "SN"@"SEL$3" ("WRM$_SNAPSHOT"."DBID" "WRM$_SNAPSHOT"."SNAP_ID" "WRM$_SNAPSHOT"."INSTANCE_NUMBER")) FULL(@"SEL$5C160134" "S"@"SEL$3") INDEX(@"SEL$5C160134" "NM"@"SEL$3" ("WRH$_STAT_NAME"."DBID" "WRH$_STAT_NAME"."STAT_ID" "WRH$_STAT_NAME"."CON_DBID")) LEADING(@"SEL$5C160134" "SN"@"SEL$3" "S"@"SEL$3" "NM"@"SEL$3") USE_HASH(@"SEL$5C160134" "S"@"SEL$3") ++++++++++++++++++++++++++++++++ USE_NL(@"SEL$5C160134" "NM"@"SEL$3") NLJ_BATCHING(@"SEL$5C160134" "NM"@"SEL$3") PX_JOIN_FILTER(@"SEL$5C160134" "S"@"SEL$3") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("S"."DBID"="SN"."DBID" AND "S"."SNAP_ID"="SN"."SNAP_ID" AND "S"."INSTANCE_NUMBER"="SN"."INSTANCE_NUMBER") 7 - filter("SN"."STATUS"=0) 8 - access("SN"."DBID"=1553461694 AND (("SN"."SNAP_ID"=22238 OR "SN"."SNAP_ID"=22239)) AND "SN"."INSTANCE_NUMBER"=1) 11 - storage(("S"."INSTANCE_NUMBER"=1 AND INTERNAL_FUNCTION("S"."SNAP_ID") AND "S"."DBID"=1553461694 AND SYS_OP_BLOOM_FILTER(:BF0001,"S"."SNAP_ID","S"."DBID","S"."INSTANCE_NUMBER"))) filter(("S"."INSTANCE_NUMBER"=1 AND INTERNAL_FUNCTION("S"."SNAP_ID") AND "S"."DBID"=1553461694 AND SYS_OP_BLOOM_FILTER(:BF0001,"S"."SNAP_ID","S"."DBID","S"."INSTANCE_NUMBER"))) 12 - access("NM"."DBID"=1553461694 AND "S"."STAT_ID"="NM"."STAT_ID") 13 - filter(("NM"."STAT_NAME"='physical reads' OR "NM"."STAT_NAME"='physical reads direct' OR "NM"."STAT_NAME"='physical reads direct (lob)')) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "VALUE"[NUMBER,22], "NM"."STAT_NAME"[VARCHAR2,64] 2 - "VALUE"[NUMBER,22], "NM".ROWID[ROWID,10] 3 - (#keys=3) "VALUE"[NUMBER,22], "S"."STAT_ID"[NUMBER,22] 4 - "SN"."DBID"[NUMBER,22], "SN"."SNAP_ID"[NUMBER,22], "SN"."SNAP_ID"[NUMBER,22], "SN"."DBID"[NUMBER,22], "SN"."INSTANCE_NUMBER"[NUMBER,22] 5 - "SN"."DBID"[NUMBER,22], "SN"."SNAP_ID"[NUMBER,22], "SN"."SNAP_ID"[NUMBER,22], "SN"."DBID"[NUMBER,22], "SN"."INSTANCE_NUMBER"[NUMBER,22] 6 - "SN"."SNAP_ID"[NUMBER,22], "SN"."DBID"[NUMBER,22], "SN"."INSTANCE_NUMBER"[NUMBER,22] 7 - "SN"."SNAP_ID"[NUMBER,22], "SN"."DBID"[NUMBER,22], "SN"."INSTANCE_NUMBER"[NUMBER,22] 8 - "SN".ROWID[ROWID,10], "SN"."DBID"[NUMBER,22], "SN"."SNAP_ID"[NUMBER,22], "SN"."INSTANCE_NUMBER"[NUMBER,22] 9 - "S"."SNAP_ID"[NUMBER,22], "S"."DBID"[NUMBER,22], "S"."INSTANCE_NUMBER"[NUMBER,22], "S"."STAT_ID"[NUMBER,22], "VALUE"[NUMBER,22] 10 - "S"."SNAP_ID"[NUMBER,22], "S"."DBID"[NUMBER,22], "S"."INSTANCE_NUMBER"[NUMBER,22], "S"."STAT_ID"[NUMBER,22], "VALUE"[NUMBER,22] 11 - "S"."SNAP_ID"[NUMBER,22], "S"."DBID"[NUMBER,22], "S"."INSTANCE_NUMBER"[NUMBER,22], "S"."STAT_ID"[NUMBER,22], "VALUE"[NUMBER,22] 12 - "NM".ROWID[ROWID,10] 13 - "NM"."STAT_NAME"[VARCHAR2,64] Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$5C160134 - leading(sn s nm) 7 - SEL$5C160134 / SN@SEL$3 U - use_hash(sn s) 11 - SEL$5C160134 / S@SEL$3 - use_hash(sn s) --//注意看Total hints for statement: 3 (U - Unused (1))部分,我根本没有加入提示,而oracle出现提示冲突.而且使用了leading(sn --//s nm)提示.导致id=12,13要执行4072次,如果WRH$_SYSSTAT记录的天数不是缺省的8条,那简直就是灾难.注我春节前将记录awr的历史 --//修改回来缺省值. --//给亏在19c，不然很难注意提示冲突导致执行计划不合理。 --//不过还是觉得很奇怪，开始提示U=>Unused,后面有提示use_hash(sn s)，到底是有用还是没用。 --//后记：实际上这样的写法use_hash(sn s)是错误的，前面的sn是多余的，真正被hash连接的表是S，注意看前面的+++++部分， --//USE_HASH(@"SEL$5C160134" "S"@"SEL$3") ，这样提示id=11有用的原因。 SYS@127.0.0.1:9105/xtdb/xtdb1> @ ddl dba_hist_sysstat C300 --------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_HIST_SYSSTAT" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "STAT_ID", "STAT_NAME", "VALUE", "CON_DBID", "CON_ID") AS select "SNAP_ID","DBID","INSTANCE_NUMBER","STAT_ID","STAT_NAME","VALUE","CON_DBID","CON_ID" from AWR_CDB_SYSSTAT; SYS@127.0.0.1:9105/xtdb/xtdb1> @ ddl AWR_CDB_SYSSTAT C300 -------------------------------------------------------------------------------------------------- CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."AWR_CDB_SYSSTAT" CONTAINER_DATA ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "STAT_ID", "STAT_NAME", "VALUE", "CON_DBID", "CON_ID") AS select /*+ leading(sn s nm) use_hash(sn s) */ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ s.snap_id, s.dbid, s.instance_number, s.stat_id, nm.stat_name, value, decode(s.con_dbid, 0, s.dbid, s.con_dbid), con_dbid_to_id(decode(s.con_dbid, 0, s.dbid, s.con_dbid)) con_id from WRM$_SNAPSHOT sn, WRH$_SYSSTAT s, WRH$_STAT_NAME nm where s.stat_id = nm.stat_id and s.dbid = nm.dbid and s.snap_id = sn.snap_id and s.dbid = sn.dbid and s.instance_number = sn.instance_number and sn.status = 0; --//注意看下划线提示，正是这个提示导致选择不合理的执行计划。 --//顺便提一下这个问题在21c下也存在. --//以前我是直接使用sql profile来稳定执行计划，这次不再浪费时间，直接修改视图定义： --//保险期间，在删除提示前在toad下抽取视图定义并保存下来，便于出现"问题"后还原： CREATE OR REPLACE FORCE VIEW SYS.AWR_CDB_SYSSTAT (SNAP_ID, DBID, INSTANCE_NUMBER, STAT_ID, STAT_NAME, VALUE, CON_DBID, CON_ID) BEQUEATH DEFINER AS SELECT /*+ ,leading(sn s nm) use_hash(sn s) */ s.snap_id ,s.dbid ,s.instance_number ,s.stat_id ,nm.stat_name ,VALUE ,DECODE (s.con_dbid, 0, s.dbid, s.con_dbid) ,con_dbid_to_id (DECODE (s.con_dbid, 0, s.dbid, s.con_dbid)) con_id FROM WRM$_SNAPSHOT sn ,WRH$_SYSSTAT s ,WRH$_STAT_NAME nm WHERE s.stat_id = nm.stat_id AND s.dbid = nm.dbid AND s.snap_id = sn.snap_id AND s.dbid = sn.dbid AND s.instance_number = sn.instance_number AND sn.status = 0; --//注意提示前加入逗号，相当于提示无效。 COMMENT ON TABLE SYS.AWR_CDB_SYSSTAT IS 'System Historical Statistics Information'; CREATE OR REPLACE PUBLIC SYNONYM AWR_CDB_SYSSTAT FOR SYS.AWR_CDB_SYSSTAT; GRANT SELECT ON SYS.AWR_CDB_SYSSTAT TO SELECT_CATALOG_ROLE; --//修改里面的提示现在21c的测试环境下看看，删除提示前的执行计划： Plan hash value: 1240731716 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 319 (100)| | | | 6 |00:00:00.02 | 1157 | | | | |* 1 | HASH JOIN | | 1 | 7 | 602 | 319 (1)| 00:00:01 | | | 6 |00:00:00.02 | 1157 | 1162K| 1162K| 550K (0)| |* 2 | TABLE ACCESS FULL | WRH$_STAT_NAME | 1 | 3 | 138 | 7 (0)| 00:00:01 | | | 3 |00:00:00.01 | 22 | | | | | 3 | PARTITION RANGE AND | | 1 | 4504 | 175K| 312 (1)| 00:00:01 |KEY(I) |KEY(I) | 4504 |00:00:00.03 | 1135 | | | | |* 4 | HASH JOIN | | 1 | 4504 | 175K| 312 (1)| 00:00:01 | | | 4504 |00:00:00.03 | 1135 | 1572K| 1572K| 773K (0)| | 5 | INLIST ITERATOR | | 1 | | | | | | | 2 |00:00:00.01 | 4 | | | | |* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| WRM$_SNAPSHOT | 2 | 2 | 32 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) | 2 |00:00:00.01 | 4 | | | | |* 7 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 2 | 2 | | 1 (0)| 00:00:01 |KEY(I) |KEY(I) | 2 |00:00:00.01 | 2 | | | | |* 8 | TABLE ACCESS FULL | WRH$_SYSSTAT | 1 | 4504 | 105K| 309 (1)| 00:00:01 |KEY(I) |KEY(I) | 4504 |00:00:00.02 | 1131 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//leading(sn s nm) --//WRM$_SNAPSHOT sn, WRH$_SYSSTAT s, WRH$_STAT_NAME nm --//删除提示后： plan hash value: 3953948462 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 23 (100)| | | | 6 |00:00:00.01 | 51 | | 1 | NESTED LOOPS | | 1 | 7 | 602 | 23 (0)| 00:00:01 | | | 6 |00:00:00.01 | 51 | | 2 | NESTED LOOPS | | 1 | 7 | 602 | 23 (0)| 00:00:01 | | | 6 |00:00:00.01 | 45 | | 3 | NESTED LOOPS | | 1 | 7 | 490 | 22 (0)| 00:00:01 | | | 6 |00:00:00.01 | 42 | |* 4 | TABLE ACCESS FULL | WRH$_STAT_NAME | 1 | 3 | 138 | 7 (0)| 00:00:01 | | | 3 |00:00:00.01 | 22 | | 5 | INLIST ITERATOR | | 3 | | | | | | | 6 |00:00:00.01 | 20 | | 6 | PARTITION RANGE ITERATOR | | 6 | 2 | 48 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 6 |00:00:00.01 | 20 | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| WRH$_SYSSTAT | 6 | 2 | 48 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 6 |00:00:00.01 | 20 | |* 8 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 6 | 2 | | 3 (0)| 00:00:01 |KEY(I) |KEY(I) | 6 |00:00:00.01 | 14 | | 9 | PARTITION RANGE INLIST | | 6 | 1 | | 0 (0)| |KEY(I) |KEY(I) | 6 |00:00:00.01 | 3 | |* 10 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 6 | 1 | | 0 (0)| |KEY(I) |KEY(I) | 6 |00:00:00.01 | 3 | |* 11 | TABLE ACCESS BY LOCAL INDEX ROWID | WRM$_SNAPSHOT | 6 | 1 | 16 | 1 (0)| 00:00:01 | 1 | 1 | 6 |00:00:00.01 | 6 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//leading(sn s nm) --//WRM$_SNAPSHOT sn, WRH$_SYSSTAT s, WRH$_STAT_NAME nm --//相当于leading(nm s sn) --//理论讲不会存在任何问题，oracle的优化器能找到合理的执行计划，只要分析对应表。

编辑推荐：

返回列表

[20250303]提示冲突问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推