Oracle SQL: the Essential Reference

8.3. SQL*Plus Tuning Aids

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.

8.3.1. SET AUTOTRACE

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.

8.3.1.1. Prerequisites for using autotrace

Some prerequisites must be met before using SET AUTOTRACE. If execution plans are desired, a plan table must exist ...