SCOTT 用戶開啟autotrace 功能

想用SCOTT用戶做執行計劃的實驗，發現無法使用；按理說任何可以使用sqlplus 的用戶都可以在session下啟用autotrace 功能，不過有的需要做下設置；

sys 用戶

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select username,account_status from dba_users where username='SCOTT';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT                          EXPIRED & LOCKED

SQL> alter user scott account  unlock identified by tiger;

User altered.


SQL>
SQL> select username,account_status from dba_users where username='SCOTT';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT                          OPEN

SCOTT 用戶登錄

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

解決過程

1、以SYS用戶登錄，運行utlxplan.sql. 建立plan 表；

SQL> @?/rdbms/admin/utlxplan.sql           
Table created.

2、以sys用戶登錄，運行plustrce.sql，數據庫啟用autotrace功能的安裝腳本，主要是創建plustrace角色，并且授權 ，擁有plustrace最主要的目的是為了訪問下面幾個VIEW ：v_$sesstat;v_$statname；v_$mystat;

SQL> @?/sqlplus/admin/plustrce.sql
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

SQL> grant plustrace to scott;

Grant succeeded.

3、以SCOTT 登錄

SQL> set autotrace on
SQL>
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
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 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
         10  consistent gets
          4  physical reads
          0  redo size
        802  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>

 設置Autotrace的命令

PS：SET AUTOTRACE ON, set timing on, alter session set time_statistics=true;

Autotrace執行計劃的各列的涵義

 Autotrace Statistics常用列解釋