ORACLE9i在使用autotrace之前，需要作一些初始的设置，
1.用sys用户运行脚本ultxplan.sql
建立这个表的脚本是：（UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%\rdbms\admin）ultxplan.sql。
SQL> connect sys/sys@colm2 as sysdba;
SQL> @C:\oracle\ora92\rdbms\admin\utlxplan.sql;
SQL> create public synonym plan_table for plan_table; --建立同义词
SQL> grant all on plan_table to public;--授权所有用户
2.要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色，这个脚本在目录（UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%\sqlplus\admin）中；
SQL> @C:\oracle\ora92\sqlplus\admin\plustrce.sql;
3.然后将角色plustrace授予需要autotrace的用户；
SQL>grant plustrace to public;
* plustrace角色只是具有以下权限:
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
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: 视图基本对象的权限不足的解决方法
ORA-01039: 视图基本对象的权限不足
Current SQL statement for this session:
EXPLAIN PLAN SET STATEMENT_ID='PLUS561' FOR select table_name from user_tables
I think this is because the user doesn't have access to base tables for USER_TABLES view which belongs to SYS user.
DBA role will do it, "SELECT ANY TABLE" (in 8i & 9i) , and "SELECT ANY DICTIONARY"(in 9i & 10g) system privileges should also do it. Try one of the following 3 ways and run your autotrace again:-
1. 8i & 9i:-
grant select any table to USER123;
2. 9i and 10g:-
grant select any dictionary to USER123;
3. in 8i and 9i, you can also grant accees to the base tables explicitly ( or create a role to hold the grants ) :
grant select on OBJ$ to USER123;
grant select on USER$ to USER123;
grant select on SEG$ to USER123;
grant select on TS$ to USER123;
grant select on TAB$ to USER123;
2.在SQPPLUS中得到更新成功或者插入成功的记录数
SQL>set feedback 1；
3.在SQPPLUS中得到语句总执行的时间
SQL> set timing on;
4.使用sys进行autotrace的话统计信息statistic都会为0
SQL> select count(*) from dba_objects;
COUNT(*)
----------
31820
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 VIEW OF 'DBA_OBJECTS'
3 2 UNION-ALL
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'USER$'
8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
11 3 NESTED LOOPS
12 11 TABLE ACCESS (FULL) OF 'USER$'
13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5.AUTOTRACE的几个常用选项
set autotrace off ---------------- 不生成autotrace 报告，这是缺省模式
set autotrace on explain ------ autotrace只显示优化器执行路径报告
set autotrace on statistics -- 只显示执行统计信息
set autotrace on ----------------- 包含执行计划和统计信息
set autotrace traceonly ------ 同set autotrace on，但是不显示查询输出
(1). set autotrace on explain; --只显示执行计划
SQL> set autotrace on explain;
SQL> select count(*) from dba_objects;
COUNT(*)
----------
31820
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 VIEW OF 'DBA_OBJECTS'
3 2 UNION-ALL
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'USER$'
8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
11 3 NESTED LOOPS
12 11 TABLE ACCESS (FULL) OF 'USER$'
13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
(2). set autotrace on statistics;--只显示统计信息
SQL> set autotrace on statistics;
SQL> select count(*) from dba_objects;
COUNT(*)
----------
31820
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25754 consistent gets
0 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 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;--同set autotrace on 只是不显示查询输出
SQL> set autotrace traceonly;
SQL> select count(*) from dba_objects;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 VIEW OF 'DBA_OBJECTS'
3 2 UNION-ALL
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'USER$'
8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
11 3 NESTED LOOPS
12 11 TABLE ACCESS (FULL) OF 'USER$'
13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25754 consistent gets
0 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
(4).set autotrace traceonly explain;--比较实用的选项，只显示执行计划，但是与set autotrace on explain;相比不会执行语句，对于仅仅查看大表的Explain Plan非常管用。
SQL> set autotrace traceonly explain;
SQL> select * from dba_objects;
已用时间: 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW OF 'DBA_OBJECTS'
2 1 UNION-ALL
3 2 FILTER
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
5 4 NESTED LOOPS
6 5 TABLE ACCESS (FULL) OF 'USER$'
7 5 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
9 8 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
10 2 TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'
11 10 NESTED LOOPS
12 11 TABLE ACCESS (FULL) OF 'USER$'
13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
6.Statistics参数的含义
recursive calls = basically sql performed on behalf of your sql.
So, if we had to PARSE the query for example, we might have
had to run some other queries to get data dictionary info.
that would be recursive calls.
db block gets = blocks gotten in "current" mode. That is,
blocks gotten as they exist right now. You'll see these
for full table scans (segment headers areread in current mode)
and modification statements (we modify the block as it
exists "right now")
consistent gets = blocks gotten in consistent read mode.
This is the mode we
read blocks in with a select for example. Also,
when you do a searched UPDATE/DELETE, we read the blocks in
consistent read mode and then get the block in current mode
to actually do the modification. A select for update will do
this as well.
physical reads = self explanatory, physical IO
redo size = self explanatory -- amount of redo generated
sorts (memory)/(disk) -- sorts done.
补充一下在10g中的用法:
在10g中带有plan_table这张表，但是将名字改为了plan_table$
只需$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色
grant plustrace to public
