最浪费磁盘读操作的前10 个语句占所有语句的比例
Set serverout on;
DECLARE
CURSOR c1 is
select disk_reads
from v$sqlarea
order by disk_reads DESC;
CURSOR c2 is
select sum(disk_reads)
from v$sqlarea;
Sumof10 NUMBER:=0;
mydr NUMBER;
mytotdr NUMBER;
BEGIN
dbms_output.put_line('Percent');
dbms_output.put_line('-------');
OPEN c1;
FOR i IN 1..10 LOOP
FETCH c1 INTO mydr;
sumof10 := sumof10 + mydr;
END LOOP;
CLOSE c1;
OPEN c2;
FETCH c2 INTO mytotdr;
CLOSE c2;
dbms_output.put_line(sumof10/mytotdr*100);
END;
343
/
示例输出
Percent
5.5183036
另一条/简单快速的SQL 语句
select sum(pct_bufgets)
from ( select rank() over ( order by disk_reads desc ) as rank_bufgets,
to_char(100 * ratio_to_report(disk_reads) over (), '999.99') pct_bufgets
from v$sqlarea )
where rank_bufgets < 11;
DECLARE
CURSOR c1 is
select disk_reads
from v$sqlarea
order by disk_reads DESC;
CURSOR c2 is
select sum(disk_reads)
from v$sqlarea;
Sumof10 NUMBER:=0;
mydr NUMBER;
mytotdr NUMBER;
BEGIN
dbms_output.put_line('Percent');
dbms_output.put_line('-------');
OPEN c1;
FOR i IN 1..10 LOOP
FETCH c1 INTO mydr;
sumof10 := sumof10 + mydr;
END LOOP;
CLOSE c1;
OPEN c2;
FETCH c2 INTO mytotdr;
CLOSE c2;
dbms_output.put_line(sumof10/mytotdr*100);
END;
343
/
示例输出
Percent
5.5183036
另一条/简单快速的SQL 语句
select sum(pct_bufgets)
from ( select rank() over ( order by disk_reads desc ) as rank_bufgets,
to_char(100 * ratio_to_report(disk_reads) over (), '999.99') pct_bufgets
from v$sqlarea )
where rank_bufgets < 11;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-707732/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
查询25 个滥用磁盘读操作的最主要语句
下一篇：
自动撤消管理
请登录后发表评论
登录
全部评论
