oracle SET AUTOTRACE ON

先用SYS设置权限：

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 19 22:00:45 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
SQL>
SQL> drop role plustrace;

角色已删除。

SQL> create role plustrace;

角色已创建。

SQL>
SQL> grant select on v_$sesstat to plustrace;

授权成功。

SQL> grant select on v_$statname to plustrace;

授权成功。

SQL> grant select on v_$mystat to plustrace;

授权成功。

SQL> grant plustrace to dba with admin option;

授权成功。

SQL>
SQL> set echo off
SQL> grant plustrace to scott;

授权成功。

SQL> 

然后，显示执行计划：

SQL> conn scott/tiger
已连接。
SQL> SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;

JOB         SUM(SAL)
--------- ----------
CLERK           4150
SALESMAN        5600
PRESIDENT       5000
MANAGER         8275
ANALYST         6000

SQL>
SQL> SET AUTOTRACE ON
SQL> SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;

JOB         SUM(SAL)
--------- ----------
CLERK           4150
SALESMAN        5600
PRESIDENT       5000
MANAGER         8275
ANALYST         6000


执行计划
----------------------------------------------------------
Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    60 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     5 |    60 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   168 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        567  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)
          5  rows processed

SQL>

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告 
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息 

SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

注意：

PL SQL 并不支持SET AUTOTRACE，所以会报cannot set autotrace