[20240321]提示冲突问题.txt

[20240321]提示冲突问题.txt --//今天优化一条监控sql语句,遇到提示冲突的问题,做一个记录: 1.环境: SYS@192.168.100.235:1521/orcl> @ pr ============================== 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.3.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@192.168.100.235:1521/orcl> @ fms.sql 5 15 &day FORCE_MATCHING_SIGNATURE SQL_ID_COUNT TOTAL_SECONDS ------------------------ ------------ -------------      7055080024159627634           23            65      6462934727001839235           20            21      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     17409746777760600368           14            16     11135749189737159965           12            16       829290737292318906           12            14     11135588330860454200            8            11     11034937719941372916            8            10      9685323836218277500            7             7      8097234170242639654            5             6 9 rows selected. --// 昨天的情况: SYS@192.168.100.235:1521/orcl> @ dfms.sql 5 15 sysdate-2 sysdate-1 FORCE_MATCHING_SIGNATURE SQL_ID_COUNT TOTAL_SECONDS ------------------------ ------------ -------------     17409746777760600368           43           960     11034937719941372916           23           770     11135588330860454200           22           460     16405634091854084691           21           390      8097234170242639654           18           380 --//很明显今天已经优化很多. SYS@192.168.100.235:1521/orcl> @ sqlff 6462934727001839235 .45 time unit : millisecond BEGIN_INTERVAL_TIME    INST_ID SQL_ID        PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC  AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC ------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- --------------- 2024-03-21 07:00:09          1 3pca49x8wzrnd      1617869433          7              28              25           1.0          3414              0               0         0.0               0               0               0 2024-03-21 07:00:09          1 3tv1zhzpktjtk      1617869433          7              26              25           1.0          3414              0               0         0.0               0               0               0 2024-03-21 07:00:09          1 bc20nn2pr4bgb      1617869433          7              22              21           1.0          3312              0               0         0.0               0               0               0 2024-03-21 07:00:09          1 dxvtb8qh04bkf      1617869433          7              23              23           1.0          3211              0               0         0.0               0               0               0 --//昨天已经优化,但是我在优化时遇到1个问题.优化后提示冲突,奇怪!!我抽取提示直接贴上来的不可能存在错误. $ cat dxvtb8qh04bkf.sql9dd_0 set termout off set sqlblanklines on alter session set current_schema=ZGYWMONITOR; alter session set statistics_level=all; --SQL_ID = dxvtb8qh04bkf SELECT 'SORT_ROWS' ROWINFO, e.value - b.value va   FROM dba_hist_sysstat b, dba_hist_sysstat e  WHERE b.snap_id         = 29031    AND e.snap_id         = 29032    AND b.dbid            = 1585360079    AND e.dbid            = 1585360079    AND b.instance_number = 1    AND e.instance_number = 1    AND b.stat_id         = e.stat_id    AND e.stat_name IN ('sorts (rows)'); set termout on set sqlblanklines off --@zws '' '' --@dpc '' '' '' @dpc '' outline '' rollback; alter session set current_schema=SYS ; --//看到这样的代码非常无语,根本无需要连接,1次抽取完成需要的数值,然后处理就ok了. select *   FROM dba_hist_sysstat b  WHERE b.snap_id in (29031,29032)    AND b.dbid            = 1585360079    AND b.instance_number = 1    AND b.stat_name IN ('sorts (rows)'); --//先抽取然后很容易处理得到需要的结果,我们使用的监控大量的都是像上面的写法. --//我已经使用sql profile稳定了执行计划,执行dxvtb8qh04bkf.sql9dd_0脚本时.执行计划出现如下: Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 28 (U - Unused (6)) ---------------------------------------------------------------------------    0 -  STATEMENT            -  ALL_ROWS            -  DB_VERSION('19.1.0')            -  IGNORE_OPTIM_EMBEDDED_HINTS            -  OPTIMIZER_FEATURES_ENABLE('19.1.0')    0 -  SEL$3          U -  leading(sn s nm) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS            -  MERGE(@"SEL$3" >"SEL$2")    0 -  SEL$335DD26A            -  MERGE(@"SEL$335DD26A" >"SEL$1")    0 -  SEL$5          U -  leading(sn s nm) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS            -  MERGE(@"SEL$5" >"SEL$4")    0 -  SEL$7286615E            -  MERGE(@"SEL$7286615E" >"SEL$1")    1 -  SEL$573A9BEE            -  LEADING(@"SEL$573A9BEE" "SN"@"SEL$5" "SN"@"SEL$3" "NM"@"SEL$5" "S"@"SEL$5" "S"@"SEL$3" "NM"@"SEL$3")    6 -  SEL$573A9BEE / SN@SEL$5          U -  use_hash(sn s) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS            -  INDEX_RS_ASC(@"SEL$573A9BEE" "SN"@"SEL$5" ("WRM$_SNAPSHOT"."DBID" "WRM$_SNAPSHOT"."SNAP_ID" "WRM$_SNAPSHOT"."INSTANCE_NUMBER"))    8 -  SEL$573A9BEE / SN@SEL$3          U -  use_hash(sn s) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS            -  INDEX_RS_ASC(@"SEL$573A9BEE" "SN"@"SEL$3" ("WRM$_SNAPSHOT"."DBID" "WRM$_SNAPSHOT"."SNAP_ID" "WRM$_SNAPSHOT"."INSTANCE_NUMBER"))            -  USE_NL(@"SEL$573A9BEE" "SN"@"SEL$3")   10 -  SEL$573A9BEE / NM@SEL$5            -  FULL(@"SEL$573A9BEE" "NM"@"SEL$5")            -  USE_NL(@"SEL$573A9BEE" "NM"@"SEL$5")   12 -  SEL$573A9BEE / S@SEL$5          U -  use_hash(sn s) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS            -  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$573A9BEE" "S"@"SEL$5")            -  INDEX_RS_ASC(@"SEL$573A9BEE" "S"@"SEL$5" ("WRH$_SYSSTAT"."DBID" "WRH$_SYSSTAT"."SNAP_ID" "WRH$_SYSSTAT"."INSTANCE_NUMBER" "WRH$_SYSSTAT"."STAT_ID" "WRH$_SYSSTAT"."CON_DBID"))            -  USE_HASH(@"SEL$573A9BEE" "S"@"SEL$5")   15 -  SEL$573A9BEE / S@SEL$3          U -  use_hash(sn s) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS            -  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$573A9BEE" "S"@"SEL$3")            -  INDEX_RS_ASC(@"SEL$573A9BEE" "S"@"SEL$3" ("WRH$_SYSSTAT"."DBID" "WRH$_SYSSTAT"."SNAP_ID" "WRH$_SYSSTAT"."INSTANCE_NUMBER" "WRH$_SYSSTAT"."STAT_ID" "WRH$_SYSSTAT"."CON_DBID"))            -  USE_HASH(@"SEL$573A9BEE" "S"@"SEL$3")   17 -  SEL$573A9BEE / NM@SEL$3            -  INDEX(@"SEL$573A9BEE" "NM"@"SEL$3" ("WRH$_STAT_NAME"."DBID" "WRH$_STAT_NAME"."STAT_ID" "WRH$_STAT_NAME"."CON_DBID"))            -  USE_NL(@"SEL$573A9BEE" "NM"@"SEL$3") Note -----    - SQL profile switch tuning dxvtb8qh04bkf used for this statement --//我不明白为什么出现提示冲突.仔细简单发现原来底层的视图加入了提示. SYS@192.168.100.235:1521/orcl> @ 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@192.168.100.235:1521/orcl> @ 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; --//注意看下划线,定义视图AWR_CDB_SYSSTAT里面出现了提示,怪不得11g下许多这类语句执行效率还是可以,到了19c引入PDB后,视图定义 --//发生了变化,导致像这样写法的执行计划发生了畸变,许多需要2-3秒才能完成. SYS@book> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@book> @ ddl DBA_HIST_SYSSTAT C300 ----------------------------------------------------------------------------------------------------------------------------------   CREATE OR REPLACE FORCE VIEW "SYS"."DBA_HIST_SYSSTAT" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "STAT_ID", "STAT_NAME", "VALUE") AS   select s.snap_id, s.dbid, s.instance_number,        s.stat_id, nm.stat_name, value from WRM$_SNAPSHOT sn, WRH$_SYSSTAT s, DBA_HIST_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; --//11g就没有这类提示.