-- 1.1 IO/CPU TOP SQL 관련 성능 개선 대상 선정 ( V$SQLAREA 기준 )
SELECT ROWNUM CNT,
T1.*
FROM (
SELECT T1.PARSING_SCHEMA_NAME, T1.MODULE, T1.SQL_ID, T1.HASH_VALUE, T1.SUBSTR_SQLTEXT,
T1.EXECUTIONS, T1.BUFFER_GETS, T1.DISK_READS, T1.ROWS_PROCESSED,
T1.LIO, T1.ELAPSED_SEC, T1.CPU_SEC,
ROUND(T1.CPU_TIME/T1.CPU_TIME_TOTAL*100,1) RATIO_CPU,
ROUND(T1.ELAPSED_TIME/T1.ELPASED_TIME_TOTAL*100,1) RATIO_ELAPSED
FROM (
SELECT PARSING_SCHEMA_NAME, MODULE, SQL_ID, HASH_VALUE, SUBSTR(SQL_TEXT,1,100) SUBSTR_SQLTEXT,
EXECUTIONS, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME,
ROUND(BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000000,1) LIO,
ROUND(ELAPSED_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000000,1) ELAPSED_SEC,
ROUND(CPU_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000000,1) CPU_SEC,
SUM(CPU_TIME) OVER () CPU_TIME_TOTAL,
SUM(ELAPSED_TIME) OVER () ELPASED_TIME_TOTAL
FROM V$SQLAREA S
) T1
WHERE T1.EXECUTIONS > 0
AND T1.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','SYSMAN')
-- 수행 시간 기준 정렬
-- ORDER BY ELAPSED_SEC DESC
-- CPU 사용률 정렬
ORDER BY RATIO_CPU DESC
) T1
WHERE ROWNUM <= 10 ;
-- 1.2 IO/CPU TOP SQL 관련 성능 개선 대상 선정 ( DBA_HIST_SQLSTAT 기준 )
SELECT SQL_ID, SCHEMA_NAME, MODULE, ELA_RATIO, ELA_TOT, CPU_RATIO, CPU_TOT, EXEC_RATIO, EXEC_TOT
, LIO_RATIO, LIO_TOT, PIO_RATIO, PIO_TOT, ROWSO_RATIO, ROWS_TOT
FROM (
SELECT SQL_ID, PARSING_SCHEMA_NAME SCHEMA_NAME,
NVL(SUBSTR(B.MODULE,1,15),'-') MODULE,
ROUND(RATIO_TO_REPORT(SUM(B.ELAPSED_TIME_DELTA)) OVER () * 100,1) AS ELA_RATIO,
ROUND(SUM(B.ELAPSED_TIME_DELTA)/1000000,0) AS ELA_TOT,
ROUND(RATIO_TO_REPORT(SUM(B.CPU_TIME_DELTA)) OVER () * 100,1) AS CPU_RATIO,
ROUND(SUM(B.CPU_TIME_DELTA)/1000000,0) AS CPU_TOT,
ROUND(RATIO_TO_REPORT(SUM(B.EXECUTIONS_DELTA)) OVER () * 100,1) AS EXEC_RATIO,
ROUND(SUM(B.EXECUTIONS_DELTA)/1000000,0) AS EXEC_TOT,
ROUND(RATIO_TO_REPORT(SUM(B.BUFFER_GETS_DELTA)) OVER () * 100,1) AS LIO_RATIO,
ROUND(SUM(B.BUFFER_GETS_DELTA)/1000000,0) AS LIO_TOT,
ROUND(RATIO_TO_REPORT(SUM(B.DISK_READS_DELTA)) OVER () * 100,1) AS PIO_RATIO,
ROUND(SUM(B.DISK_READS_DELTA)/1000000,0) AS PIO_TOT,
ROUND(RATIO_TO_REPORT(SUM(B.ROWS_PROCESSED_DELTA)) OVER () * 100,1) AS ROWSO_RATIO,
ROUND(SUM(B.ROWS_PROCESSED_DELTA)/1000000,0) AS ROWS_TOT
FROM DBA_HIST_SNAPSHOT A,
DBA_HIST_SQLSTAT B
WHERE A.INSTANCE_NUMBER = 1
--AND A.BEGIN_INTERVAL_TIME >= TO_DATE (:B1,'YYYY-MM-DD')
--AND A.END_INTERVAL_TIME <= TO_DATE(:B2,'YYYY-MM-DD')+0.99999
AND A.BEGIN_INTERVAL_TIME >= SYSDATE -2/24
AND A.END_INTERVAL_TIME <= SYSDATE
AND A.DBID = B.DBID
AND B.PARSING_SCHEMA_NAME NOT IN ( 'SYS','SYSTEM','SYSMAN')
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND A.SNAP_ID = B.SNAP_ID
GROUP BY B.SQL_ID, B.PARSING_SCHEMA_NAME, B.MODULE
ORDER BY CPU_RATIO DESC )
WHERE ROWNUM <= 100 ;