ORACLE auto trace (explain plan) 功能打开

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 只显示执行计划，但不显示查询输出