Oracle AutoTRACE使用分析+實(shí)例

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

AUTOTRACE是一項(xiàng) SQL*Plus 功能，自動(dòng)跟蹤為 SQL 語(yǔ)句生成一個(gè)執(zhí)行計(jì)劃并且提供與該語(yǔ)句的處理有關(guān)的統(tǒng)計(jì)。

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

使用AUTOTRACE不會(huì)產(chǎn)生跟蹤文件。

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

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

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

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

    AutoTrace進(jìn)行優(yōu)化的注意事項(xiàng)

1．可以通過(guò)設(shè)置timing來(lái)得到執(zhí)行SQL所用的時(shí)間，但不能僅把這個(gè)時(shí)間來(lái)當(dāng)作SQL執(zhí)行效率的唯一量度。這個(gè)時(shí)間會(huì)包括進(jìn)行AUTOTRACE的一些時(shí)間消耗，所以這個(gè)時(shí)間并不僅僅是SQL執(zhí)行的時(shí)間。這個(gè)時(shí)間會(huì)與SQL執(zhí)行時(shí)間有一定的誤差，而在SQL比較簡(jiǎn)單的時(shí)候尤為明顯。

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

AutoTrace是Oracle中優(yōu)化工具中最基本的工具，雖然功能比較有限，但足以滿足我們?nèi)粘９ぷ鞯男枰?/p>

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

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

已用時(shí)間:  00: 00: 04.46                                               

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

索引已創(chuàng)建。

已用時(shí)間:  00: 00: 04.57

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

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

已用時(shí)間:  00: 00: 00.01

使用索引之后：0.01秒

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

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

已用時(shí)間:  00: 00: 02.09

執(zhí)行計(jì)劃
----------------------------------------------------------
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;

表已更改。

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

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

已用時(shí)間:  00: 00: 00.04

什么情況下索引不起作用：
1、類型不匹配時(shí)

2、條件列包含函數(shù)但沒有創(chuàng)建函數(shù)索引時(shí)

3、復(fù)合索引中的前導(dǎo)列沒有被作為查詢條件

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

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

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]