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博客 ” ，链接：https://blog.itpub.net/29949829/viewspace-1314539/ ，如需转载，请注明出处，否则将追究法律责任。
