Search notes:

SQL*Plus: SET AUTOTRACE

When the corresponding setting of autotrace are enabled, SQL*Plus displays an SQL Statement's execution plan and some execution statistitics after executing the statement in SQL*Plus.

Settings

The following settings can be enabled with autotrace.
set autotrace off The default: nothing is reported
set autotrace on explain Only display the optimizer execution path.
set autotrace on statistics Only SQL statement execution statistics.
set autotrace on Display both the optimizer execution path and the SQL statement execution statistics.
set autotrace traceonly Like set autotrace on but do not print SQL statement results.
Compare with the AUTOTRACE system variable.

Reported statistics

If autotrace is enabled with statistics, it reports the following ones

Prerequisites

In order to use the autotrace feature, a user must have
A plan table can be created with the $ORACLE_HOME/rdbms/admin/catplan.sql script.
The PLUSTRACE role is created with the $ORACLE_HOME/sqlplus/admin/plustrce.sql script. This role grants the necessary privileges to access the dynamic performance views 
$ sqlplus / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to rene;

No autotrace with enabled FIPS flagging

With FIPS flagging (i.e. setting flagger to a value different from off), autotrace is not available and causes

See also

set timing …
SQL*Plus

Index