如何找出消耗资源的sql语句

--CPU高的SQL
select sql_text from v$sql order by cpu_time desc
--逻辑读多的SQL：
select * from (select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 500000
order by buffer_gets desc) where rownum<=30;
--执行次数多的SQL ：
select sql_text,executions from
(select sql_text,executions from v$sqlarea order by executions desc)
where rownum<81;
--读硬盘多的SQL ：
select sql_text,disk_reads from
(select sql_text,disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;
--排序多的SQL ：
select sql_text,sorts from
(select sql_text,sorts from v$sqlarea order by sorts desc)
where rownum<21;
                            
--CPU消耗高，如果确认是oracle进程做的
--那么直接在OS上抓到消耗CPU的PID，然后查出是哪个session，找出相应的SQL
@getsqlbypid
select sql_text from v$sqltext where hash_value = (
select sql_hash_value from v$session where sid = (
select s.sid from
v$session s, v$process p
where p.addr  = s.paddr
and   p.spid = &ospid
))
order by piece;

已有 0 人发表留言，猛击->> 这里<<-参与讨论


ITeye推荐

• —软件人才免语言低担保 赴美带薪读研！—