oracle sqlplus生成trace,如何启用sqlplus的AutoTrace功能

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> 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;

授权成功。

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

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

SQL> grant plustrace to public ;

授权成功。

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

C:\Documents and Settings\Paul Yi>sqlplus paul/paul

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 6月 28 16:14:03 2007

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

连接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> set autotrace on

SQL> set timing on

SQL> select * from t_receive;

ID TELENO LOG_DATA U_

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

1 1234 2007-06-28 15:33:56 注

1 1234 2007-06-28 15:34:19 取

1 1235 2007-06-28 15:34:43 注

1 1235 2007-06-28 15:34:53 取

1 1235 2007-06-28 15:35:03 变

已用时间: 00: 00: 00.00

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'T_RECEIVE'

Statistics

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

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

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

5 rows processed

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

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

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

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

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

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

补充两个AUTOTRACE

set autotrace traceonly exp

set autotrace traceonly exp stat