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

About AUTOTRACE
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (i.e., SELECT, DELETE, UPDATE, MERGE, and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.

Controlling the Report
You can control the report by setting the AUTOTRACE system variable:

• 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 printing of the user’s query output, if any.

regards,
rajeshkumar govindarajan