監控SQL語句，獲取執行計劃和執行成本，是每個Oracle開發人員與DBA所必須具備的能力之一。

當Oracle徹底進入CBO時代，我們面對一種全新的局面。一方面，基於資料統計量的CBO最佳化器，讓SQL語句執行計劃生成更加科學、更加有效。但是另一方面，最佳化器和最佳化模式也變得越來越複雜。我們對於一個SQL操作的執行計劃關注度和不確定程度同時在提升。

在這樣的情況下，Oracle提供了一系列的觀察SQL執行計劃的工具，其中Autotrace工具是最簡單和常用的工具。

簡單的說，Autotrace工具具有對SQL語句執行計劃進行分析輸出，以及評估執行使用資源。下面，我們對Autotrace工具從安裝、使用到結果分析進行簡單的介紹。

Autotrace安裝

首先，有一點需要說明：Autorace工具是在Oracle提供的Sqlplus視窗上使用。在如pl/sql developer之類的開發工具上是不能使用的。sqlplus雖然使用簡單，但是存在介面互動性差的特點。所以如果在Windows平臺上，可以在sqlplusw中使用autotrace。不過很遺憾的是，Oracle11g中，sqlplusw這個雞肋工具也已經不存在了。

還有一點建議的是，Autotrace是一個很安全的除錯工具。最好可以設定給所有Oracle可以訪問到（賦給public使用者）。但是，安裝的過程中因為存在賦許可權相關的內容，最好使用sys使用者登入。

安裝Autotrace工具，共分為兩個步驟：

步驟一、建立plan_table資料表。Plan_table是Oracle中一個很常用的資料表。它主要為各種工具提供裝載生成sql執行計劃的空間。

//使用Windows環境下做演示；

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 1月 30 20:22:07 2011

SQL> conn / as sysdba;

已連線。

SQL> show user

USER 為 "SYS"

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

表已建立。

建立資料表：plan_table是透過指令碼utlxplan.sql實現的。該指令碼在${ORACLE_HOME}/rdbms/admin目錄下（注意windows和Linux/Unix目錄結構的差異）。

該端演示是使用sqlplusw進行的。其中？表示${ORACLE_HOME}目錄，在sqlplus中可以使用？來進行代表。如果在其他工具下，比如PL/SQL Developer下，可以在command視窗下使用 @{指令碼路徑}\utlxplan.sql。該指令碼只是一個create table plan_table命令，如果有興趣可以單獨開啟該檔案。

之後，透過公有同義詞和授權方法，將plan_table使用權給所有使用者。

SQL> create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public;

授權成功。

步驟二、安裝autotrace指令碼

autotrace工具指令碼執行。在sqlplusw視窗下執行:

SQL> @?\sqlplus\admin\plustrce.sql //注意是plustrce.sql，不是plustrace.sql，筆者當年困惑了好久。。。

SQL>

SQL> drop role plustrace;

角色已刪除。

SQL> create role plustrace;

角色已建立。

SQL>

SQL> grant select on v_$sesstat to plustrace;

授權成功。

SQL> grant select on v_$statname to plustrace;

授權成功。

SQL> grant select on v_$mystat to plustrace;

授權成功。

SQL> grant plustrace to dba with admin option;

授權成功。

上面的指令碼中，是需要輸入的內容，其他都是輸入指令碼的語句。

最後，如果希望所有的使用者都能使用的autotrace工具，只要將plustrace角色賦給public就可以了。

SQL> grant plustrace to public;

授權成功。

設定結束後，我們已經完全安裝好autotrace工具了。下面簡單介紹一下，如果和使用autotrace工具。

使用autotrace

使用autorace是在sqlplus環境下，透過對autotrace的開啟關閉來設定。下面是一個簡單的例子。

SQL> conn scott/tiger@orcl

已連線。

SQL> set timing on; //設定其可以顯示出所有sql語句的執行時間，非常實用！！

SQL> set autotrace on; //設定所有的內容。

SQL> select count(*) from emp;

COUNT(*)

---------- ………………………語句執行結果

14

已用時間: 00: 00: 00.03

執行計劃

----------------------------------------------------------

Plan hash value: 2937609675

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------

統計資訊

----------------------------------------------------------

219 recursive calls

0 db block gets

41 consistent gets

1 physical reads

0 redo size

408 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

觀察這個輸出結果，我們可以看出結果分為下面幾個部分：

1、輸出結果：SQL語句的結果；

2、執行計劃；

3、統計資訊；

每個部分分別承擔了不同的地位和作用。但是有一點需要注意，輸出的執行計劃和依據，都是根據統計量進行評估的結果。實際執行的成本常常與執行計劃後面每個步驟的數值有差異。

對set autotrace命令，還存在一些變種，方便使用。

ü set autotrace on explain; 只顯示出執行計劃；

ü set autotrace on statistics; 只顯示出統計量資訊；

ü set autotrace traceonly; 不顯示查詢結果；

ü set autotrace traceonly explain; 只顯示執行語句，並且不會執行語句。最適合進行大表操作的預估算；

最後，我們介紹一下統計量的各部分含義。

專案	含義	備註
recursive calls	遞迴呼叫SQL的個數；Oracle在執行這個SQL的時候，有時候會生成很多額外的SQL語句，這個就成為遞迴呼叫；
db block gets	邏輯讀，從資料buffer cache中讀取；進行current模式讀取；
consistent gets	邏輯讀，進行一致讀模式讀取；
physical reads	物理讀成本；
redo size	產生重做日誌大小
bytes sent via SQL*Net to client	利用sql*net傳入到client的位元組數；
bytes received via SQL*Net from client	利用sql*net傳出client的位元組數；
SQL*Net roundtrips to/from client
sorts (memory)	記憶體中排序空間使用；
sorts (disk)	物理儲存中排序空間使用；	如果memory空間使用不足，是會使用disk的空間的；
rows processed

Autorace工具是我們經常使用的效能評測工具，能夠幫助解決很多問題。與其相同地位的還有dbms_xplan包方法和set events跟蹤事件檔案。

Autotrace工具使用——小工具，大用場

相關文章