SQL*Plus has two features that can be useful when testing and tuning SQL statements. The SET AUTOTRACE command can be used to place SQL*Plus into a mode in which it displays the execution plan and statistics for each SQL statement that is executed. SQL*Plus also implements commands that allow tracking of the elapsed time necessary to execute one or more SQL statements.
The SET AUTOTRACE command causes SQL*Plus to display the execution plan and the following statistics for each statement that is executed:
The number of recursive SQL statements executed
The number of blocks retrieved in current mode (db block gets)
The number of blocks retrieved in read consistent mode (consistent gets)
The number of physical reads from disk
The amount of redo used
The number of bytes transmitted via Net8 to the client executing the statement
The number of bytes received via Net8 from the client
The number of Net8 messages (roundtrips) exchanged between the client and server
The number of in-memory sorts
The number of sorts performed using disk
The total number of rows processed
Some of these statistics differ from what is provided with SQL Trace; others are the same. Unlike the statistics generated from SQL Trace, statistics from SET AUTOTRACE are not broken out into parse, execution, and fetch phases.
Some prerequisites must be met before using SET AUTOTRACE. If execution plans are desired, a plan table must exist ...