SET AUTOTRACE는 Oracle에서 Plan을 확인하는 방법 중 한가지로 SQL*Plus에서 자동으로 실행 계획(Plan)과 통계정보( Statistics )를 확인할 수 있다.

Autotrace Setting	Result
SET AUTOTRACE OFF	default setting으로 Autotrace를 생성하지 않음.
SET AUTOTRACE ON	execution plan과 staticstics를 모두 보여줌.
SET AUTOTRACE ON EXPLAIN	execution plan만 보여줌.
SET AUTOTRACE ON STATISTICS	staticstics만 보여줌.
SET AUTOTRACE TRACEONLY	SET AUTOTRACE ON과 비슷하나, Query 실행 결과를 보여주지 않음.

예제 쿼리

oracle> SELECT * FROM t1;

SET AUTOTRACE ON

        C1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        670  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SET AUTOTRACE ON EXPLAIN

        C1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SET AUTOTRACE ON STATISTICS

        C1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        670  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SET AUTOTRACE TRACEONLY

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        670  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed