BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  Oracle Tips by Burleson

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  

Stephen Andert 

http://www.rampant-books.com/book_2004_2_wait_tuning.htm

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.