oracle中怎麼確定效能差的SQL語句

前者很容易定位。所有的作業系統都可以讓我們查看 CPU 密集型任務。這些任務可以追溯到一個特定使用者，一個特定應用程式模組。 CPU 密集型模組一般都是由較差的代碼和/或結構造成，而不是效能差的 SQL。一旦確定模組，你必須試圖使之更有效率。一個可能的解決方案是將把某些處理移除程式，讓資料庫處理（高明點的 SQL，儲存物件，內嵌函式，數組處理等）。

第二個是 I/O 密集型的 SQL 陳述式。這些語句會導致大量的資料庫 I/O（全表掃描，排序，更新等），並以很高代價運行幾個小時。從 Oracle 7 開始，解決了 SQL 識別問題。通過查詢資料庫共用池地區，我們可以很容易確定大多數 I/O 密集型 SQL 陳述式。

下面 SQL 陳述式示範了如何確定 I/O 命中率低於 80%的 SQL 陳述式。這個命中率是，自從 SQL 陳述式第一次被解析到共用池，通過所有執行的語句反應整體 I/O。下面可能是最近幾分鐘或幾天的結果：

代碼如下

複製代碼

sql> SELECT executions,    2        disk_reads,    3        buffer_gets,    4        ROUND((buffer_gets - disk_reads) / buffer_gets, 2) hit_ratio,    5        sql_text    6     FROM   v$sqlarea    7    WHERE  executions  > 0    8     AND    buffer_gets > 0    9     AND    (buffer_gets - disk_reads) / buffer_gets < 0.80    10   order by 4 desc ;   EXECUTIONS DISK_READS BUFFER_GETS  HIT_RATIO SQL_TEXT ---------- ---------- ----------- ---------- -----------------------------------------------------------------------         16        180         369        .51 SELECT SKU,PREPACK_IND,CASE_ID,TRANSFER_QTY,UNIT_COST,UNIT_RETAIL,ROWID                                              FROM TSF_DETAIL WHERE transfer = :1  order by sku         16        30          63         .52 SELECT TRANSFER,TO_STORE,TO_WH FROM TSFHEAD  WHERE TRANSFER = :b1  AND                                              TRANSFER_STATUS = 'A'         2         3           7          .57 SELECT SKU   FROM UPC_EAN  WHERE UPC = :b1         12        14          35         .60 SELECT SUBSTR(DESC_UP,1,30),DEPT,SYSTEM_IND   FROM DESC_LOOK  WHERE                                              SKU = :b1         14        13          35         .63 SELECT UNIT_COST,UNIT_RETAIL,SUBCLASS FROM WIN_SKUS WHERE SKU = :b1 事實上，我們發現對特定的 SQL，上面的資料有些誤導，其實語句沒有問題。考慮下面 v$sqlarea 輸出： Executions Disk_Reads Buffer_Gets Hit_Ratio Sql_Text ---------- ---------- ----------- --------- --------------------     2          6          19         0.68   SELECT A.EMP_NO, ...

該語句的命中率很低，但事實上它很有效。因為，SQL 是通過 UNIQUE 索引操作的，物理磁碟讀取的數量幾乎與邏輯讀取一樣。UNIQUE 索引顯著減少了整體的物理和邏輯磁碟 I/O 數量，導致了一個令人誤解的低命中率。

下面例子，命中率很好。但是真的很好嗎？

代碼如下	複製代碼
Executions Disk_Reads Buffer_Gets Hit_Ratio Sql_Text ---------- ---------- ----------- --------- --------------------     2         3625       178777      0.98   SELECT A.EMP_NO, ...

這個 SQL 陳述式看上去很有效。但是， 當我們仔細看時，事情就不是那麼回事了。命中率並沒有透露出，該語句存在五個表串連，並且每次執行進行了超過 3600 個物理磁碟讀取。這是否太多了？是否有效？若不進一步研究，無法回答這兩個問題。事實上，這個執行個體中，五個表的中其一個錯誤地執行了全表掃描。通過重新構造 SQL，我們可以減少物理磁碟 I/O 到小於 50，同時，也顯著減少邏輯磁碟 I/O。巧合的是，命中率也下降到不到 70％。

我們首選 V$SQLAREA 查詢是每個語句執行的物理磁碟 I/O 的真實報告。命中率是資訊性的，但有時會產生誤導。邏輯 I/O 相關的很少。如果語句執行 1,000,000 個邏輯 I/O，但只用了不到十分之一秒，這就沒人在乎了。這是總的物理 I/O，幾乎消耗了所有的時間，和確定潛在不正確的 SQL。例如：

代碼如下

複製代碼

sql> SELECT sql_text, executions,              ROUND(disk_reads / executions, 2) reads_per_run,              disk_reads, buffer_gets,              ROUND((buffer_gets - disk_reads)                   / buffer_gets, 2) hit_ratio,              sql_text       FROM   v$sqlarea       WHERE  executions  > 0       AND    buffer_gets > 0       AND    (buffer_gets - disk_reads) / buffer_gets < 0.80       ORDER by 3 desc ;

 

前兩個語句會報告更具啟發性的結果：

代碼如下	複製代碼
Executions Reads_Per_Run Disk_Reads Buffer_Gets Hit_Ratio Sql_Text ---------- ------------- ---------- ----------- --------- ------------     2           3            6          19        0.68    SELECT ...     2         1812.5       3625       178777      0.98    SELECT ...

從視圖 V$SQLAREA 中，我們可以立即隔離所有具有高物理讀取的語句。這些語句可能並不一定低效或寫得不好，但恰恰是它們需要進一步調查或調整。