SP2-0618 0611错误解决办法

    SQL调优占到了全部调优工作的90%以上，所以很多大牛做调优都是在SQL上进行优化。在生产库上，可能嵌套两层、三层甚至多层循环，进行不同的表连接，而执行计划恰好能够反映Oracle执行该SQL语句的流程，所以查看执行计划是很有帮助的。

    但是对于初次查看执行计划的用户需要一个plustrace角色，否则就会报下面的错：

scott@PROD>set autotrace traceonly

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

 

SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色

解决办法如下：

一、跑脚本plustrce.sql

脚本内容如下，很简单，就是创建plustrace角色：

[oracle@RH55_11g ~]$cat $ORACLE_HOME/sqlplus/admin/plustrce.sql set echo on   drop role plustrace; create role plustrace;   grant select on v_$sesstat to plustrace; grant select on v_$statname to plustrace; grant select on v_$mystat to plustrace; grant plustrace to dba with admin option;   set echo off

先跑脚本来创建角色：

sys@PROD>@?/sqlplus/admin/plustrce.sql sys@PROD> set echo on sys@PROD> sys@PROD>drop role plustrace;   Role dropped.   sys@PROD>create role plustrace;   Role created.   sys@PROD> sys@PROD>grant select on v_$sesstat to plustrace;   Grant succeeded.   sys@PROD>grant select on v_$statname to plustrace;   Grant succeeded.   sys@PROD>grant select on v_$mystat to plustrace;   Grant succeeded.   sys@PROD>grant plustrace to dba with admin option;   Grant succeeded.   sys@PROD> sys@PROD>set echo off

 

二、授予该用户plustrace角色

sys@PROD>grant plustrace to scott;   Grant succeeded.

三、在scott用户下打开跟踪，查看执行计划

sys@PROD>conn scott/tiger Connected. scott@PROD>set autotrace traceonly scott@PROD>select * from emp;   14 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932   -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------     Statistics ----------------------------------------------------------          37  recursive calls           0  db block gets          71  consistent gets           6  physical reads           0  redo size        1630  bytes sent via SQL*Net to client         523  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           6  sorts (memory)           0  sorts (disk)          14  rows processed   scott@PROD>

 

    还有另一种查看执行计划，可以不需要plustrace角色，通过调用dbms_xplan.display来查看执行计划：

scott@PROD>conn / as sysdba Connected. sys@PROD>drop role plustrace;   Role dropped.   Elapsed: 00:00:00.12 sys@PROD>conn scott/tiger Connected. scott@PROD>explain plan for   2  select * from dept;   Explained.   Elapsed: 00:00:00.01 scott@PROD>select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3383998547   -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------   8 rows selected.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29949829/viewspace-1314539/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29949829/viewspace-1314539/