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;