TOP的sql查询

古城微笑少年丶 2022-08-12 00:26 158阅读 0赞

1、查找使用资源最多的 [**SQL**][SQL] 语句：  
（较高的磁盘读取（disk\_reads消耗I/O）和较高的逻辑读取（buffer\_gets消耗CPU）被用作衡量标准）  
select sql\_text from  
(select sql\_text,executions,buffer\_gets,disk\_reads  
from v$sql  
where buffer\_gets > 100000  
or disk\_reads > 100000  
order by buffer\_gets + 100\*disk\_reads DESC)  
where rownum <= 5;  
  
2、查找使用CPU最多的SQL语句：  
（较高的逻辑读取（buffer\_gets消耗CPU）被用作衡量标准）  
select sql\_text from  
(select sql\_text,executions,buffer\_gets,disk\_reads  
from v$sql  
where buffer\_gets > 100000  
order by buffer\_gets desc)  
where rownum <= 5;  
或者  
（直接使用v$sql里的cpu\_time）  
select sql\_text,  
round(cpu\_time/1000000, 2) cpu\_seconds from  
(select \* from v$sql order by cpu\_time desc)  
where rownum <= 5;  
  
  
3、查找使用磁盘I/O最多的SQL语句：  
（较高的磁盘读取（disk\_reads消耗I/O）被用作衡量标准）  
select sql\_text from  
(select sql\_text,executions,buffer\_gets,disk\_reads  
from v$sql  
where disk\_reads > 100000  
order by disk\_reads desc)  
where rownum <= 5;  
  
4、查找占用 [**数据库**][SQL] 时间最多的SQL语句：  
select sql\_text,round(elapsed\_time/1000000, 2) elapsed\_seconds,executions from  
(select \* from v$sql order by elapsed\_time desc)  
where rownum <= 5;  
  
5、查找执行次数（executions）最多的SQL语句：  
select sql\_text, executions from  
(select \* from v$sql  
where executions > 1000  
order by executions desc)  
where rownum <= 5;  
  
6、查找解析调用最多的SQL语句：  
select sql\_text,parse\_calls from  
(select \* from v$sql  
where parse\_calls > 1000  
order by parse\_calls desc)  
where rownum <= 5;  
  
7、查找使用共享内存最多的SQL语句：  
（使用共享内存大于1048576（bytes）的SQL语句会显示）  
select sql\_text,sharable\_mem from  
(select \* from v$sql  
where sharable\_mem > 1048576  
order by sharable\_mem desc)  
where rownum <= 5;

[SQL]: http://space.itpub.net/?uid-26194851-action-viewspace-itemid-750177