Displaying an Execution Plan for a Query

Performance Tuning

SQL> set autotrace on;
SQL> select * from dual;

D

X

Execution Plan

Plan hash value: 272002086

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

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

Statistics

     24  recursive calls
      0  db block gets
      6  consistent gets
      3  physical reads
      0  redo size
    418  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)

1 rows processed

How It Works
There are several options to choose from when using AUTOTRACE, and the basic factors are as follows:

  1. Do you want to execute the query?
  2. Do you want to see the execution plan for the query?
  3. Do you want to see the execution statistics for the query?

The most common use for AUTOTRACE is to get the execution plan for the query, without running the query. By doing this, you can quickly see whether you have a reasonable execution plan, and can do this without having to execute the query.

AUTOTRACE Option
Execution Plan
Statistics
Query Executed
AUTOT[RACE] OFF
NO
No
Yes
AUTOT[RACE] ON
Yes
Yes
Yes
AUTOT[RACE] ON EXP[LAIN]
Yes
No
Yes
AUTOT[RACE] ON STAT[ISTICS]
No
Yes
Yes
AUTOT[RACE] TRACE[ONLY]
Yes
Yes
Yes (but output is suppressed
AUTOT[RACE] TRACE[ONLY] EXP[LAIN]
Yes
No
No

Once you are done using AUTOTRACE for a given session and want to turn it off and run other queries without using AUTOTRACE, run the following command from within your SQL Plus session:
SQL> set autot off

The default for each SQL Plus session is AUTOTRACE OFF, but if you want to check to see what your current AUTOTRACE setting is for a given session, you can do that by executing the following command:
SQL> show autot
autotrace OFF

DBMS_XPLAN.DISPLAY

SQL> explain plan for select * from dual;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 272002086


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

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

8 rows selected.