[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就没有这类提示.