从dba_hist_sqlstat视图中查找过去时段最占用资源的会话

从dba_hist_sqlstat视图中查找过去时段最占用资源的会话

超过阀值的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;