[ORACLE] AWR 이용한 SQL PLAN 변경 확인

sql_id와 awr를 이용한 sql plan 변경 확인 쿼리

 /* sql_stat from awr(sql_id) */
select /*+ parallel(16) */ b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME
  , to_char(b.END_INTERVAL_TIME,'yyyymmdd') day
  , to_char(b.END_INTERVAL_TIME,'hh24') hour
  , to_char(b.END_INTERVAL_TIME,'mi') min
  ,a.SNAP_ID
  ,a.INSTANCE_NUMBER
  ,a.SQL_ID
  ,a.PLAN_HASH_VALUE
  ,a.PARSING_SCHEMA_NAME
  ,a.EXECUTIONS_DELTA
  ,TRIM( REPLACE( REPLACE(
         CASE
           WHEN INSTR(DBMS_LOB.SUBSTR(SQL_TEXT, 200), '*/')=0 THEN DBMS_LOB.SUBSTR(SQL_TEXT, 200)
           ELSE substr(DBMS_LOB.SUBSTR(DBMS_LOB.SUBSTR(C.SQL_TEXT, 200), INSTR(DBMS_LOB.SUBSTR(C.SQL_TEXT, 200), '*/')+1), INSTR(DBMS_LOB.SUBSTR(C.SQL_TEXT, 200), '/*'), 200)
         END,CHR(10),NULL),CHR(13),NULL) ) as sql_text
  ,round(a.ELAPSED_TIME_DELTA/case when executions_delta=0 then 1 else executions_delta end    ,0)/1000000 ELAPSED_DELTA_PER_EXEC_sec
  ,round(a.BUFFER_GETS_DELTA/case when executions_delta=0 then 1 else executions_delta end     ,0) BUFFER_GETS_DELTA_PER_EXEC
  ,round(a.ROWS_PROCESSED_DELTA/case when executions_delta=0 then 1 else executions_delta end  ,0) ROWS_PROCESSED_DELTA_PER_EXEC
  ,round(a.CPU_TIME_DELTA/case when executions_delta=0 then 1 else executions_delta end        ,0) CPU_TIME_DELTA_PER_EXEC
  ,round(a.IOWAIT_DELTA/case when executions_delta=0 then 1 else executions_delta end          ,0) IOWAIT_DELTA_PER_EXEC
  ,round(a.CLWAIT_DELTA/case when executions_delta=0 then 1 else executions_delta end          ,0) CLWAIT_DELTA_PER_EXEC
--  ,round(a.APWAIT_DELTA/case when executions_delta=0 then 1 else executions_delta end          ,1) APWAIT_DELTA_PER_EXEC
--  ,round(a.CCWAIT_DELTA/case when executions_delta=0 then 1 else executions_delta end          ,1) CCWAIT_DELTA_PER_EXEC
--  , COMMAND_TYPE
from dba_hist_sqlstat a inner join dba_hist_snapshot b on (a.dbid=b.dbid and a.snap_id=b.snap_id and a.instance_number=b.instance_number)
        inner join dba_hist_sqltext c on(a.sql_id=c.sql_id)
where a.sql_id in(  /* sql_id list */
'fdn1p36mycawr'
)
--and EXECUTIONS_DELTA>0
AND 1=1
--   and TO_CHAR(END_INTERVAL_TIME,'YYYYMMDD') in('20210607','20210608','20210609') /*필요기간설정*/
   and END_INTERVAL_TIME >= trunc(sysdate)-30 /*필요기간설정*/
--   and to_char(END_INTERVAL_TIME, 'hh24mi') between '1040' and '1100' /*필요시간설정*/
   and to_char(END_INTERVAL_TIME, 'd') not in(1,7) /*필요시 주말 제외*/
--   and a.PARSING_SCHEMA_NAME in('ILOG') /*필요시 계정 구분*/
--   and a.snap_id between 266481-10 and 266481+10 /*필요시 snap_id 로 구분*/
--   and upper(c.sql_text) like '%RULEID%' /*필요시 sql_text로 찾기*/
order by a.snap_id desc, a.sql_id
;