Oracle如何查看歷史TOP SQL

133次閱讀

共計 2294 個字符，預計需要花費 6 分鐘才能閱讀完成。

這篇文章給大家分享的是有關 Oracle 如何查看歷史 TOP SQL 的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

Oracle 查看歷史 TOP SQL

歷史 TOP SQL 可以通過 AWR 直接查看，
但是有時 AWR 信息顯示不全，默認只顯示 TOP 10，
可以通過 dba_hist_sqltext，dba_hist_sqlstat 等查看更詳細的信息；

— 查看快照信息
— 選擇 2018-06-14 全天的快照 6504-6528；
—conn chenjch/chenjch
select SNAP_ID,
DBID,
to_char(BEGIN_INTERVAL_TIME, yyyy-mm-dd hh34:mi:ss),
to_char(END_INTERVAL_TIME, yyyy-mm-dd hh34:mi:ss),
FLUSH_ELAPSED,
SNAP_LEVEL
from dba_hist_snapshot order by 1;

—1 查看 2018-06-14 全天 SQL ordered by Elapsed Time
— 時間單位默認 microseconds
select a.sql_id,
a.module,
a.elap,
a.exec,
decode(a.exec, 0, to_number(null), (a.elap / a.exec)) elap_one,
b.sql_text
from dba_hist_sqltext b,
(select sql_id,
max(module) module,
sum(elapsed_time_delta) / 1000000 elap,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = 1000919065
and instance_number = 1
and 6504 snap_id
and snap_id = 6528
group by sql_id) a
where a.sql_id = b.sql_id
order by elap desc;

—2 查看 2018-06-14 全天 SQL ordered by Gets
— 時間單位默認 microseconds
select a.sql_id,
a.module,
a.bget,
decode(a.exec, 0, to_number(null), a.bget / a.exec) get_per_exec,
a.exec,
(a.cput/1000000)/elap*100 CPU,
a.elap,
b.sql_text
from dba_hist_sqltext b,
(select sql_id,
max(module) module,
sum(buffer_gets_delta) bget,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) / 1000000 elap
from dba_hist_sqlstat
where dbid = 1000919065
and instance_number = 1
and 6504 snap_id
and snap_id = 6528
group by sql_id) a
where a.sql_id = b.sql_id
order by bget desc;

—3 查看 2018-06-14 全天 SQL ordered by Reads
— 時間單位默認 microseconds
select a.sql_id,
a.module,
a.diskread,
a.exec,
decode(a.exec, 0, to_number(null), a.diskread / a.exec) diskread_one,
(a.cput/1000000)/elap*100 CPU,
a.elap,
b.sql_text
from dba_hist_sqltext b,
(select sql_id,
max(module) module,
sum(disk_reads_delta) diskread,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) / 1000000 elap
from dba_hist_sqlstat
where dbid = 1000919065
and instance_number = 1
and 6504 snap_id
and snap_id = 6528
group by sql_id) a
where a.sql_id = b.sql_id
order by diskread desc;