Displaying an Execution Plan for a Query

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.