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常用列解释