Autotrace

Autotrace facility enables you to produce the EXPLAIN PLAN and Execution statistics for SQL Statement in SQL*Plus.

Requirements to use Autotrace

1. You must have a plan table to generate Explain plan report. If you don't have it then execute the following script to create it.

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

2. You must have PLUSTRACE role. If you don't have it, then run the following script as a SYS user and grant the role.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> Grant plustrace to scott;

Turning on Autotrace

SQL> Set Autotrace ON explain -- Generates only Explain plan report
SQL> Set Autotrace ON Statistics -- Generates only Statistics report
SQL> Set Autotrace ON -- Generates both Explain plan and Statistics.

SQL> Set Autotrace OFF -- Turns off tracing

Sample Output

SQL> Select * from test;

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=22)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1 Bytes=22)

Statistics

314 recursive calls
0 db block gets
55 consistent gets
5 physical reads
0 redo size
143 bytes sent via SQL*Net to client
210 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed

Thanks