oracle dba_hist_sql,从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;