** 在 SQL*PLUS ** ** 中应用 AUTOTRACE REPORT **
** 作者：刘颖博 **
** 时间： 2004-1-12 **
** mail ** ** ： [email protected] ** ** ，请指正 **
** 转载请注明出处及作者 ** ** **
在 SQL*PLUS中，当你成功的执行一个DML语句，比如 SELECT , DELETE , UPDATE , INSERT，你 可以通过 SQL优化器和语句的执行统计自动的获得一份报告。这份报告对于DML语句的性能监控和调优都是很有用处的。这份报告就是本文要讲的AUTOTRACE 报告。
** 配置 AUTOTRACE报告（Configuring the AUTOTRACE Report） **
你可以通过以下的 AUTOTRACE系统变量来配置AUTOTRACE报告. 如下表：
_ Table AUTOTRACE Settings _
** AUTOTRACE Setting ** ** **
|
** Result ** ** **
---|---
SET AUTOTRACE OFF
|
不能获得 AUTOTRACE报告 . 这是默认的.
SET AUTOTRACE ON EXPLAIN
|
仅仅显示优化器执行计划的 AUTOTRACE 报告
SET AUTOTRACE ON STATISTICS
|
仅仅显示 SQL语句执行的统计结果的 AUTOTRACE 报告
SET AUTOTRACE ON
|
包括上面两项内容的 AUTOTRACE报告
SET AUTOTRACE TRACEONLY
|
与 SET AUTOTRACE ON类似 ,所有的统计和数据都在，但不可以打印
其实，平时我们应该较多的就是 SET AUTOTRACE ON 、SET AUTOTRACE OFF,谁会在乎多看一点AUTOTRACE报告呢！ J ** **
** 安装 AUTOTRACE报告（Setups Required for the AUTOTRACE Report） **
要用这个特性，用户必须被赋予 PLUSTRACE角色，而PLUSTRACE角色需要DBA来赋予。另外，该用户必须创建PLAN_TABLE表。
I. 首先创建 PLUSTRACE角色并且赋给DBA：
CONNECT sys/sys’s password AS SYSDBA
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
II. 赋权限给用户
CONNECT / AS SYSDBA
GRANT PLUSTRACE TO USER（预赋权的用户名）;
这样，就可以在该用户下设置 AUTOTRACE报告的显示与否了。
** AUTOTRACE报告中涉及到的两个方面的内容 **
I． SQL语句的执行计划
执行计划就是 SQL优化器执行语句的查询执行顺序，每一行的执行计划都会有个行号，这个行号是连续的
II． SQL语句的数据库统计
数据库统计是服务器记录的执行当前的这条语句所需要的系统资源等，主要包括下表的内容
** 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"
（注：当前是 sysdba用户）
（注：创建 PLUSTRACE角色并且赋给DBA）
SQL> start $ORACLE_HOME/sqlplus/admin/plustrce.sql
（注： start 等价于@）
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"
(注：为了看的清楚些，建立一个测试的表test，然后察看AUTOTRACE报告)
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
上面我们就可以看到，当你设置了 set AUTOTRACE on后，执行相应的DML语句，就会有相应的AUTOTRACE报告出现了。当然，我们的这种设置是session级别的。这样，我们就可以根据AUTOTRACE报告对我们执行的DML语句进行分析和调优了！
参考：
oracle的官方文档(otn.oracle.com)