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 只显示执行计划,但不显示查询输出
---------------------------------------------------------------------------------
Kind Regards,
HaiTao Niu
JavaScript developer :)
ORACLE Certified Database Administrator - 10g
SUN Certified Applocation Developer - Java 6
Ring Building,No.28,ZhongGuanCun Software Park, No.8 Dong Bei Wang West Road, Haidian District, Beijing P.R.China 100193
E-mail: 1648500@qq.com