找到Oracle資料庫中效能最差的查詢語句

“V$SQLAREA”和“ V$SQL”是非常有用的視圖，在其中你可以尋找發現執行效率最差的需要最佳化的SQL語句。“DISK_READS”列的值表示在系統中執行該語句讀取的磁碟量。

這個與執行(DISK_READS/EXECUTIONS)相結合，返回每個語句執行實現最佳磁碟命中率的SQL 陳述式。排在這個列表中最上方的任何語句都最可能是有問題的查詢，需要進行最佳化。AWR報告或者Statspack報告也列出了資源密集的查詢。

下面的查詢語句可以用來尋找你資料庫中效能最差的查詢：

select b.username username, a.disk_reads reads,  a.executions exec, a.disk_reads /decode  (a.executions, 0, 1,a.executions) rds_exec_ratio,  a.sql_text Statement  from V$sqlarea a, dba_users b  where a.parsing_user_id = b.user_id  and a.disk_reads > 100000  order by a.disk_reads desc;  USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT  -------- ------- ----- --------------- ---------------------  ---------------------------------------------------  ADHOC1 7281934 1 7281934 select custno, ordno  from cust, orders  ADHOC5 4230044 4 1057511 select ordno  from orders where trunc(ordno) = 721305  ADHOC1 801716 2 400858 select custno,  ordno from cust where substr(custno,1,6) = '314159'

前面語句中的“DISK_READS”列可以被“BUFFER_GETS”列替代，查出關於需要最大記憶體量的SQL語句。

現在考慮第二個例子的輸出，這個例子是對一個有十億行級的表(EMP3)進行行數統計的，還有對原本1.3億行資料但刪除所有資料後只保留了前面15條資料的表(EMP2)進行行數統計。請注意，Oracle一直根據EMP2的高水位(HWM)計數(它會讀取超過80萬個塊，8k塊，儘管所有資料只剩下不到1個塊大小了)。下面列表可以展示給你，對錶EMP2的錯誤查詢，既然該表只剩下15行了，該查詢需要被調整(對該表做分析將不會改善這一點)。

USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT  　　-------- ------- ----- --------------- -------------------------  　　SCOTT 5875532 1 5875532 select count(*) from emp3  　　SCOTT 800065 1 800065 select count(*) from emp2

對於這個問題，如果EMP2表完全是空的，你可以簡單地清空表(truncate)來解決這個問題。但是因為該表仍然有15行資料，所以你有幾種處理方案。選擇哪種方法取決於你的具體情況。你可以：

匯出/清空/匯入;建立暫存資料表emp2b(CREATE TABLE emp2b AS SELECT * FROM emp2 );然後刪除表並重新命名暫存資料表(我有必要關心索引和相關對象等等)。

執行“ALTER TABLE emp2 MOVE TABLESPACE new1”重建索引。

如果有主鍵，請使用“DBMS_REDEFINITION.CAN_REDEF_TABLE”驗證該表可以聯機重定義。

請檢查Oracle文檔瞭解每個選項的文法、優點、缺點以及使用約定(此處為列舉全部)，這樣你可以為你的實際情況應用最好的選項(這些選項每個都有一些主要缺點，包括使用者不能訪問表和被刪除的相關對象，取決於你用的哪個選項，所以要小心一些)。一旦我重組了表，下一次“count(*)”只會讀取一個快，而不是800，065個塊(這個問題很值得處理)。要注意在這個查詢中，我把表“emp2”改成了“emP2”，這樣我可以在緩衝中找到遊標。

alter table emp2 move; -- You can specify a tablespace  select count(*)  from emP2;  select b.username username, a.disk_reads reads,  a.executions exec, a.disk_reads /decode  (a.executions, 0, 1,a.executions) rds_exec_ratio,  a.sql_text Statement  from V$sqlarea a, dba_users b  where a.parsing_user_id = b.user_id  and a.sql_text like '%emP2%'order by a.disk_reads desc;  USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT  -------- ------- ----- --------------- ---------------------  SCOTT 1 1 1 select count(*) from emP2

你還可以收縮表、索引組織表，索引，分區，子分區，物化視圖或者物化視圖日誌的空間。你可以使用“ALTER TABLE，ALTER INDEX，ALTER MATERIALIZED VIEW或者ALTER MATERIALIZED VIEW LOG”語句加上“SHRINK SPACE”從句實現這一目的。請參見“Oracle管理員指南”瞭解更多資訊。最後，如果你想使用“ALTER TABLE 表名 MOVE TABLESPACE 資料表空間名”命令，要考慮使用相同大小的資料表空間(或者如果可以的話用更小一點的資料表空間)來回移動資料，這樣不會太浪費空間。