How is AUTOTRACE Different from EXPLAIN PLAN?

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).

TIP

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.

CAUTION

AUTOTRACE may fail when querying system views because the user may not have permission to view underlying objects.

 

Table 1. shows other AUTOTRACE options.

Option	Function
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