autotrace setup oracle

Tracing Statements
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.

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

To use this feature, you must create a PLAN_TABLE table in your schema and then
have the PLUSTRACE role granted to you. DBA privileges are required to grant the
PLUSTRACE role. For information on how to grant a role and how to create the
PLAN_TABLE table, see the Oracle Database SQL Reference.
For more information about the roles and the PLAN_TABLE, see the Oracle Database
SQL Reference and the AUTOTRACE variable of the SET command on page 12-89.
Example 8–1 Creating a PLAN_TABLE
Run the following commands from your SQL*Plus session to create the PLAN_TABLE
in the HR schema:
CONNECT HR
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Example 8–2 Creating the PLUSTRACE Role
Run the following commands from your SQL*Plus session to create the PLUSTRACE
role and grant it to the DBA:
CONNECT / AS SYSDBA
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
drop role plustrace;

create role plustrace;
grant plustrace to dba with admin option;

Example 8–3 Granting the PLUSTRACE Role
Run the following commands from your SQL*Plus session to grant the PLUSTRACE
role to the HR user:
CONNECT / AS SYSDBA
GRANT PLUSTRACE TO HR;

SQL*Plus
User’s Guide and Reference
Release 10.2
B14357-01