Introduction to ORACLE Diagnostic EVENTS


Before proceeding, please review the following note as it contain some important additional information on Events.

Note 75713.1 "Important Customer information about using Numeric Events"

EVENTS are primarily used to produce additional diagnostic information when insufficient information is available to resolve a given problem.

EVENTS are also used to workaround or resolve problems by changing Oracle's behaviour or enabling undocumented features.

*WARNING* Do not use an Oracle Diagnostic Event unless directed to do so by Oracle Support Services or via a Support related article on Metalink. 
Incorrect usage can result in disruptions to the database services.

Setting EVENTS
--------------

There are a number of ways in which events can be set.

How you set an event depends on the nature of the event and the circumstances at the time. As stated above, specific information on how you set a given event 
should be provided by Oracle Support Services or the Support related article that is suggesting the use of a given event. Most events can be set using more than one of the following methods :

      o As INIT parameters
      o In the current session
      o From another session using a Debug tool

INIT Parameters
~~~~~~~~~~~~~~~

Syntax:

EVENT = "<event_name> <action>"

Reference: 

Note 160178.1 How to set EVENTS in the SPFILE

Current Session
~~~~~~~~~~~~~~~

Syntax:

ALTER SESSION SET EVENTS '<event_name> <action>';

From another Session using a Debug tool
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are a number of debug tools :

      o ORADEBUG
      o ORAMBX (VMS only)

  ORADEBUG :
  ========

     Syntax:

     Prior to Oracle 9i, 

     SVRMGR> oradebug event <event_name> <action>

     Oracle 9i and above :

     SQL> oradebug event <event_name> <action>

     Reference: 

Note 29786.1   "SUPTOOL:  ORADEBUG 7.3+ (Server Manager/SQLPLUS Debug Commands)"
Note 1058210.6 "HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING ORADEBUG"

  ORAMBX : on OpenVMS is still available and described under :
  ======

  Note 29062.1 "SUPTOOL:  ORAMBX (VMS) - Quick Reference"

This note will not enter into additional details on these tools.

EVENT Categories
----------------

The most commonly used events fall into one of four categories :

      o Dump diagnostic information on request
      o Dump diagnostic information when an error occurs
      o Change Oracle's behaviour
      o Produce trace diagnostic information as the instance runs

Dump diagnostic information on request (Immediate Dump)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

An immediate dump Event will result in information immediately being 
written to a trace file.

Some common immediate dump Events include : 

SYSTEMSTATE, ERRORSTACK, CONTROLF, FILE_HDRS and REDOHDR

These type of events are typically set in the current session.

For example:

ALTER SESSION SET EVENTS 'IMMEDIATE trace name ERRORSTACK level 3';

Dump Diagnostic information when an error occurs (On-Error Dump)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The on-error dump Event is similar to the immediate dump Event with the 
difference being that the trace output is only produced when the given
error occurs.

You can use virtually any standard Oracle error to trigger this type of
event.

For example, an ORA-942 "table or view does not exist" error does not include
the name of the problem table or view. When this is not obvious from the
application (due to its complexity), then it can be difficult to investigate
the source of the problem. However, an On-Error dump against the 942 error can 
help narrow the search.

These type of events are typically set as INIT parameters.

For example, using the 942 error :

EVENT "942 trace name ERRORSTACK level 3"

Once established, the next time a session encounters an ORA-942 error, a 
trace file will be produced that shows (amongst other information) the current 
SQL statement being executed. This current SQL can now be checked and the 
offending table or view more easily discovered.

Change Oracle's behaviour
~~~~~~~~~~~~~~~~~~~~~~~~~

Instance behaviour can be changed or hidden features can be enabled using
these type of Event

A common event in this category is 10262 which is discussed in 

Note 21235.1 EVENT: 10262 "Do not check for memory leaks"

These type of events are typically set as INIT parameters.

For example:

EVENT "10262 trace name context forever, level 4000"

Produce trace diagnostic information as the instance runs (Trace Events)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Trace events produce diagnostic information as processes are running.

They are used to gather additional information about a problem.

A common event in this category is 10046 which is discussed in

Note 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

These type of events are typically set as INIT parameters.

For example:

EVENT = "10046 trace name context forever, level 12"

Summary
-------

EVENT usage and syntax can be very complex and due to the possible impact on 
the database, great care should be taken when dealing with them.

Oracle Support Services (or a Support article) should provide information
on the appropriate method to be adopted and syntax to be used when 
establishing a given event.

If it is possible to do so, test an event against a development system 
prior to doing the same thing on a production system.

The misuse of events can lead to a loss of service.

RELATED DOCUMENTS
-----------------

Note 75713.1   Important Customer information about using Numeric Events
Note 21235.1   EVENT: 10262 "Do not check for memory leaks"
Note 21154.1   EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note 160178.1  How to set EVENTS in the SPFILE
Note 1058210.6 HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING ORADEBUG
Note 29786.1   SUPTOOL:  ORADEBUG 7.3+ (Server Manager/SQLPLUS Debug Commands)

Note 29062.1   SUPTOOL:  ORAMBX (VMS) – Quick Reference

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: