Monday, March 2, 2009

How to set autotrace in SQL*Plus...

Setting autotrace allows displaying some statistics and/or a query execution plan for DML statements.

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.