在SQL*PLUS中，當(dāng)你成功的執(zhí)行一個(gè)DML語句，比如
SELECT
,
DELETE
,
UPDATE
,
INSERT，你
可以通過SQL優(yōu)化器和語句的執(zhí)行統(tǒng)計(jì)自動(dòng)的獲得一份報(bào)告。這份報(bào)告對于DML語句的性能監(jiān)控和調(diào)優(yōu)都是很有用處的。這份報(bào)告就是本文要講的AUTOTRACE 報(bào)告。
?
配置AUTOTRACE報(bào)告（Configuring the AUTOTRACE Report）
你可以通過以下的AUTOTRACE系統(tǒng)變量來配置AUTOTRACE報(bào)告. 如下表：
Table? AUTOTRACE Settings
|
AUTOTRACE Setting
|
Result
|
|
SET AUTOTRACE OFF
|
不能獲得
AUTOTRACE報(bào)告
. 這是默認(rèn)的.
|
|
SET AUTOTRACE ON EXPLAIN
|
僅僅顯示優(yōu)化器執(zhí)行計(jì)劃的
AUTOTRACE
報(bào)告
|
|
SET AUTOTRACE ON STATISTICS
|
僅僅顯示SQL語句執(zhí)行的統(tǒng)計(jì)結(jié)果的
AUTOTRACE
報(bào)告
|
|
SET AUTOTRACE ON
|
包括上面兩項(xiàng)內(nèi)容的AUTOTRACE報(bào)告
|
|
SET AUTOTRACE TRACEONLY
|
與
SET
AUTOTRACE
ON類似
,所有的統(tǒng)計(jì)和數(shù)據(jù)都在，但不可以打印
|
?
其實(shí)，平時(shí)我們應(yīng)該較多的就是SET AUTOTRACE ON 、SET AUTOTRACE OFF,誰會在乎多看一點(diǎn)AUTOTRACE報(bào)告呢！
J
安裝AUTOTRACE報(bào)告（Setups Required for the AUTOTRACE Report）
要用這個(gè)特性，用戶必須被賦予PLUSTRACE角色，而PLUSTRACE角色需要DBA來賦予。另外，該用戶必須創(chuàng)建PLAN_TABLE表。
I.????????????????
首先創(chuàng)建PLUSTRACE角色并且賦給DBA：
CONNECT sys/sys’s password AS SYSDBA
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
II.?????????????
賦權(quán)限給用戶
CONNECT / AS SYSDBA
GRANT PLUSTRACE TO USER（預(yù)賦權(quán)的用戶名）;
?
這樣，就可以在該用戶下設(shè)置AUTOTRACE報(bào)告的顯示與否了。
?
AUTOTRACE報(bào)告中涉及到的兩個(gè)方面的內(nèi)容
I．?????????????
SQL語句的執(zhí)行計(jì)劃
執(zhí)行計(jì)劃就是SQL優(yōu)化器執(zhí)行語句的查詢執(zhí)行順序，每一行的執(zhí)行計(jì)劃都會有個(gè)行號，這個(gè)行號是連續(xù)的
II．?????????
SQL語句的數(shù)據(jù)庫統(tǒng)計(jì)
數(shù)據(jù)庫統(tǒng)計(jì)是服務(wù)器記錄的執(zhí)行當(dāng)前的這條語句所需要的系統(tǒng)資源等，主要包括下表的內(nèi)容
|
Database Statistic Name
|
Description
|
|
recursive calls
|
Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
|
|
db block gets
|
Number of times a CURRENT block was requested.
|
|
consistent gets
|
Number of times a consistent read was requested for a block.
|
|
physical reads
|
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
|
|
redo size
|
Total amount of redo generated in bytes.
|
|
bytes sent via SQL*Net to client
|
Total number of bytes sent to the client from the foreground processes.
|
|
bytes received via SQL*Net from client
|
Total number of bytes received from the client over Oracle Net.
|
|
SQL*Net roundtrips to/from client
|
Total number of Oracle Net messages sent to and received from the client.
|
|
sorts (memory)
|
Number of sort operations that were performed completely in memory and did not require any disk writes.
|
|
sorts (disk)
|
Number of sort operations that required at least one disk write.
|
|
rows processed
|
Number of rows processed during the operation.
|
?
簡單使用的例子：
SQL> show user
USER is "SYS"
（注：當(dāng)前是sysdba用戶）
（注：創(chuàng)建PLUSTRACE角色并且賦給DBA）
SQL> start $ORACLE_HOME/sqlplus/admin/plustrce.sql
（注：start 等價(jià)于@）
SQL> drop role plustrace;
drop role plustrace
????????? *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
?
?
SQL> create role plustrace;
?
Role created.
?
SQL>
SQL> grant select on v_$sesstat to plustrace;
?
Grant succeeded.
?
SQL> grant select on v_$statname to plustrace;
?
Grant succeeded.
SQL>
?
SQL> grant select on v_$session to plustrace;
?
Grant succeeded.
?
SQL> grant plustrace to dba with admin option;
?
Grant succeeded.
?
SQL>
SQL> set echo off
（注：賦角色plustrace給所需用戶，這里用戶是bill）
SQL> grant plustrace to bill;
?
Grant succeeded.
（注：接下來，用bill用戶連接，測試AUTOTRACE）
SQL> connect bill/bill’s password;
Connected.
SQL> show user
USER is "BILL"
(注：為了看的清楚些，建立一個(gè)測試的表test，然后察看AUTOTRACE報(bào)告)
SQL>? create table test(id number(1));
?
Table created.
?
SQL> insert into test values(1);
?
1 row created.
?
SQL> /
?
1 row created.
?
SQL> /
?
1 row created.
?
SQL> /
?
1 row created.
?
SQL> commit;
?
Commit complete.
SQL> select * from test;
?
??????? ID
----------
???????? 1
???????? 1
???????? 1
???????
?1
?
SQL> set AUTOTRACE on
SQL> select * from test;
?
??????? ID
----------
???????? 1
???????? 1
???????? 1
???????? 1
?
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT Optimizer=CHOOSE
?? 1??? 0?? TABLE ACCESS (FULL) OF 'TEST'
?
?
?
?
Statistics
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
????????? 4? consistent gets
????????? 0? physical reads
????????? 0? redo size
??????? 547? bytes sent via SQL*Net to client
??????? 655? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
4????????
rows processed
?
上面我們就可以看到，當(dāng)你設(shè)置了set AUTOTRACE on后，執(zhí)行相應(yīng)的DML語句，就會有相應(yīng)的AUTOTRACE報(bào)告出現(xiàn)了。當(dāng)然，我們的這種設(shè)置是session級別的。這樣，我們就可以根據(jù)AUTOTRACE報(bào)告對我們執(zhí)行的DML語句進(jìn)行分析和調(diào)優(yōu)了！
轉(zhuǎn)自：http://dev.csdn.net/develop/article/23/23430.shtm