ORACLE Set Autotrace

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
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

Set Autotrace allows you to display some statistics and/or query plans for DML statements.

Set Autotrace on: Displays statistical information and execution plans for statements.

Set autotrace on explain: Shows only execution plans.

Set Autotrace on statistics: displays only statistics.

Set Autotrace traceonly: Displays execution plans and statistics, but prints query results.

Set Autotrace off: Closing Autotrace

If you open autotrace with the statistics option, 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)
Predecessor condition

Autotrace's execution plan features need to create a plan_table table that can be scripted

$ORACLE _home/rdbms/admin/utlxplan.sql Creation

Statistical features require users to have query permissions on V_$sesstat, V_$statname, and v_$session tables.

The $oracle_home/sqlplus/admin/plustrce.sql that came with ORACLE installation installs the Plustrace role, and plustrace roles are granted these query permissions. If the Plustrace role is granted to the user, then he or she can have permission to open the Autotrace. Alternatively, you can grant the plustrace role to public.