oracle autotrace

set autotrace on
set autotrace off
set autotrace on statistics
set autotrace on explainplan
set autotrace on traceonly


SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
• SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer
execution path.
• SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
• SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
• SET AUTOTRACE TRACEONLY: This is like SET AUTOTRACE ON, but it suppresses the printingof the user’s query output, if any.


Setting Up AUTOTRACE in SQL*Plus
AUTOTRACE is a facility within SQL*Plus that shows you the explain plan of the queries you’ve
executed and the resources they used. This book makes extensive use of the AUTOTRACE
facility.
There is more than one way to get AUTOTRACE configured. This is what I like to do to get
AUTOTRACE working:
1. cd [ORACLE_HOME]/rdbms/admin
2. log into SQL*Plus as SYSTEM
3. Run @utlxplan
4. Run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
5. Run GRANT ALL ON PLAN_TABLE TO PUBLIC;
You can replace the GRANT TO PUBLIC with some user if you want. By making the PLAN_
TABLE public, you let anyone trace using SQL*Plus (not a bad thing, in my opinion). This
prevents each and every user from having to install his or her own plan table. The alternative
is for you to run @utlxplan in every schema from which you want to use AUTOTRACE.
The next step is creating and granting the PLUSTRACE role:
1. cd [ORACLE_HOME]/sqlplus/admin
2. Log in to SQL*Plus as SYS or as SYSDBA
3. Run @plustrce
4. Run GRANT PLUSTRACE TO PUBLIC;
Again, you can replace PUBLIC in the GRANT command with some user if you want.

reference:
book: Expert Oracle database architecture 9i and 10g programming technologies.