Set autotrace on | Shows the optimizer execution plan as well as statistics of the statement |
Set autotrace on explain | Shows only the optimizer execution plan |
Set autotrace on statistics | Shows only the statistics |
Set autotrace traceonly | Like SET AUTOTRACE ON, but doesn’t print a query result. |
Set autotrace off | Disables all autotrace |
Combinations:
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
If autotrace is enabled with statistics, then the following statistics are displayed:
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
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_$sestat, v_$statname, 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 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 across sessions with the glogin.sql and/or login.sql file.
