set autotrace on explain异常：SP2-0613: Unable to verify PLAN_TABLE format or existence

最新推荐文章于 2024-02-01 16:40:25 发布

原创最新推荐文章于 2024-02-01 16:40:25 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

工作所遇问题同时被 2 个专栏收录

34 篇文章

订阅专栏

database

17 篇文章

订阅专栏

本文介绍了如何在Oracle数据库中设置自动跟踪和定时功能以优化SQL查询性能，包括创建PLAN_TABLE表、授予角色权限等步骤。

SQL> set autotrace on explain
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SQL> connect / as sysdba
Connected.
SQL> @%ORACLE_HOME%/rdbms/admin/utlxplan

Table created.

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant all on plan_table to public;

Grant succeeded.

SQL> @%ORACLE_HOME%/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_$session to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

执行以上操作之后，基本就行了。

SQL> set autotrace on exp
SQL> set timing on
SQL> select count(*) from tab;

  COUNT(*)
----------
      2470

Elapsed: 00:00:02.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (OUTER)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   4    3         INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   5    2       TABLE ACCESS (CLUSTER) OF 'TAB$'
   6    5         INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)