Oracle set autotrace 功能理解对比

AUTOTRACE是一个SQL*Plus工具,用于跟踪SQL的执行计划,收集执行时所耗用资源的统计信息,是SQL优化工具之一，是分析SQL的执行计划，执行效率的一个非常简单方便的工具，在绝大多数情况下，也是非常有用的工具。利用AutoTrace工具提供的SQL执行计划和执行状态可以为我们优化SQL的时候提供优化的依据，以及优化效果的明显的对比效果。

Autotrace几个常用选项的说明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出 

OFF模式不用多说了，这里我们举例说明 SET AUTOTRACE ON 和 SET AUTOTRACE TRACEONLY的区别，让大家加深AUTOTRACE的理解

在OFF的情况下执行如下语句，输入下面结果：

SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP Public Relations Representative           4500      10500

然后设置SET AUTOTRACE ON后，同样执行上列SQL语句

SQL> set autotrace on
SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP Public Relations Representative           4500      10500

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
       1700  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         19  rows processed

可以看到和OFF情况下，多了Execution Plan和Statistics信息

然后我们再看下SET AUTOTRACE TRACEONLY情况下，执行同样ＳＱＬ语句的结果：

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
       1700  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         19  rows processed

可以看到没有显示查询结果，只显示出来执行计划和统计信息。