 |
|
Oracle Tips by Burleson |
ALTER SESSION SET EVENTS
This is pretty easy, but exiting the program
may be an easier solution. Exiting the session normally (exit from
SQL*Plus for example) causes additional information to be written
that captures the explain plan in the trace file. This will not
work for three-tier applications, as the sessions are persistent
across multiple user connections. It will be necessary to use the
ALTER SESSION SET EVENTS command in these instances to turn tracing
off to avoid a huge trace file that is not properly scoped.
DBMS_SUPPORT.START_TRACE ()
Besides the ALTER SESSION command, the
dbms_support package provides a procedure called start_trace. This
procedure accepts two parameters both of which are Boolean values.
If the first is TRUE, information on WAIT is collected, and if the
second is TRUE, information on BIND values is collected. Figure 3.2
is an expanded version of Figure 3.1 showing the various Level
settings.
Figure 3.2 – dbms_support to trace level
conversion
Using this procedure is fairly simple. An
example follows:
SQL> execute sys.dbms_support.start_trace (true,true);
PL/SQL procedure successfully completed.
One that is done, proceed to do the work
that is being traced. After tracing is completed, either exit the
session, or use the stop_trace procedure as follows:
SQL> execute sys.dbms_support.stop_trace;
PL/SQL procedure successfully completed.
Be aware that the dbms_support package is
not always installed by default. However, it can be installed, by
running $ORACLE_HOME/rdbms/admin/dbmssupp.sql.
A note about rights
Sometimes when trying to trace a session and
the user is unable to start tracing, it is probably just a rights
issue. This can be fixed by granting the user execute privileges on
this package as demonstrated in the following example.
SQL> conn kevin/kevin
Connected.
SQL> execute sys.dbms_support.start_trace (true,true);
BEGIN sys.dbms_support.start_trace (true,true);
END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SUPPORT'
must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> conn / as sysRemote DBA
Connected.
SQL> grant execute on dbms_support to kevin;
Grant succeeded.
SQL> conn kevin/kevin
Connected.
SQL> execute sys.dbms_support.start_trace (true,true);
PL/SQL procedure successfully completed.
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Interface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
