과거 기간의 경과 시간/CPU 기준 상위 SQL 찾기(AWR + V$SQLAREA)

명령어

-- AWR: top 20 by elapsed time within a snapshot range (per plan)
SELECT *
FROM  (SELECT st.sql_id,
              st.plan_hash_value,
              SUM(st.executions_delta)                       AS execs,
              ROUND(SUM(st.elapsed_time_delta)/1e6, 1)       AS elapsed_s,
              ROUND(SUM(st.cpu_time_delta)/1e6, 1)           AS cpu_s,
              ROUND(SUM(st.iowait_delta)/1e6, 1)             AS io_wait_s,
              SUM(st.buffer_gets_delta)                      AS buffer_gets,
              ROUND(SUM(st.elapsed_time_delta)/1e3
                    / NULLIF(SUM(st.executions_delta),0), 2) AS ms_per_exec,
              MAX(st.parsing_schema_name)                    AS parsing_schema
       FROM   dba_hist_sqlstat st
       WHERE  st.dbid = (SELECT dbid FROM v$database)
         AND  st.snap_id BETWEEN <begin_snap> AND <end_snap>
       GROUP  BY st.sql_id, st.plan_hash_value
       ORDER  BY elapsed_s DESC)
WHERE ROWNUM <= 20;
-- For CPU ranking: ORDER BY cpu_s DESC
-- SQL text: SELECT DBMS_LOB.SUBSTR(sql_text, 4000, 1) FROM dba_hist_sqltext WHERE sql_id = '<sql_id>';
-- Plan history of a single SQL: @?/rdbms/admin/awrsqrpt.sql

-- Current (since instance startup, still in cache) by CPU:
SELECT sql_id,
       ROUND(cpu_time/1e6,1)     AS cpu_s,
       ROUND(elapsed_time/1e6,1) AS elapsed_s,
       executions, buffer_gets, disk_reads,
       ROUND(cpu_time/1e3/NULLIF(executions,0),2) AS cpu_ms_per_exec,
       parsing_schema_name,
       SUBSTR(sql_text,1,80) AS sql_text
FROM   v$sqlarea
WHERE  parsing_schema_name NOT IN ('SYS','SYSTEM')
ORDER  BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;

하는 일

DBA_HIST_SQLSTAT의 *_DELTA 컬럼을 합산하여 스냅샷 범위에서 가장 비용이 큰 SQL을 plan_hash_value별로 나열하며, 이를 통해 같은 SQL의 계획 변경도 드러난다. V$SQLAREA 쿼리는 아직 캐시에 있는 항목의 누적 값을 제공한다.

사용 시점

"어젯밤 02:00~04:00에 무엇이 시스템에 부하를 줬나?", 계획 변경 탐지(같은 sql_id, 다른 plan_hash_value), 튜닝 후보 목록 작성용.

주의

DBA_HIST_* 조회에는 Diagnostics Pack 라이선스가 필요하지만 V$SQLAREA는 필요 없다. AWR은 상위 SQL만(기본 top-N) 보관하므로 작지만 매우 빈번한 문장은 누락될 수 있다. V$SQLAREA 값은 커서가 age out되면 사라진다. delta 값은 재시작을 넘어서도 정확하지만 캐시 기반 쿼리는 0부터 다시 시작한다.