文章內(nèi)容

Oracle AutoTRACE使用分析+實例

發(fā)布時間: 2012/9/14 16:40:19

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

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

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

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

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í)行結(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的過程中，從硬盤上讀取的數(shù)據(jù)塊個數(shù)
redo size 重做數(shù)——執(zhí)行SQL的過程中，產(chǎn)生的重做日志的大小
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) 在內(nèi)存中發(fā)生的排序
sorts(disk) 不能在內(nèi)存中發(fā)生的排序，需要硬盤來協(xié)助
rows processed 結(jié)果的記錄數(shù)

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

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

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

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

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

關(guān)于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 當(dāng)用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、復(fù)合索引中的前導(dǎo)列沒有被作為查詢條件

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

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

本文出自：億恩科技【www.ypdoo.com.cn】

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