How is AUTOTRACE Different from EXPLAIN PLAN?

By: Richard Niemiec


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

 

0334_001

 

 

 

The output is as follows:

 

0334_002

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
TABLE 1. AUTOTRACE Options

Comments

Leave a Reply