------------------------------------------------------------------------------------------------------------
-- 상황 : DB UPGRADE 전, AWR 기준으로 TOP 30 쿼리를 SPB(SQL PLAN BASELINE) 에 등록 ENABLED=NO 후, 이슈 발생 시, ENABLED=YES 켜지
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- 1. AWR 기준으로 TOP 30 쿼리를 SPB(SQL PLAN BASELINE) 에 등록 ENABLED=NO 등록
------------------------------------------------------------------------------------------------------------
SELECT RN, SQL_INFO, MIN_SNAP_ID, MAX_SNAP_ID, SQL_ID, PLAN_HASH_VALUE, MODULE, PARSING_SCHEMA_NAME, "TOTAL_ELAPSED(S)",ELPASED_TIME_RAITO, "ELAPSED/EXE(MS)"
,'VAR CNT NUMBER ' AS FIRST_ROWS_LOAD_FROM_AWR
-- , 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>'||MIN_SNAP_ID||',END_SNAP=>'||MAX_SNAP_ID||', BASIC_FILTER=>q''# SQL_ID='''||sql_id||''' AND PLAN_HASH_VALUE ='||PLAN_HASH_VALUE||' #'');' AS REGISTER_SPM_FROM_AWR
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>'||MIN_SNAP_ID||',END_SNAP=>'||MAX_SNAP_ID||', BASIC_FILTER=>q''# SQL_ID='''||sql_id||''' AND PLAN_HASH_VALUE ='||PLAN_HASH_VALUE||' #'', ENABLED=>''NO'');' AS LOAD_FROM_AWR
FROM (
SELECT ROWNUM AS RN, T.*
FROM (
SELECT SQL_ID, PLAN_HASH_VALUE, FORCE_MATCHING_SIGNATURE, MODULE, PARSING_SCHEMA_NAME
,COUNT( DISTINCT PLAN_HASH_VALUE ) OVER ( PARTITION BY SQL_ID) AS "PLAN_COUNT"
,MIN(SNAP_ID) AS MIN_SNAP_ID
,MAX(SNAP_ID) AS MAX_SNAP_ID
,SUM(EXECUTIONS_DELTA) AS "TOTAL_EXE",
ROUND(SUM(ELAPSED_TIME_DELTA/1000000)) AS "TOTAL_ELAPSED(S)",
ROUND(RATIO_TO_REPORT(SUM(ELAPSED_TIME_DELTA)) OVER () *100,1) AS ELPASED_TIME_RAITO, -- gfw4410md31u1
ROUND(SUM(CPU_TIME_DELTA/1000000)) AS "TOTAL_CPU(S)",
ROUND(RATIO_TO_REPORT(SUM(CPU_TIME_DELTA)) OVER () *100,1) AS CPU_TIME_RAITO,
ROUND(SUM(ELAPSED_TIME_DELTA/1000)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA))) AS "ELAPSED/EXE(MS)", -- a19cxsswgfzvx fpb0t0hg17d70
ROUND(RATIO_TO_REPORT(SUM(ELAPSED_TIME_DELTA)) OVER () *100,1) AS EXEC_RAITO,
SUM(ROWS_PROCESSED_DELTA) AS "TOTAL_ROWS",
ROUND(SUM(ROWS_PROCESSED_DELTA)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA))) AS "ROWS/EXE",
SUM(PARSE_CALLS_DELTA) AS "TOTAL_PARSE_COUNT",
ROUND(RATIO_TO_REPORT(SUM(PARSE_CALLS_DELTA)) OVER () *100,1) AS "PARSE_RATIO",
SUM(BUFFER_GETS_DELTA) AS "TOTAL_LOGICAL",
SUM(DISK_READS_DELTA) AS "TOTAL_DISK",
ROUND(SUM(DISK_READS_DELTA)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA))) AS "DISK/EXE",
ROUND(RATIO_TO_REPORT(SUM(DISK_READS_DELTA)) OVER () *100,1) AS DISK_RAITO,
ROUND(SUM(BUFFER_GETS_DELTA)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA))) AS "BUFFER/EXE",
ROUND(RATIO_TO_REPORT(SUM(BUFFER_GETS_DELTA)) OVER () *100,1) AS BUFFER_RAITO,
ROUND(SUM(APWAIT_DELTA/1000)) AS "TOTAL_APWAIT(MS)",
ROUND(SUM(APWAIT_DELTA)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA))) AS "APWAIT/EXE(us)",
ROUND(RATIO_TO_REPORT(SUM(APWAIT_DELTA)) OVER () *100,1) AS APP_TIME_RAITO,
ROUND(SUM(DIRECT_WRITES_DELTA)) AS "DIRECT_WRITES_DELTA",
ROUND(SUM(CLWAIT_DELTA/1000000)) AS "CLWAIT_DELTA_SUM(S)",
ROUND(SUM(CLWAIT_DELTA/1000)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA))) AS "CLWAIT/EXE(ms)",
ROUND(RATIO_TO_REPORT(SUM(CLWAIT_DELTA)) OVER () *100,1) AS CLWAIT_TIME_RAITO,
ROUND(SUM(CCWAIT_DELTA/1000000)) AS "CCWAIT_DELTA_SUM(S)" ,
ROUND(SUM(CCWAIT_DELTA/1000)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA))) AS "CON_WAIT/EXE(ms)",
ROUND(RATIO_TO_REPORT(SUM(CCWAIT_DELTA)) OVER () *100,1) AS CCWAIT_TIME_RAITO,
( SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT S3 WHERE S1.SQL_ID = S3.SQL_ID ) AS SQL_INFO
FROM DBA_HIST_SQLSTAT S1
, ( SELECT MIN(SNAP_ID) AS START_SNAP_ID , MAX(SNAP_ID) AS END_SNAP_ID
FROM DBA_HIST_SNAPSHOT
--WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2019/09/17 07:00:00','YYYY/MM/DD HH24:MI:SS') -- 43dahf6vxhazt SELECT * FROM QRTZ_MGT_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE
--AND TO_DATE('2019/09/17 08:00:00','YYYY/MM/DD HH24:MI:SS')
WHERE BEGIN_INTERVAL_TIME BETWEEN SYSDATE - 3 -- :START_DAYS
AND SYSDATE
) S2
WHERE S1.SNAP_ID BETWEEN S2.START_SNAP_ID AND S2.END_SNAP_ID
AND 1=1
-- AND S1.PARSING_SCHEMA_NAME NOT IN ( 'OPS$ORACLE','SYS','SYSTEM')
AND S1.PARSING_SCHEMA_NAME ='SCOTT'
AND S1.PLAN_HASH_VALUE != 0
--AND S1.MODULE IN ('SQL*Plus')
AND S1.SQL_ID IN ('6d06qd7tv32hp','7ck4atqcg485x')
--AND S1.PLAN_HASH_VALUE =1756381138
GROUP BY SQL_ID, PLAN_HASH_VALUE , FORCE_MATCHING_SIGNATURE, MODULE, PARSING_SCHEMA_NAME
ORDER BY ROUND(SUM(ELAPSED_TIME_DELTA/1000000)) DESC NULLS LAST
-- ORDER BY ROUND(SUM(APWAIT_DELTA/1000)) DESC NULLS LAST
) T
WHERE MIN_SNAP_ID != MAX_SNAP_ID
)
WHERE RN <= 100
VAR CNT NUMBER
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>31,END_SNAP=>34, BASIC_FILTER=>q'# SQL_ID='6d06qd7tv32hp' AND PLAN_HASH_VALUE =1012929312 #', ENABLED=>'NO');
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>30,END_SNAP=>34, BASIC_FILTER=>q'# SQL_ID='6d06qd7tv32hp' AND PLAN_HASH_VALUE =1642965905 #', ENABLED=>'NO');
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>46,END_SNAP=>47, BASIC_FILTER=>q'# SQL_ID='7ck4atqcg485x' AND PLAN_HASH_VALUE =3643076765 #', ENABLED=>'NO');
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>46,END_SNAP=>47, BASIC_FILTER=>q'# SQL_ID='7ck4atqcg485x' AND PLAN_HASH_VALUE =2071717713 #', ENABLED=>'NO');
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>30,END_SNAP=>31, BASIC_FILTER=>q'# SQL_ID='6d06qd7tv32hp' AND PLAN_HASH_VALUE =1756381138 #', ENABLED=>'NO');
------------------------------------------------------------------------------------------------------------
-- 2. 이슈 발생 쿼리에 대한 SPB(SQL PLAN BASELINE) 에 등록 ENABLED=YES 로 수정
------------------------------------------------------------------------------------------------------------
-- [참고]DBMS_SPM.ALTER_SQL_PLAN_BASELINE 으로는 ACCEPTED 를 설정할 수 없음 // enabled, fixed, autopurge 등 설정 가능
DECLARE
x number;
BEGIN
x := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SQL_2a1b71d655a6c233',
'SQL_PLAN_2n6vjutaudhjm0dc6bb43',
ATTRIBUTE_NAME => 'ENABLED',
ATTRIBUTE_VALUE => 'YES');
END;
/
------------------------------------------------------------------------------------------------------------
-- 3. 필요 시, 해당 SQL_ID PURGE
------------------------------------------------------------------------------------------------------------
-- RAC 면 노드 별로 수행해야함
--- 특정 SQL 전체(SQL_TEXT 및 SQL_PLAN) PURGE 하기
Select 'execute sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',64)' AS PURGE_BY_SQL_ID
From v$sqlarea
WHERE SQL_TEXT LIKE 'SELECT /*+ ORG_SQL */%' ;
--- 특정 SQL의 PLAN만 PURGE 하기
Select 'execute sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',64)'
From v$sqlarea
WHERE SQL_TEXT LIKE 'SELECT /*+ ORG_SQL */%' ;
------------------------------------------------------------------------------------------------------------
-- 4. SPB 사용 확인
------------------------------------------------------------------------------------------------------------
select sql_id, plan_hash_value, SQL_PLAN_BASELINE, substr(sql_text,1,30) as sql_info
from v$sql
where sql_text like 'SELECT /*+ ORG_SQL */%' ;
-- 끝 --