Saturday, May 19, 2012

Setting up Autotrace in SQL*Plus

AUTOTRACE is a facility within SQL*Plus to show us the explain plan of the queries we've executed, and
the resources they used. There is more than one way to get AUTOTRACE configured.

This is how i do to get AUTOTRACE working:
•  cd $ORACLE_HOME/rdbms/admin (or cd %ORACLE_HOME%/rdbms/admin on Windows)
•  log into SQL*Plus as SYSTEM ==> sqlplus system/pass@alias
•  run @utlxplan
•  run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
•  run GRANT ALL ON PLAN_TABLE TO PUBLIC;
You can replace the GRANT TO PUBLIC with some user if you want. By making it public, you let
anyone trace using SQL*Plus. This prevents every user from having to install their 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:
•  cd $ORACLE_HOME/sqlplus/admin (or cd %ORACLE_HOME%/sqlplus/admin on Windows)
•  log into SQL*Plus as SYS or AS SYSDBA
•  run @plustrce
•  run GRANT PLUSTRACE TO PUBLIC;
Again, you can replace PUBLIC in the GRANT command with some user if you want.

No comments:

Post a Comment