Oracle最佳化AutoTrace輸出內容的含義

SQLPLUS的AutoTrace是分析SQL的執行計畫，執行效率的一個非常簡單方便的工具，在絕大多數情況下，也是非常有用的工具。利用AutoTrace工具提供的SQL執行計畫和執行狀態可以為我們最佳化SQL的時候提供最佳化的依據，以及最佳化效果的明顯的對比效果。

在SQLPLUS中輸入相關AUTOTRACE命令，輸入想要最佳化的SQL語句，即可得到SQL的執行計畫和執行狀態資訊。

SQL> set timing on //開啟時間顯示
SQL> set autot traceonly //僅顯示trace結果，不顯示SQL執行結果
SQL> select * from ac01 where aac001=’9990000111′;

經過時間: 00: 00: 00.62

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘AC01′
2 1 INDEX (UNIQUE SCAN) OF ‘PK_AC01′ (UNIQUE)
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
1875 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
舉例：
SET AUTOT[RACE] OFF 停止AutoTrace
SET AUTOT[RACE] ON 開啟AutoTrace，顯示AUTOTRACE資訊和SQL執行結果
SET AUTOT[RACE] TRACEONLY 開啟AutoTrace，僅顯示AUTOTRACE資訊
SET AUTOT[RACE] ON EXPLAIN 開啟AutoTrace，僅顯示AUTOTRACE的EXPLAIN資訊
SET AUTOT[RACE] ON STATISTICS開啟AutoTrace，僅顯示AUTOTRACE的STATISTICS資訊

三．執行計畫的分析
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘AC01′
2 1 INDEX (UNIQUE SCAN) OF ‘PK_AC01′ (UNIQUE)
———————————————————-

這個就是SQL select * from ac01 where aac001=’9990000111′;執行計畫。
執行的過程為：
1）INDEX (UNIQUE SCAN) OF ‘PK_AC01′ (UNIQUE)
2）TABLE ACCESS (BY INDEX ROWID) OF ‘AC01′
執行計畫是一個樹狀結構，計劃的執行是從葉結點開始，直到根結點。所以不同的層上，越底層的越先被執行（第一列數字中較大的）；不同層上，越左邊的越先被執行（第二列數字中較小的）。
通過分析這個實行計劃可以知道以下幾點：
a）這是一條SELECT語句
b）資料庫系統現在使用的最佳化器模式為CHOOSE
c）執行的時候先通過AC01表上的唯一索引PK_AC01尋找到相應記錄的ROWID，然後通過索引的ROWID直接存取AC01表，找到相應的記錄。
這是一條比較簡單的SQL，所以執行計畫也相對來說比較簡單，沒有涉及到過多的串連和索引等。

四．執行狀態的分析
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
1875 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

這些資料就是這條SQL語句的執行狀態。下面分別說一下各個資料項目的含義：
（1）recursive calls
遞迴調用——執行SQL的時候的產生的遞迴調用的數量，這個參數和訪問資料字典的次數有很大的關係。一般來說，這個參數值不會很大。
（2）db block gets
DB塊取——在發生INSERT，DELETE，UPDATE，SELECT FOR UPDATE的時候，資料庫緩衝區中的資料庫塊的個數。在SELECT語句中一般為0。
（3）consistent gets
一致性讀——除了SELECT FOR UPDATE的時候，從資料庫緩衝區中讀取的資料區塊的個數
（4）physical reads
物理讀——執行SQL的過程中，從硬碟上讀取的資料快個數
（5）redo size
重做數——執行SQL的過程中，產生的重做日誌的大小
（6）bytes set via sql*net to client
通過sql*net發送給用戶端的位元組數
（7）bytes received via sql*net from client
通過sql*net接受用戶端的位元組數
（8）sql*net roundtrips to/from client
（9）sorts(memory)
在記憶體中發生的排序
（10）sorts(disk)
不能在記憶體中發生的排序，需要硬碟來協助
（11）rows processed
結果的記錄數

五．用AutoTrace進行最佳化的注意事項
1．可以通過設定timing來得到執行SQL所用的時間，但不能僅把這個時間來當作SQL執行效率的唯一量度。這個時間會包括進行AUTOTRACE的一些時間消耗，所以這個時間並不僅僅是SQL執行的時間。這個時間會與SQL執行時間有一定的誤差，而在SQL比較簡單的時候尤為明顯。
2．判斷SQL效率高低應該通過執行SQL執行狀態裡面的邏輯讀的數量
邏輯讀 =（db block gets+ consistent gets）
六．總結
AutoTrace是ORACLE中最佳化工具中最基本的工具，雖然功能比較有限，但足以滿足我們日常工作的需要。