Oracle如何查看历史TOPSQL
这篇文章给大家分享的是有关Oracle如何查看历史TOP SQL的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
专注于为中小企业提供成都网站制作、成都网站设计服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业乐东黎族免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了成百上千家企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
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;
感谢各位的阅读!关于“Oracle如何查看历史TOP SQL”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
分享文章:Oracle如何查看历史TOPSQL
当前链接:http://myzitong.com/article/ghheoc.html