Oracle 9i 打开autotrace on 查看执行计划

 

1.创建表，通过utlxplan脚本

 

SQL> @?/rdbms/admin/utlxplan

Table created.

 

2. 创建同义词为了多个用户可以共享一个plan_table 并授权给public

 

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant all on plan_table to public;

Grant succeeded.

 

3.创建plustrace 角色

 

SQL> @?/sqlplus/admin/plustrce.sql   执行这个脚本就可以了。

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> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off

 

4、将角色权限授予public

 

sql>grant plustrace to public;

 

完成以上几步就可以使用autotrace 功能

 

set autotrace off  默认为不打开

 

set autotrace on  查看执行计划和查询信息

 

set autotrace traceonly 只显示执行计划，但不显示查询输出