Autotrace in SQLPLUS

From Thomas Kyte about autotrace:

Here is what I like to do to get autotrace working:

• cd $oracle_home/rdbms/admin
• log into sqlplus as system
• run SQL> @utlxplan
• run SQL> create public synonym plan_table for plan_table
• run SQL> grant all on plan_table to public
• exit sqlplus and cd $oracle_home/sqlplus/admin
• log into sqlplus as SYS
• run SQL> @plustrce
• run SQL> grant plustrace to public

You can replace public with some user if you want. by making it public, you let anyone trace using sqlplus (not a bad thing in my opinion).

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 (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements. For complete articles please take a look at http://asktom.oracle.com/.