Generating EXECUTION PLAN of a SQL query using AUTOTRACE

Suppose you are tuning a sql query and want to quickly generate an execution plan from SQL Plus for a query.

Here is the solution:

From SQL Plus, you can use the AUTOTRACE feature to quickly generate the execution plan for a query. This SQL Plus utility is very handy at getting the execution plan, along with getting statistics for the query’s execution plan. In the most basic form, to enable AUTOTRACE within your session, execute following command within SQL Plus:

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?

As you can see from the below table, you can achieve all the above mentioned 3 aspects. The portions of the words in brackets are optional.

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.

Run a simple query to test this:

If you want to see only execution statistics. use statistics with autotrace command.
run a simple query to test this:

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

SQL> set autotrace 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 autotrace
autotrace OFF