-- 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 ;