如何启用sqlplus的AutoTrace功能

通过以下方法可以把Autotrace的权限授予Everyone，

如果你需要限制Autotrace权限，可以把对public的授权改为对特定user的授权。

D:\oracle\ora92>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 3 15:16:03 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys as sysdba

请输入口令:

已连接。

SQL> @?\rdbms\admin\utlxplan

表已创建。

SQL> create public synonym plan_table for plan_table;

同义词已创建。

SQL> grant all on plan_table to public ;

授权成功。

SQL> @?\sqlplus\admin\plustrce

SQL>

SQL> drop role plustrace;

drop role plustrace

*

ERROR 位于第 1 行:

ORA-01919: 角色'PLUSTRACE'不存在

SQL> create role plustrace;

角色已创建

SQL> grant select on v_$sesstat to plustrace;

SQL> grant select on v_$statname to plustrace;

SQL> grant select on v_$session to plustrace;

SQL> grant plustrace to dba with admin option;

SQL> set echo off

DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public

这样所有用户都将拥有plustrace角色的权限.

SQL> grant plustrace to public ;

然后我们就可以使用AutoTrace的功能了.

SQL> connect eqsp/eqsp

SQL> set autotrace on

SQL> set timing on

关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告

SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

SQL> set autotrace traceonly

SQL> select table_name from user_tables;

已选择98行。

已用时间: 00: 00: 00.04

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 NESTED LOOPS (OUTER)

3 2 NESTED LOOPS (OUTER)

4 3 NESTED LOOPS (OUTER)

5 4 NESTED LOOPS (OUTER)

6 5 NESTED LOOPS

7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

9 6 TABLE ACCESS (CLUSTER) OF 'TAB$'

10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)

11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)

13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)

14 3 TABLE ACCESS (CLUSTER) OF 'USER$'

15 14 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

16 2 TABLE ACCESS (CLUSTER) OF 'SEG$'

17 16 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)

18 1 TABLE ACCESS (CLUSTER) OF 'TS$'

19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

Statistics

0 recursive calls

0 db block gets

1389 consistent gets

0 physical reads

0 redo size

2528 bytes sent via SQL*Net to client

569 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

98 rows processed

-The End-