Oracle最佳化工具——AutoTrace

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

一． AutoTrace的設定

SQL> connect / as sysdba

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant select,update,insert,delete on plan_table to public;

Grant succeeded.

SQL> @?/sqlplus/admin/plustrce.sql

SQL>grant plustrace to public.

二． AutoTrace的使用

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

SQL> conn ny_lx/test

已連線。

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中最佳化工具中最基本的工具，雖然功能比較有限，但足以滿足我們日常工作的需要。