TRACE and EXPLAIN PLAN allow users to monitor
execution statistics of SQL and PL/SQL and provides information
about how Oracle will actually execute the plan.
Instance Wide trace
The DBA can set up instance wide tracing by
setting the SQL_TRACE initialization parameter to TRUE, however
this is not suggested since then each session will generate a
trace in the location specified by the initialization parameter
USER_DUMP_DEST that is named for the users session id. However,
the DBA should set the undocumented parameter "_public_trace_files"
to TRUE in the test and development environments to allow the
developers to see their trace files (on UNIX, on WINDOWS this
isn't required).
In order to make the trace files more useful the
DBA should also set the parameter TIMED_STATISTICS to TRUE.
However, the DBA should verify that the release of Oracle you are
using doesn't have any bugs associated with TIMED_STATISTICS and
SQL_TRACE as there have been some reported against various 8i
versions.
Trace in a session
As long as a user has ALTER SESSION permission
they can alter their own session to be traced. The following
commands will turn on tracing in a user session:
ALTER SESSION SET TIMED_STATISTICS=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;
The user should be careful to turn off tracing
when it is no longer needed. Logging off of a session
automatically terminates session specific tracing.
Tracing Forms,
Reports
Tracing in forms, reports and procedures can be
turned on by use of the DBMS_SESSION.SET_SQL_TRACE command,
however the TIMED_STATISTICS parameter must still be set manually
or via a call using EXECUTE IMMEDIATE on versions prior to
Oracle8i unless the DBMS_SYSTEM package has been loaded. In
Oracle9i the DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION package can be
used to alter the settings of session alterable parameters.
Tracing another
session
The DBA will usually be the one turning on and off
tracing for other sessions, however, this is done using the
DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION package, the oradebug
program or using system events.
Learn More about Oracle Tuning:
