Autotrace is an sql*plus feature that automatically tracks the generation of an execution plan for SQL statements and provides statistics about the processing of the statement. Sql*plus Autotrace can be used instead of SQL Trace, the benefit of autotrace is that you do not have to format the trace file, and it automatically displays the execution plan for the SQL statement. However, Autotrace parses and executes statements, while explain plan parses only statements. Using autotrace does not produce trace files.
1 Enable Autotrace
1.1 with SYS user, run Utlxplan.sql
Sqlplus/as SYSDBA
sql> @ $ORACLE _home/rdbms/admin/utlxplan.sql
1.2 Creating Plustrace roles by executing plustrce.sql scripts
sql> @ $ORACLE _home/sqlplus/admin/plustrce.sql
1.3 Licensing
Grant the Plustrace role to the current user (a user without DBA role), or grant plustrace to public;
2 Setting the Autotrace command
Serial number |
Command |
Explain |
1 |
SET AUTOTRACE OFF |
This is the default value, which is to turn off Autotrace |
2 |
SET AUTOTRACE on |
Generating result sets and interpreting plans and listing statistics |
3 |
SET AUTOTRACE on EXPLAIN |
Show result set and interpret schedule do not show statistics |
4 |
SET AUTOTRACE traceonly |
Show interpreting schedules and statistics, although executing the statement but you will not see the result set |
5 |
SET AUTOTRACE traceonly STATISTICS |
Show Statistics only |
Eg:set AUTOTRACE on, set timing on, alter session SET Time_statistics=true;
The following is for reference only
3 Autotrace The meaning of the columns of the implementation plan
Serial number |
Column Name |
Explain |
1 |
Id_plus_exp |
Line number of each step |
2 |
Parent_id_plus_exp |
The level number of the parent for each step |
3 |
Plan_plus_exp |
Every step of the actual |
4 |
Object_node_plus_exp |
Dblink or parallel queries are used |
4 Autotrace Statistics Common column interpretation
Serial number |
Column Name |
Explain |
1 |
DB Block gets |
The number of blocks read from the buffer cache |
2 |
Consistent gets |
Number of block of undo data read from buffer cache |
3 |
Physical Reads |
The number of blocks read from disk |
4 |
Redo Size |
The size of the redo generated by DML |
5 |
Sorts (memory) |
The sort amount that is executed in memory |
6 |
Sorts (disk) |
The amount of sorting performed on the disk |