在SQL*PLUS中应用AUTOTRACE REPORT

 

在SQL*PLUS中应用AUTOTRACE REPORT

 

作者：刘颖博

时间：2004-1-12

mail：liuyingbo@126.com，请指正

 

转载请注明出处及作者

 

 

在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)