Collecting SQL Trace Data in Other Sessions

DBMS_SYSTEM.SET_EV()

This package contains several helpful procedures including:

set_int_param_in_session
set_ev
set_bool_param_in_session

Earlier in this chapter examples of uses for the set_int_param_in_session procedure and the set_bool_param_in_session procedure were provided. Now the set_ev procedure and one way to use its functionality will be covered.

set_ev is short for SET EVent and it accepts five parameters. The first four will be explained here, and the fifth should be left set to a null field with two single quotes side-by-side with no space (‘’). The dbms_system package is officially unsupported by Oracle and the fifth field is undocumented and not intended to be used outside of Oracle.

The four remaining parameters are:

SID
Serial#
Event
Level

SID and Serial# are the identifiers that can be used to uniquely specify the particular session or process in the database that is of interest. Use the session_identification.sql script introduced in Chapter 2 to get these pieces of information.

Event should be set to the event number that will be traced. In this book, it will always be 10046. It is strongly advised not to set any other without good counsel and/or direction from Oracle Support.

Level is the detail that is desired. Use the table in Figure 3.1 to determine the setting for Level. All four of these parameters are numeric. Here is the output from using the session_identification.sql followed by executing this procedure to enable level 12 tracing for a user whose last name is ANDERT. In this example, ANDERT must be part of the userid:

SQL> @session_identification

Enter value for username: ANDERT

old 10: USERNAME like UPPER('%&username%')

new 10: USERNAME like UPPER('%ANDERT%')

SID SERIAL# USERNAME LOGON_TIM STATUS

------ ---------- ---------- --------- --------

8 45 ANDERTST 20-DEC-03 INACTIVE

SQL> execute dbms_system.set_ev (8, 45,10046,12,'');

The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Interface Analysis

ISBN 0-9745993-7-9