René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

set autotrace in SQL*Plus

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
Setting autotrace allows to display some statistics and/or an query execution plan for DML statements.
set autotrace on:Shows the execution plan as well as statistics of the statement.
set autotrace on explain:Displays the execution plan only.
set autotrace on statistics:Displays the statistics only.
set autotrace traceonly:Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off:Disables all autotrace
If autotrace is enabled with statistics, then the following statistics are displayed: Additionally, there is rows processed which is not found in v$statname.

Prerequisites

The explain plan feature of autotrace requires a plan_table which can be created with $ORACLE_HOME/rdbms/admin/utlxplan.sql
The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.
An Oracle installation comes with $ORACLE_HOME/sqlplus/admin/plustrce.sql which installs the role plustrace. plustrace is granted those select rights. If now plustrace is granted to a user, he will then be able to turn autotrace on. Alternatively, plustrace can be granted to public.

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.

If flagger is set to something different than off, it's not possible to issue a set autotrace.. in SQL*Plus, it will throw an SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.. In this case, flagger must be turned off: 
set flagger off

Storing settings across sessions

Settings (such as autotrace) can be stored accross sessions with the glogin.sql and/or login.sql file.

Links

See also On comparing multiple SQL statements' statistics.