SQL> set autotrace traceonly

 SQL> select * from hr.jobs;

 19 rows selected.

 Execution Plan

 ----------------------------------------------------------

 Plan hash value: 944056911

 --------------------------------------------------------------------------

 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

 --------------------------------------------------------------------------

 |   0 | SELECT STATEMENT  |      |    19 |   627 |     3   (0)| 00:00:01 |

 |   1 |  TABLE ACCESS FULL| JOBS |    19 |   627 |     3   (0)| 00:00:01 |

 --------------------------------------------------------------------------

 Statistics

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

           9  consistent gets

           0  physical reads

           0  redo size

        1452  bytes sent via SQL*Net to client

         396  bytes received via SQL*Net from client

           3  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

          19  rows processed

 SQL> 

 SQL> conn /as sysdba                        --用系统账户登录

 Connected.

 SQL> start $ORACLE_HOME/rdbms/admin/utlxplan --执行utlxplan脚本

 Table created.

 SQL> create public synonym plan_table for plan_table;--为表plan_table创建公共同义词    

 Synonym created.

 --如果需要限制Autotrace权限，可以把对public的授权改为对特定user的授权。

 SQL> grant all on plan_table to public;--将同义词表plan_table授予给所有用户

 Grant succeeded.

 SQL>

 SQL> start $ORACLE_HOME/sqlplus/admin/plustrce.sql  --执行创建角色的脚本

 SQL>

 --以下是脚本执行过程：首先 删除PLUSTRACE角色，重建PLUSTRACE角色，赋予权限

 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>         

 --授予给单独用户

 SQL> grant plustrace to hr;

 Grant succeeded.

 --授予给所有用户

 SQL> grant plustrace to public;

 Grant succeeded.

 SQL>

 --在sql提示符下输入set autot后将会给出设置autotrace的提示，如下：

 SQL> set autot

 Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

 set autotrace off :缺省值，将不生成autotrace 报告

 set autotrace on :包含执行计划和统计信息

 set autotrace traceonly :等同于set autotrace on，但不显示查询输出的结果

 set autotrace on explain :只显示优化器执行路径报告

 set autotrace on statistics :只显示执行统计信息        

 SQL> conn hr/hr123

 Connected.

 SQL> set autotrace traceonly

 SQL> select * from jobs;

 19 rows selected.

 Execution Plan

 ----------------------------------------------------------

 Plan hash value: 944056911

 --------------------------------------------------------------------------

 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

 --------------------------------------------------------------------------

 |   0 | SELECT STATEMENT  |      |    19 |   627 |     3   (0)| 00:00:01 |

 |   1 |  TABLE ACCESS FULL| JOBS |    19 |   627 |     3   (0)| 00:00:01 |

 --------------------------------------------------------------------------

 Statistics

 ----------------------------------------------------------

           1  recursive calls

           0  db block gets

           9  consistent gets

           0  physical reads

           0  redo size

        1452  bytes sent via SQL*Net to client

         396  bytes received via SQL*Net from client

           3  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

          19  rows processed

 SQL>