The main difference between the AUTOTRACE and EXPLAIN PLAN commands in Oracle is that AUTOTRACE actually executes the query (in the way TRACE does) and automatically queries the plan table, whereas EXPLAIN PLAN does neither. The AUTOTRACE command generates similar information, as shown in the next listing. To use AUTOTRACE, the user must possess the PLUSTRACE role (by running plustrce.sql, which is usually located in the ORACLE_HOME/sqlplus/admin directory).
The output is as follows:
The AUTOTRACE option provides an EXPLAIN PLAN and statistics for a query. AUTOTRACE provides many of the TRACE and TKPROF statistics such as disk reads (physical reads) and total reads (consistent reads + db block gets).
If the error “Unable to verify plan table format or existence” occurs when enabling AUTOTRACE, you must create a plan table using the utlxplan.sql script.
AUTOTRACE may fail when querying system views because the user may not have permission to view underlying objects.
Table 1. shows other AUTOTRACE options.
|SET AUTOT ON
|Turn on AUTOTRACE (short way)
|SET AUTOT OFF
|Turns off AUTOTRACE (short way)
|SET AUTOT ON EXP
|Shows only the EXPLAIN PLAN
|SET AUTOTRACE ON STAT
|Shows only the statistics
|SET AUTOT TRACE
|Does not show the output of the query