ORACLE9i在使用autotrace之前，需要作一些初始设置:
1.用sys用户运行脚本utlxplan.sql创建PLAN_TABLE表
脚本目录：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%/rdbms/admin）utlxplan.sql。
SQL> connect sys/sys@sys as sysdba;
SQL> @J:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/utlxplan.sql;
SQL> create public synonym plan_table for plan_table; --建立同义词
SQL> grant all on plan_table to public;--授权所有用户
2.用sys用户运行脚本plustrce.sql建立一个角色plustrace
脚本目录：（UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%/sqlplus/admin）plustrce.sql；
SQL> @J:/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql;
3.将角色plustrace授予需要autotrace的用户；
SQL>grant plustrace to public;
plustrce.sql脚本如下
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant select on v_$session to plustrace;
grant plustrace to dba with admin option;
4.经过以上步骤的设置，就可以在sql*plus中使用autotrace了，使用非常简单，只要在执行语句之前，执行这样一条命令：
SQL>set autotrace on
即可。
*autotrace功能只能在SQL*PLUS里使用
补充：
1.ORA-01039: 视图基本对象的权限不足的解决方法
1). 8i & 9i中的解决办法:-
grant select any table to scott;
2). 9i and 10g中的解决办法:-
grant select any dictionary to scott;
3). 8i and 9i中,也可显式地把基本表的select权限赋给scott:
grant select on OBJ$ to scott;
grant select on USER$ to scott;
grant select on SEG$ to scott;
grant select on TS$ to scott;
grant select on TAB$ to scott;
2.在SQPPLUS中得到更新成功或者插入成功的记录数
SQL>set feedback 1；
已创建 1 行。
3.在SQPPLUS中得到语句总执行的时间
SQL> set timing on;
已用时间: 00: 00: 00.10
4.AUTOTRACE的几个常用选项
(1) set autotrace on explain; --只显示执行计划
SQL> set autotrace on explain;
SQL> select count(*) from dept;
COUNT(*)
----------
4
执行计划
----------------------------------------------------------
Plan hash value: 2946246213
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| DEPTNO_DNAME_IND | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
(2) set autotrace traceonly; --同set autotrace on 只是不显示查询输出
SQL> set autotrace traceonly;
SQL> set feedback
SQL> set timing off;
SQL> select count(*) from dept;
执行计划
----------------------------------------------------------
Plan hash value: 2946246213
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| DEPTNO_DNAME_IND | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
(3) set autotrace traceonly explain;
--比较实用的选项，只显示执行计划，但是与set autotrace on explain;相比不会执行语句，对于仅仅查看大表的Explain Plan非常管用。
SQL> set autotrace traceonly explain;
SQL> select count(*) from dept;
执行计划
----------------------------------------------------------
Plan hash value: 2946246213
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| DEPTNO_DNAME_IND | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
(4) set autotrace on statistics;--只显示统计信息
SQL> set autotrace on statistics;
SQL> select count(*) from dept;
COUNT(*)
----------
4
统计信息
----------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed