[Oracle] SQL 수행통계 확인 (Top-N SQL)

Oracle DBMS SQL 성능 튜닝 대상 추출시 활용.

 

-- ###########################################################
-- SQL 수행 통계 확인
-- 
-- 특정 SQL의 과거 수행이력 확인
-- ###########################################################
--set lines 155  
--set pages 200
--col date_time for a20  
--col sql_id for a20
--col executions for 999,999,999
--col avg_buffer_gets for 999,999,999
--col avg_disk_reads for 999,999,999
--col avg_rows_processed for 999,999,999
--col avg_elapsed_time for 999,999.999  
--break on plan_hash_value on startup_time skip 1 
SELECT TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') DATE_TIME
     , SQL_ID
     , PLAN_HASH_VALUE
     , NVL(EXECUTIONS_DELTA,0) EXECUTIONS
     , ROUND((BUFFER_GETS_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))) AVG_BUFFER_GETS  
     , ROUND((DISK_READS_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))) AVG_DISK_READS
     , ROUND((ROWS_PROCESSED_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))) AVG_ROWS_PROCESSED
     , ROUND((ELAPSED_TIME_DELTA/DECODE(NVL(EXECUTIONS_DELTA,0),0,1,EXECUTIONS_DELTA))/1000000,2) AVG_ELAPSED_TIME
  FROM DBA_HIST_SNAPSHOT T1, DBA_HIST_SQLSTAT T2
 WHERE T1.DBID = T2.DBID
   AND T1.SNAP_ID = T2.SNAP_ID
   AND T1.INSTANCE_NUMBER = T2.INSTANCE_NUMBER
   AND T2.SQL_ID = 'gy5c8jtf43g6b'
   AND T2.EXECUTIONS_DELTA > 0
 ORDER BY DATE_TIME DESC
;
 
 
-- #############################################################
-- SQL 수행 통계 확인 (TOP-N 쿼리)
--
-- 용도 : 전체 SQL의 실행 영향율 확인 (SQL_ID별 실행영향율 확인)
-- 대상 : DBA_HIST_SQLSTAT 과거 수행이력
-- 
-- 영향율 지표 -> CPU, ELAPSED_TIME, EXEC, LIO, PIO, ROWS
-- RATIO_TO_REPORT 활용
-- #############################################################
SELECT B.INSTANCE_NUMBER INST_ID
     , SQL_ID 
     , PARSING_SCHEMA_NAME SCHEMA_NAME 
     , PLAN_HASH_VALUE
     , 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
--     , SUM(B.EXECUTIONS_DELTA)                                             AS EXEC_TOT
     , ROUND(RATIO_TO_REPORT(SUM(B.BUFFER_GETS_DELTA)) OVER() * 100, 1)     AS LIO_RATIO
--     , SUM(B.BUFFER_GETS_DELTA)                                            AS LIO_TOT
     , ROUND(RATIO_TO_REPORT(SUM(B.DISK_READS_DELTA)) OVER() * 100, 1)      AS PIO_RATIO
--     , SUM(B.DISK_READS_DELTA)                                             AS PIO_TOT
     , ROUND(RATIO_TO_REPORT(SUM(B.ROWS_PROCESSED_DELTA)) OVER() * 100, 1)  AS ROWS_RATIO
--     , SUM(B.ROWS_PROCESSED_DELTA)                                         AS ROWS_TOT
     , (SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID = B.SQL_ID)     AS SQL_TEXT
  FROM DBA_HIST_SNAPSHOT A, DBA_HIST_SQLSTAT B
 WHERE A.DBID=B.DBID
   AND A.SNAP_ID = B.SNAP_ID
   AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
   AND A.INSTANCE_NUMBER = 1
--   AND A.END_INTERVAL_TIME  >= SYSDATE -1
   AND A.END_INTERVAL_TIME >= TO_DATE('2022-08-18 00:00', 'YYYY-MM-DD HH24:MI')
   AND A.END_INTERVAL_TIME <= TO_DATE('2022-08-18 23:59', 'YYYY-MM-DD HH24:MI')
   AND B.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
   AND B.EXECUTIONS_DELTA > 0
 GROUP BY B.INSTANCE_NUMBER, B.SQL_ID, B.PARSING_SCHEMA_NAME, PLAN_HASH_VALUE, B.MODULE
 ORDER BY CPU_RATIO DESC
;
 
 
-- #############################################################
-- SQL 수행 통계 확인 (TOP-N 쿼리)
--
-- 용도 : 전체 SQL의 실행 영향율 확인 (SQL_ID별 실행영향율 확인)
-- 대상 : DBA_HIST_SQLSTAT 과거 수행이력
--
-- 영향율 지표 -> CPU, ELAPSED_TIME, EXEC, LIO, PIO, ROWS
--
-- [1회 수행당 평균 확인]
-- 1회 수행당 평균 LIO 발생 횟수 (BUFF_PER_EXEC)
-- 1회 수행당 평균 PIO 발생 횟수 (DISK_PER_EXEC)
-- 1회 수행당 평균 추출 행 수    (ROWS_PER_EXEC)
-- 1회 수행당 평균 수행시간      (ELAPS_PER_EXEC)
-- 1회 수행당 평균 CPU 시간      (CPU_PER_EXEC)
--
-- #############################################################
SELECT INST_ID
     , SQL_ID
     , USERNAME
     , HASH_VALUE
     , (SELECT MIN(MODULE) FROM V$SQLAREA WHERE SQL_ID = T1.SQL_ID) AS MODULE
     , ROUND(ELAPS/ELAPS_TOTAL * 100, 3)                            AS ELA_RATIO
     , ROUND(CPU_TIME/CPU_TOTAL * 100, 3)                           AS CPU_RATIO
     , ROUND(EXEC/EXEC_TOTAL * 100, 3)                              AS EXEC_RATIO
     , ROUND(BUFFER/BUFFER_TOTAL * 100, 3)                          AS LIO_RATIO
     , ROUND(DISK/DISK_TOTAL * 100, 3)                              AS PIO_RATIO
     , ROUND(ROWS_PROC/ROWS_TOTAL * 100, 3)                         AS ROWS_RATIO
     , EXEC                                                         AS "1일 수행횟수" -- SYSDATE -1
     , ROUND(BUFFER/EXEC)                                           AS BUFF_PER_EXEC
     , ROUND(DISK/EXEC)                                             AS DISK_PER_EXEC
     , ROUND(ROWS_PROC/EXEC)                                        AS ROWS_PER_EXEC
     , ROUND(CPU_TIME/EXEC/1000000, 3)                              AS CPU_PER_EXEC
     , ROUND(ELAPS/EXEC/1000000, 3)                                 AS ELAPS_PER_EXEC
     , (SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE DBID = T1.DBID AND SQL_ID = T1.SQL_ID) AS SQL_TEXT
  FROM (SELECT T1.INSTANCE_NUMBER INST_ID
             , T1.DBID
             , SQL_ID
             , PLAN_HASH_VALUE HASH_VALUE
             , PARSING_SCHEMA_NAME USERNAME
             , DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA)) EXEC
             , SUM(BUFFER_GETS_DELTA) BUFFER
             , SUM(DISK_READS_DELTA) DISK
             , SUM(ROWS_PROCESSED_DELTA) ROWS_PROC
             , SUM(CPU_TIME_DELTA) CPU_TIME
             , SUM(ELAPSED_TIME_DELTA) ELAPS
             , SUM(SUM(BUFFER_GETS_DELTA)) OVER (PARTITION BY T1.INSTANCE_NUMBER)       AS BUFFER_TOTAL
             , SUM(SUM(DISK_READS_DELTA)) OVER (PARTITION BY T1.INSTANCE_NUMBER)        AS DISK_TOTAL
             , SUM(SUM(CPU_TIME_DELTA)) OVER (PARTITION BY T1.INSTANCE_NUMBER)          AS CPU_TOTAL
             , SUM(SUM(ELAPSED_TIME_DELTA)) OVER (PARTITION BY T1.INSTANCE_NUMBER)      AS ELAPS_TOTAL
             , SUM(SUM(ROWS_PROCESSED_DELTA)) OVER (PARTITION BY T1.INSTANCE_NUMBER)    AS ROWS_TOTAL
             , SUM(SUM(EXECUTIONS_DELTA)) OVER (PARTITION BY T1.INSTANCE_NUMBER)        AS EXEC_TOTAL
          FROM DBA_HIST_SQLSTAT T1, DBA_HIST_SNAPSHOT T2
         WHERE T1.SNAP_ID = T2.SNAP_ID
           AND T1.DBID = T2.DBID
           AND T1.INSTANCE_NUMBER = T2.INSTANCE_NUMBER
--           AND T2.END_INTERVAL_TIME >= SYSDATE -1
           AND T2.END_INTERVAL_TIME >= TO_DATE('2022-08-18 00:00', 'YYYY-MM-DD HH24:MI')
           AND T2.END_INTERVAL_TIME <= TO_DATE('2022-08-18 23:59', 'YYYY-MM-DD HH24:MI')
           AND T1.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
           AND T1.EXECUTIONS_DELTA > 0
         GROUP BY T1.INSTANCE_NUMBER, T1.DBID, SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME
  ) T1
 WHERE INST_ID = 1
 ORDER BY CPU_RATIO DESC
;
 
 
-- #############################################################
-- SQL 수행 통계 확인 (TOP-N 쿼리)
--
-- 용도 : 전체 SQL의 실행 영향율 확인 (SQL_ID별 실행영향율 확인)
-- 대상 : V$SQL 현재 Memory (SGA)에 존재하는 SQL
--
-- 영향율 지표     -> CPU, ELAPSED_TIME, EXEC, LIO, PIO, ROWS
-- 
-- [1회 수행당 평균 확인]
-- 1회 수행당 평균 LIO 발생 횟수 (BUFF_PER_EXEC)
-- 1회 수행당 평균 PIO 발생 횟수 (DISK_PER_EXEC)
-- 1회 수행당 평균 추출 행 수    (ROWS_PER_EXEC)
-- 1회 수행당 평균 수행시간      (ELAPS_PER_EXEC)
-- 1회 수행당 평균 CPU 시간      (CPU_PER_EXEC)
--
-- #############################################################
SELECT SQL_ID
     , PLAN_HASH_VALUE
     , PARSING_SCHEMA_NAME  AS USER_NAME
     , CHILD_NUMBER
     , EXECUTIONS
     , EXEC_DAY_PER         AS "1일 수행횟수"
     , BUFFER_GETS
     , BUFF_PER_EXEC
     , CPU_TIME
     , CPU_PER_EXEC
     , ELAPSED
     , ELAPS_PER_EXEC
     , ROWS_PROCESSED
     , ROWS_PER_EXEC
     , DISK_READS
     , DISK_PER_EXEC
     , FIRST_LOAD_TIME
     , LAST_LOAD_TIME
     , LAST_ACTIVE_TIME
     , ROUND(ELAPS_PER_EXEC * EXEC_DAY_PER/SUM(ELAPS_PER_EXEC * EXEC_DAY_PER) OVER() * 100, 2) AS ELA_RATIO
     , ROUND(CPU_PER_EXEC   * EXEC_DAY_PER/SUM(CPU_PER_EXEC   * EXEC_DAY_PER) OVER() * 100, 2) AS CPU_RATIO
     , ROUND(ROWS_PER_EXEC  * EXEC_DAY_PER/SUM(ROWS_PER_EXEC  * EXEC_DAY_PER) OVER() * 100, 2) AS ROWS_RATIO
     , ROUND(BUFF_PER_EXEC  * EXEC_DAY_PER/SUM(BUFF_PER_EXEC  * EXEC_DAY_PER) OVER() * 100, 2) AS BUFFER_RATIO
     , ROUND(DISK_PER_EXEC  * EXEC_DAY_PER/SUM(DISK_PER_EXEC  * EXEC_DAY_PER) OVER() * 100, 2) AS DISK_RATIO
     , SQL_FULLTEXT
  FROM (SELECT PLAN_HASH_VALUE
             , SQL_ID
             , PARSING_SCHEMA_NAME
             , CHILD_NUMBER
             , EXECUTIONS
             , BUFFER_GETS
             , TRUNC(BUFFER_GETS/EXECUTIONS)             AS BUFF_PER_EXEC
             , DISK_READS
             , ROUND(DISK_READS/EXECUTIONS)              AS DISK_PER_EXEC
             , ROUND(CPU_TIME/1000000, 2) CPU_TIME
             , ROUND(CPU_TIME/EXECUTIONS/1000000, 2)     AS CPU_PER_EXEC
             , ROUND(ELAPSED_TIME/1000000, 2) ELAPSED
             , ROUND(ELAPSED_TIME/EXECUTIONS/1000000, 2) AS ELAPS_PER_EXEC
             , ROWS_PROCESSED
             , ROUND(ROWS_PROCESSED/EXECUTIONS)          AS ROWS_PER_EXEC
             , FIRST_LOAD_TIME
             , LAST_LOAD_TIME
             , LAST_ACTIVE_TIME
             , ROUND(EXECUTIONS/DECODE(ROUND(SYSDATE-TO_DATE(FIRST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS')), 0, 1, ROUND(SYSDATE-TO_DATE(FIRST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS')))) EXEC_DAY_PER
             , SQL_FULLTEXT
          FROM V$SQL 
         WHERE 1=1
--           AND SQL_ID = 'fg6yfj97ugurg'
           AND PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
           AND EXECUTIONS > 0
  )
 ORDER BY ELA_RATIO DESC;