Setting Up AUTOTRACE in SQL*Plus

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.

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.