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

1. 查看消耗記憶體最多的sql(v$sqlarea)

1 select b.username ,2        a.buffer_gets , --所有子遊標運行這條語句導致的讀記憶體次數3        a.executions,   --所有子遊標的執行這條語句次數4        a.buffer_gets/decode(a.executions,0,1,a.executions),--這條語句執行一次讀取記憶體次數5        a.sql_text SQL6 from v$sqlarea a,dba_users b7 where a.parsing_user_id = b.user_id and a.buffer_gets >100008 order by buffer_gets desc;

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

1 select b.username ,2        a.disk_reads ,  --所有子遊標運行這條語句導致的讀磁碟次數3        a.executions,   --所有子遊標的執行這條語句次數4        a.disk_reads/decode(a.executions,0,1,a.executions),----這條語句執行一次讀取磁碟次數5        a.sql_text SQL6 from v$sqlarea a,dba_users b7 where a.parsing_user_id = b.user_id and a.DISK_READS >100008 order by disk_reads desc;

3.查看執行次數多的SQL(v$sqlarea)

1 select sql_text, executions 2 from v$sqlarea 3 where rownum<814 order by executions desc

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

1 select sql_text, sorts 2 from v$sqlarea 3 order by sorts desc4 where rownum<21;

5.分析的次數太多，執行的次數太少，要用綁變數的方法來寫sql(v$sqlarea)

1 select substr(sql_text,1,80) "sql",count(*),sum(executions) "totexecs"2 from v$sqlarea3 where executions<5  --sql_text 執行次數小於54 group by substr(sql_text,1,80)5 having count(*)>30  --sql_text 分析次數大於306 order by 2;

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

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

7.查看目前使用者&username執行的SQL(v$sqltext_with_newlines,v$session)

1 select sql_text2 from v$sqltext_with_newlines3 where(hash_value, address) in4      (select sql_hash_value, sql_address5       from v$session6       where username='&username')7 order by address, piece;