Geçmiş dönemde en çok elapsed/CPU harcayan SQL'leri bul (AWR + V$SQLAREA)

Komut

-- AWR: snapshot araliginda elapsed'a gore top 20 (plan bazinda)
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;
-- CPU siralamasi icin: ORDER BY cpu_s DESC
-- SQL metni: SELECT DBMS_LOB.SUBSTR(sql_text, 4000, 1) FROM dba_hist_sqltext WHERE sql_id = '<sql_id>';
-- Tek SQL'in plan gecmisi: @?/rdbms/admin/awrsqrpt.sql

-- Anlik (instance acildigindan beri, cache'te kalanlar) CPU'ya gore:
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;

Ne yapar?

DBA_HIST_SQLSTAT'taki *_DELTA kolonlarını toplayarak bir snapshot aralığında en pahalı SQL'leri plan_hash_value kırılımıyla listeler; aynı SQL'in plan değiştirip değiştirmediği de görünür. V$SQLAREA sorgusu cache'teki kümülatif değerleri verir.

Ne zaman kullanılır?

"Dün gece 02:00-04:00 arası ne yordu sistemi?" sorusunda, plan değişikliği (aynı sql_id, farklı plan_hash_value) tespitinde, tuning adayı listesi çıkarırken.

Dikkat

DBA_HIST_* sorgulamak Diagnostics Pack lisansı gerektirir; V$SQLAREA gerektirmez. AWR yalnızca top SQL'leri saklar (varsayılan top-N), küçük ama çok sık çalışan SQL'ler eksik olabilir. V$SQLAREA değerleri cursor cache'ten düştüğünde kaybolur. Snapshot aralığına restart girmişse delta'lar yine doğrudur ama cache bazlı sorgu sıfırlanmıştır.