SP2-0618 0611錯誤解決辦法

hai503發表於2016-10-05

    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/12707874/viewspace-2125855/，如需轉載，請註明出處，否則將追究法律責任。

相關文章