AutoTRACE是分析SQL的執行計劃，執行效率的一個非常簡單方便的工具

/* 2008/07/01 星期一
*蒙昭良
*環境：windowsXP + Oracle10gR2
*AutoTRACE是分析SQL的執行計劃，執行效率的一個非常簡單方便的工具
*/

 AUTOTRACE是一項 SQL*Plus 功能，自動跟蹤為 SQL 語句生成一個執行計劃並且提供與該語句的處理有關的統計。

SQL*Plus AUTOTRACE 可以用來替代 SQL Trace 使用，AUTOTRACE 的好處是您不必設定跟蹤檔案的格式，並且它將自動為 SQL 語句顯示執行計劃。然而，AUTOTRACE 分析和執行語句；而EXPLAIN PLAN僅分析語句。

使用AUTOTRACE不會產生跟蹤檔案。

 

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

用法: 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資訊

結果解釋
physical reads 物理讀——執行SQL的過程中，從硬碟上讀取的資料塊個數
redo size      重做數——執行SQL的過程中，產生的重做日誌的大小
bytes set via sql*net to client  透過sql*net傳送給客戶端的位元組數
bytes received via sql*net from client  透過sql*net接受客戶端的位元組數
sorts(memory)  在記憶體中發生的排序
sorts(disk)    不能在記憶體中發生的排序，需要硬碟來協助
rows processed 結果的記錄數 

    AutoTrace進行最佳化的注意事項

1． 可以透過設定timing來得到執行SQL所用的時間，但不能僅把這個時間來當作SQL執行效率的唯一量度。這個時間會包括進行AUTOTRACE的一些時間消耗，所以這個時間並不僅僅是SQL執行的時間。這個時間會與SQL執行時間有一定的誤差，而在SQL比較簡單的時候尤為明顯。

2． 判斷SQL效率高低應該透過執行SQL執行狀態裡面的邏輯讀的數量
     邏輯讀 =（db block gets+ consistent gets）
總結

AutoTrace是ORACLE中最佳化工具中最基本的工具，雖然功能比較有限，但足以滿足我們日常工作的需要。

 

   在Oracle9i中需要執行$ORACLE_HOME\RDBMS\ADMIN\utlxplan.sql指令碼生成plan_table表；
   在Oracle10g中PLAN_TABLE不再需要建立，Oracle預設增加了一個字典表PLAN_TABLE$，然後基於PLAN_TABLE$建立公用同義詞供使用者使用

關於Autotrace幾個常用選項的說明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 報告，這是預設模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只顯示最佳化器執行路徑報告
SET AUTOTRACE ON STATISTICS -- 只顯示執行統計資訊
SET AUTOTRACE ON ----------------- 包含執行計劃和統計資訊
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不顯示查詢輸出

 

1 在where中使用索引
SQL> set timing on
SQL> set autotrace on

沒有使用索引之前：全表掃描花4.46秒
SQL> select count(*) from test where wner='RISENET';

  COUNT(*)                                                                     
----------                                                                     
      1350                                                                     

已用時間:  00: 00: 04.46                                                

SQL> create index test_owner_index
  2  on test(owner);

索引已建立。

已用時間:  00: 00: 04.57

使用索引之後：0.01秒

SQL> select count(*) from test where wner='RISENET';

  COUNT(*)                                                                     
----------                                                                     
      1350                                                                     

已用時間:  00: 00: 00.01

 
2  當用count(*)使用全表掃描時，可以建立主鍵，這樣可以使用到索引
SQL> select count(*) from test;

  COUNT(*)
----------
    205880

已用時間:  00: 00: 02.09

執行計劃
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  4109   (1)| 00:00:50 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |   102K|  4109   (1)| 00:00:50 |
-------------------------------------------------------------------

SQL> alter table mzl
  2  add primary key (object_id)
  3  using index;

表已更改。

已用時間:  00: 00: 00.53
SQL> select count(*) from mzl;

  COUNT(*)
----------
     51473

已用時間:  00: 00: 00.04

什麼情況下索引不起作用：
1、型別不匹配時

2、條件列包含函式但沒有建立函式索引時

3、複合索引中的前導列沒有被作為查詢條件

4、CBO模式下選擇的行數比例過大，最佳化器採取了全表掃描

5、CBO模式下表很就沒分析，表的增長明顯，最佳化器採取了全表掃描