Oracle 中定位重要(消耗资源多)的SQL

1. 查看消耗内存最多的sql(v$sqlarea)

select b.username ,
       a.buffer_gets , --所有子游标运行这条语句导致的读内存次数
       a.executions,   --所有子游标的执行这条语句次数
       a.buffer_gets/decode(a.executions,0,1,a.executions),--这条语句执行一次读取内存次数
       a.sql_text SQL
from v$sqlarea a,dba_users b
where a.parsing_user_id = b.user_id and a.buffer_gets >10000
order by buffer_gets desc;

2.查看消耗磁盘多的sql(v$sqlarea)

select b.username ,
       a.disk_reads ,  --所有子游标运行这条语句导致的读磁盘次数
       a.executions,   --所有子游标的执行这条语句次数
       a.disk_reads/decode(a.executions,0,1,a.executions),----这条语句执行一次读取磁盘次数
       a.sql_text SQL
from v$sqlarea a,dba_users b
where a.parsing_user_id = b.user_id and a.DISK_READS >10000
order by disk_reads desc;

3.查看执行次数多的SQL(v$sqlarea)

select sql_text, executions 
from v$sqlarea 
where rownum<81
order by executions desc

4.查看排序多的SQL(v$sqlarea)

select sql_text, sorts 
from v$sqlarea 
order by sorts desc
where rownum<21;

5.分析的次数太多，执行的次数太少，要用绑变量的方法来写sql(v$sqlarea)

select substr(sql_text,1,80) "sql",count(*),sum(executions) "totexecs"
from v$sqlarea
where executions<5  --sql_text 执行次数小于5
group by substr(sql_text,1,80)
having count(*)>30  --sql_text 分析次数大于30
order by 2;

6.前5位用户I/O等待最高的SQL语句 (v$sqlarea)

select sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time 
from sys.v$sqlarea
where rownum < 6 
order by 5 desc

7.查看当前用户&username执行的SQL(v$sqltext_with_newlines,v$session)

select sql_text
from v$sqltext_with_newlines
where(hash_value, address) in
     (select sql_hash_value, sql_address
      from v$session
      where username='&username')
order by address, piece;