Oracle AutoTRACE使用分析+實例

*環(huán)境：windowsXP + Oracle10gR2
*AutoTRACE是分析SQL的執(zhí)行計劃，執(zhí)行效率的一個非常簡單方便的工具
*/

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

SQL*Plus AUTOTRACE 可以用來替代 SQL Trace 使用，AUTOTRACE 的好處是您不必設置跟蹤文件的格式，并且它將自動為 SQL 語句顯示執(zhí)行計劃。然而，AUTOTRACE 分析和執(zhí)行語句；而EXPLAIN PLAN僅分析語句。-
 

使用AUTOTRACE不會產生跟蹤文件。

     SQLPLUS的AutoTrace是分析SQL的執(zhí)行計劃，執(zhí)行效率的一個非常簡單方便的工具，在絕大多數(shù)情況下，也是非常有用的工具。利用AutoTrace工具提供的SQL執(zhí)行計劃和執(zhí)行狀態(tài)可以為我們優(yōu)化SQL的時候提供優(yōu)化的依據(jù)，以及優(yōu)化效果的明顯的對比效果。

用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

舉例：
SET AUTOT[RACE] OFF 停止AutoTrace
SET AUTOT[RACE] ON 開啟AutoTrace，顯示AUTOTRACE信息和SQL執(zhí)行結果
SET AUTOT[RACE] TRACEONLY 開啟AutoTrace，僅顯示AUTOTRACE信息
SET AUTOT[RACE] ON EXPLAIN 開啟AutoTrace，僅顯示AUTOTRACE的EXPLAIN信息
SET AUTOT[RACE] ON STATISTICS開啟AutoTrace，僅顯示AUTOTRACE的STATISTICS信息

結果解釋
physical reads 物理讀——執(zhí)行SQL的過程中，從硬盤上讀取的數(shù)據(jù)塊個數(shù)
redo size      重做數(shù)——執(zhí)行SQL的過程中，產生的重做日志的大小
bytes set via sql*net to client  通過sql*net發(fā)送給客戶端的字節(jié)數(shù)
bytes received via sql*net from client  通過sql*net接受客戶端的字節(jié)數(shù)
sorts(memory)  在內存中發(fā)生的排序
sorts(disk)    不能在內存中發(fā)生的排序，需要硬盤來協(xié)助
rows processed 結果的記錄數(shù)

    AutoTrace進行優(yōu)化的注意事項

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

2． 判斷SQL效率高低應該通過執(zhí)行SQL執(zhí)行狀態(tài)里面的邏輯讀的數(shù)量
     邏輯讀 =（db block gets+ consistent gets）
總結

AutoTrace是Oracle中優(yōu)化工具中最基本的工具，雖然功能比較有限，但足以滿足我們日常工作的需要。

   在Oracle9i中需要運行$ORACLE_HOME\RDBMS\ADMIN\utlxplan.sql腳本生成plan_table表；
   在Oracle10g中PLAN_TABLE不再需要創(chuàng)建，Oracle缺省增加了一個字典表PLAN_TABLE$，然后基于PLAN_TABLE$創(chuàng)建公用同義詞供用戶使用

關于Autotrace幾個常用選項的說明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 報告，這是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只顯示優(yōu)化器執(zhí)行路徑報告
SET AUTOTRACE ON STATISTICS -- 只顯示執(zhí)行統(tǒng)計信息
SET AUTOTRACE ON ----------------- 包含執(zhí)行計劃和統(tǒng)計信息
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);

索引已創(chuàng)建。

已用時間:  00: 00: 04.57

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

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

已用時間:  00: 00: 00.01

使用索引之后：0.01秒

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

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

已用時間:  00: 00: 02.09

執(zhí)行計劃
----------------------------------------------------------
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、條件列包含函數(shù)但沒有創(chuàng)建函數(shù)索引時

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

4、CBO模式下選擇的行數(shù)比例過大，優(yōu)化器采取了全表掃描

5、CBO模式下表很就沒分析，表的增長明顯，優(yōu)化器采取了全表掃描

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]