专题 › Oracle技术专区 › 抓最费资源SQL的SQL

oraask2 发表于 2010-11-18 15:59:07

抓最费资源SQL的SQL

--执行时间最长的
select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text，
sql_fulltext
from v$sqlarea
where elapsed_time > 20000
order by elapsed_time desc) a
where rownum < =10

--sCPU TIME最大的
elect rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text,
sql_fulltext
from v$sqlarea
where cpu_time > 20000
order by cpu_time desc) a
where rownum < =10

--逻辑读最多的
select rownum as rank, a.*
from (
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text,
sql_fulltext
from v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc) a
where rownum < =10

--物理读最多的
select rownum as rank, a.*
from (
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text,
sql_fulltext
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc) a
where rownum < =10
处理的数据量最大的
select rownum as rank, a.*
from (
select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value,
sql_text,
sql_fulltext
from v$sqlarea
where rows_processed > 10000
order by rows_processed desc) a
where rownum < =10

oraask2 发表于 2010-11-18 16:00:10

标题写的有点像大后天（明天的明天的明天）。。

kevin.zhang 发表于 2010-11-18 16:07:24

谢谢分享，以后可以直接复制粘贴用了。
不过书写检查还是要严谨点，譬如少了标点符号啊，中英文逗号 ",""，" 问题啊

oraunix 发表于 2010-11-19 08:59:35

坤哥，做得不错。

cnahwhtj 发表于 2010-11-29 15:22:38

好东东，多谢分享!

f418000561 发表于 2014-3-28 16:34:42

支持下，不错

Iris1988 发表于 2014-3-29 15:59:16

支持下吧，虽然需要更改下。。

fly8007 发表于 2014-6-23 14:06:51

支持下，:lol:lol:lol:lol:lol:lol

查看完整版本: 抓最费资源SQL的SQL