Autotrace utility

The autotrace is an SQL*Plus utility used in tuning SQL statements. The autotrace utility offers SQL statement tracing. The PLUSTRACE role is required by the database user enabling trace. The PLUSTRACE role is created by executing the $ORACLE_HOME/sqlplus/admin/plustrce.sql script as SYSDBA.

Tracing is enabled/disabled using the set autotrace command in SQL*Plus, shown as follows:

SQL> set autotrace on
SQL> set autotrace off

The autotrace utility does not generate any trace files; instead the trace output is displayed on the screen. The following are the autotrace supported options:

• on: This enables all options

• off: This disables tracing

• on explain: This displays returned rows and the execution plan

• on statistics: This displays returned rows and statistics

• trace explain: This displays the execution plan without actually executing it

• traceonly: This displays the execution plan and statistics without returning the rows

The following is an example usage of the autotrace...