超過閥值的sql語句會在awr中儲存一段時間（預設是7天），可以透過dba_hist_sqlstat檢視查詢。

1.查詢檢視dba_hist_sqlstat
select snap_id, disk_reads_delta reads_delta,
executions_delta exec_delta, disk_reads_delta /decode
(executions_delta, 0, 1,executions_delta) rds_exec_ratio,
sql_id
from dba_hist_sqlstat
where disk_reads_delta > 100000
order by disk_reads_delta desc;

snap_id    reads_delta    exec_delta    rds_exec_ratio    sql_id
39           511106       1    511106       8h1qaqha580hh
29           216898             3 72299.33 d5bcqvumxr4y4

2.根據sql_id，在dba_hist_sqltext中檢視相關sql語句
select command_type,sql_text
from dba_hist_sqltext
where sql_id='d5bcqvumxr4y4';

command_type          sql_text
3                    select count(id) from bom

注：command_type=3表示這是select命令，完整的command_type可以select * from audit_actions;

3.檢視之前命令的執行計劃
select * from table(dbms_xplan.display_awr('8h1qaqha580hh'));

========================================================================
補充幾個有用的oracle dba_hist_*查詢語句：
1.耗cpu最多的10條語句

select *
from (select s.sql_id,
               sum(s.cpu_time_delta),
               sum(s.disk_reads_delta),
               count(*)
          from dba_hist_sqlstat s
         group by s.sql_id
         order by sum(s.cpu_time_delta) desc)
where rownum < 11;

2.最近7天，指定時間段（8:00-16:00）最消耗cpu的10條語句

select *
from (select s.sql_id,
               sum(s.cpu_time_delta),
               sum(s.disk_reads_delta),
               count(*)
          from dba_hist_sqlstat s, dba_hist_snapshot p
         where 1 = 1
           and s.snap_id = p.snap_id
           and extract(hour from p.end_interval_time) between 8 and 16
           and p.end_interval_time between sysdate - 7 and sysdate
         group by s.sql_id
         order by sum(s.cpu_time_delta) desc)
where rownum < 11;

3.可以進一步關聯dba_hist_sqltext檢視得到詳細的sql語句

select * from
   (select
    s.sql_id, s.sql_text
    sum(s.cpu_time_delta),
    sum(s.disk_reads_delta),
    count(*)
   from dba_hist_sqlstat s, dba_hist_snapshot p, dba_hist_sqltext t
   where 1=1
    and s.snap_id = p.snap_id
    and s.sql_id = t.sql_id
    and extract(hour from p.end_interval_time) between 8 and 16
    and t.command_type != 47 ╟- exclude pl/sql blocks from output
    and p.end_interval_time between sysdate-7 and sysdate
   group by s.sql_id
   order by sum(s.cpu_time_delta) desc
   )
where rownum < 11

4.分析指定sql語句各版本執行計劃的資源消耗情況

select st.sql_id,
       st.plan_hash_value,
       sum(st.executions_delta) executions,
       sum(st.rows_processed_delta) crows,
       trunc(sum(st.cpu_time_delta) / 1000000 / 60) cpu_mins,
       trunc(sum(st.elapsed_time_delta) / 1000000 / 60) ela_mins
from dba_hist_sqlstat st
where st.sql_id in (‘ &1′)
group by st.sql_id, st.plan_hash_value
order by st.sql_id, cpu_mins;

5.分析對比sql語句在不同執行計劃中的執行情況

select st2.sql_id,
       st2.plan_hash_value,
       st_long.plan_hash_value l_plan_hash_value,
       st2.cpu_mins,
       st_long.cpu_mins         l_cpu_mins,
       st2.ela_mins,
       st_long.ela_mins         l_ela_mins,
       st2.executions,
       st_long.executions       l_executions,
       st2.crows,
       st_long.crows            l_crows,
       st2.cpu_mins_per_row,
       st_long.cpu_mins_per_row l_cpu_mins_per_row
from (select st.sql_id,
               st.plan_hash_value,
               sum(st.executions_delta) executions,
               sum(st.rows_processed_delta) crows,
               trunc(sum(st.cpu_time_delta) / 1000000 / 60) cpu_mins,
               decode(sum(st.rows_processed_delta),
                      0,
                      0,
                      (sum(st.cpu_time_delta) / 1000000 / 60) /
                      sum(st.rows_processed_delta)) cpu_mins_per_row,
               trunc(sum(st.elapsed_time_delta) / 1000000 / 60) ela_mins
          from dba_hist_sqlstat st
         where 1 = 1
           and (st.cpu_time_delta != 0 or st.rows_processed_delta != 0)
         group by st.sql_id, st.plan_hash_value) st2,
       (select st.sql_id,
               st.plan_hash_value,
               sum(st.executions_delta) executions,
               sum(st.rows_processed_delta) crows,
               trunc(sum(st.cpu_time_delta) / 1000000 / 60) cpu_mins,
               decode(sum(st.rows_processed_delta),
                      0,
                      0,
                      (sum(st.cpu_time_delta) / 1000000 / 60) /
                      sum(st.rows_processed_delta)) cpu_mins_per_row,
               trunc(sum(st.elapsed_time_delta) / 1000000 / 60) ela_mins
          from dba_hist_sqlstat st
         where 1 = 1
           and (st.cpu_time_delta != 0 or st.rows_processed_delta != 0)
         having trunc(sum(st.cpu_time_delta) / 1000000 / 60) > 10
         group by st.sql_id, st.plan_hash_value) st_long
where 1 = 1
   and st2.sql_id = st_long.sql_id
   and st_long.cpu_mins_per_row /
       decode(st2.cpu_mins_per_row, 0, 1, st2.cpu_mins_per_row) > 2
order by l_cpu_mins          desc,
          st2.sql_id,
          st_long.cpu_mins    desc,
          st2.plan_hash_value;

從DBA_HIST_SQLSTAT檢視中查詢過去時段最佔用資源的會話

相關文章