AUTOTRACE in sqlplus

AUTOTRACE in sqlplus

The autotrace command causes Oracle to print out the execution plan for a command and details about the number of disc and buffer reads that have occurred during the execution.

SET AUTOTRACE ON

To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema.

* 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).