想用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的命令
序号 | 命令 | 解释 |
1 | SET AUTOTRACE OFF | 此为默认值，即关闭Autotrace |
2 | SET AUTOTRACE ON | 产生结果集和解释计划并列出统计 |
3 | SET AUTOTRACE ON EXPLAIN | 显示结果集和解释计划不显示统计 |
4 | SET AUTOTRACE TRACEONLY | 显示解释计划和统计，尽管执行该语句但您将看不到结果集 |
5 | SET AUTOTRACE TRACEONLY STATISTICS | 只显示统计 |
PS：SET AUTOTRACE ON, set timing on, alter session set time_statistics=true;
Autotrace执行计划的各列的涵义
序号 | 列名 | 解释 |
1 | ID_PLUS_EXP | 每一步骤的行号 |
2 | PARENT_ID_PLUS_EXP | 每一步的Parent的级别号 |
3 | PLAN_PLUS_EXP | 实际的每步 |
4 | OBJECT_NODE_PLUS_EXP | Dblink或并行查询时才会用到 |
Autotrace Statistics常用列解释
序号 | 列名 | 解释 |
1 | db block gets | 从buffer cache中读取的block的数量 |
2 | consistent gets | 从buffer cache中读取的undo数据的block的数量 |
3 | physical reads | 从磁盘读取的block的数量 |
4 | redo size | DML生成的redo的大小 |
5 | sorts (memory) | 在内存执行的排序量 |
6 | sorts (disk) | 在磁盘上执行的排序量 |
