sqlplus autotrace 功能

先看一下帮助

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

总体来说有3个开关，OFF,ON,TRACE

OFF即关闭trace,ON打开trace，TRACE[ONLY]仅显示trace信息，不显示查询结果。

[oracle@CESTOS5 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 25 20:50:08 2010

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

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

SQL> set autotrace on
SQL> select * from wang.t;

ID
--------------------
1

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |     8 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     4 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        408  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

此时显示出查询结果，Execution Plan，及Statistics

SQL> set autotrace on stat;
SQL> select * from wang.t;

ID
--------------------
1

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        408  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

此时加了stat参数只显示查询结果和Statistics

SQL> set autotrace on exp;
SQL> select * from wang.t;

ID
--------------------
1

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |     8 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     4 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

只显示结果和Execution Plan。

SQL> set autotrace trace;
SQL> select * from wang.t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |     8 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     4 |     8 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        408  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

不显示查询结果。

SQL> set autotrace off;
SQL> select * from wang.t;

ID
--------------------
1

关闭了autotrace。