--=======================
-- 启用 AUTOTRACE功能
--=======================
*Plus工具,用于跟踪SQL的执行计划,收集执行时所耗用资源的统计信息,是SQL优化工具之一,下面给出启用
AUTOTRACE 功能步骤。
    
一、创建基础表
/rdbms/admin/utlxplan脚本来创建plan_table
    
> conn system/redhat  --使用system帐户登陆
.
> start $ORACLE_HOME/rdbms/admin/utlxplan --执行utlxplan脚本
Table created.
        
> 
create public synonym plan_tablefor plan_table; 
--为表plan_table创建公共同义词
Synonym created.
> 
grant all on plan_tableto
public;  --将同义词表plan_table授予给所有用户
Grant succeeded.
二、创建角色
/sqlplus/admin/plustrce.sql脚本
    
> conn 
/ as sysdba   --使用sysdba帐户登陆
.
    
> start $ORACLE_HOME/sqlplus/admin/plustrce.sql  --执行创建角色的脚本
>
> 
drop role plustrace;
drop 
role
*
:
-01919:role
'PLUSTRACE' doesnot
> 
create role plustrace;
Role created.
>
> 
grant select on v_$sesstatto plustrace;
Grant succeeded.
> 
grant select on v_$statnameto plustrace;
Grant succeeded.
> 
grant select on v_$mystatto plustrace;
Grant succeeded.
> 
grant plustrace to dba 
with admin option;
Grant succeeded.
        
三、角色的授予
public,
    则该数据库内所有的用户都将拥有plustrace角色的权限。也可以单独授予给某个组和用户
    
        授予给所有用户
> 
grant plustrace to 
public;
        授予给单独用户
> 
grant plustrace to scott;
            
    完成上述设置之后即可使用autotrace 功能
四、AUTOTRACE的几个选项
set
        
> 
set
: 
SET AUTOT[RACE] {OFF|
ON | TRACE[ONLY]}
        
        set autotrace off :缺省值，将不生成autotrace 报告
        set autotrace on :包含执行计划和统计信息
        set autotrace traceonly :等同于set autotrace on，但不显示查询输出的结果
        set autotrace on explain :只显示优化器执行路径报告
        set autotrace on statistics :只显示执行统计信息
        
        scott@ORCL> set autotrace on;
        scott@ORCL> select * from emp where ename='SCOTT';
             EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
        ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
              7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 3956160932
        --------------------------------------------------------------------------
        | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
        --------------------------------------------------------------------------
        |   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
        |*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
        --------------------------------------------------------------------------
        Predicate Information (identified by operation id):
        ---------------------------------------------------
           1 - filter("ENAME"='SCOTT')
        Statistics
        ----------------------------------------------------------
                  0  recursive calls
                  0  db block gets
                  8  consistent gets
                  0  physical reads
                  0  redo size
                824  bytes sent via SQL*Net to client
                385  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
                  1  rows processed