Oracle event trace types

Setting an Oracle trace event

Another set of parameters that may be useful are events, so I?ll discuss events a bit in this subsection. Setting an event means to tell Oracle to generate information in form of a trace file in the context of the event. The trace file is usually located in a directory specified by the initialization parameter USER_DUMP_DEST. By examining the resulting trace file, detailed information about the event traced can be deduced. The general format for an event is:

EVENTS = "<trace class><event name><action><name><trace name><qualifier>"

There are two types of events: session-events and process-events. Process-events are initialized in the parameter file; session-events are initialized with the ALTER SESSION... or ALTER SYSTEM command. When checking for posted events, the Oracle Server first checks for session-events then for process-events.

Oracle trace Event Classes

There are four traceable event classes:

• Class 1: 'Dump something.' Traces are generated upon so-called unconditioned immediate, events. This is the case when Oracle data has to be dumped; for example , the headers of all redolog files or the contents of the controlfile. These events can not be set in the init<SID>.ora, but must be set using the ALTER SESSION or the DBMS_SESSION.SET_EV() procedure.
   

• Class 2: 'Trap on Error.' Setting this class of (error-) events causes Oracle to generate an errorstack every time the event occurs.
   

• Class 3: 'Change execution path.' Setting such an event will cause Oracle to change the execution path for some specific Oracle internal code segment. For example, setting event "10269" prevents SMON from doing free-space coalescing.
   

• Class 4: 'Trace something.' Events from this class are set to obtain traces that are used for, for example, SQL tuning. A common event is "10046", which will cause Oracle to trace the SQL access path on each SQL-statement.

The SET EVENTS command in an init<SID>.ora file have generally been placed there at the command of Oracle support to perform specific functions. Usually, these alerts turn on more advanced levels of tracing and error detection than are commonly available. Source 2.6 lists some of the more common events.

The syntax to specify multiple events in the init.ora is:

EVENT="<event 1>:<event 2>: <event 3>: <event n>"

You can also split the events on multiple lines by using the continuation backslash character (\) at the end of each event and continue the next event on the next line. For example:

    EVENT="<event 1>:\
    <event 2>:\
    <event 3>: \
    <event n>"

       For Example:

    EVENT="\
    10210 trace name context forever, level 10:\
    10211 trace name context forever, level 10:\
    10231 trace name context forever, level 10:\
    10232 trace name context forever, level 10"

After setting the events in the initialization file, you need to stop and restart the instance. Be sure to check the alert.log and verify that the events are in effect. You can specify almost all EVENT settings  at the session level using the ALTER SESSION command or a call to the DBMS_SYSYTEM.SET_EV( ) procedure; doing so does not require an instance bounce for the EVENT to take effect.

The alert.log should show the events that are in effect; for example:

    event = 10210 trace name context forever, level 10:10211 trace name context for ever, level 10:10231 trace name context forever, level 10:10232 trace name context forever, level 10

Example Uses of the EVENT Initialization Parameter

To enable block header and trailer checking to detect corrupt blocks:

event="10210 trace name context forever, level 10"  -- for tables
event="10211 trace name context forever, level 10"  -- for indexes
event="10210 trace name context forever, level 2" -- data block checking
event="10211 trace name context forever, level 2" -- index block checking
event="10235 trace name context forever, level 1" -- memory heap checking
event="10049 trace name context forever, level 2" -- memory protect cursors

And to go with these, the undocumented parameter setting:

 _db_block_cache_protect=TRUE

which will prevent corruption from getting to your disks (at the cost of a database crash).

For tracing of a MAX_CURSORS exceeded error:

event="1000 trace name ERRORSTACK level 3"

To get an error stack related to a SQLNET ORA-03120 error:

event="3120 trace name error stack"

To work around a space leak problem:

event="10262 trace name context forever, level x"

where x is the size of space leak to ignore.

To trace memory shortages:

event="10235 trace name context forever, level 4"

event="600 trace name heapdump, level 4"

To take a shared pool heapdump to track Ora-04031 as the error occurs, set the following event in your init.ora file:

event = "4031 trace name heapdump forever, level 2"

For ORA-04030  errors: Take a dump by setting this event in your INIT file and analyze the trace file. This will clearly pinpoint the problem.

event="4030 trace name errorstack level 3"

The following undocumented SQL statements can be used to obtain information about internal database structures:

* To dump the control file:

alter session set events 'immediate trace name CONTROLF level 10'

* To dump the file headers:

alter session set events 'immediate trace name FILE_HDRS level 10'

* To dump redo log headers:

alter session set events 'immediate trace name REDOHDR level 10'

* To dump the system state:

alter session set events 'immediate trace name SYSTEMSTATE level 10'

* To dump the optimizer statistics whenever a SQL statement is parsed:

alter session set events '10053 trace name context forever'

* To prevent db block corruptions:

event = "10210 trace name context forever, level 10"

event = "10211 trace name context forever, level 10"

event = "10231 trace name context forever, level 10"

* To enable the maximum level of SQL performance monitoring:

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

* To enable a memory-protect cursor:

event = "10049 trace name context forever, level  2"

* To perform data-block checks:

event = "10210 trace name context forever, level  2"

* To perform index-block checks:

event = "10211 trace name context forever, level  2"

* To perform memory-heap checks:

event = "10235 trace name context forever, level  1"

* To allow 300 bytes memory leak for each connection:

event = "10262 trace name context forever, level 300"

You should be noticing a pattern here for tracing events related to error codes: the first argument in the EVENT is the error code followed by the action you want to take upon receiving the code.

Events at the Session Level

Events are also used as the SESSION level using the ALTER SESSION command or calls to the DBMS_SYSTEM.SET_EV() procedure. The general format for the ALTER SESSION command is:

ALTER SESSION SET EVENTS 'ev_number ev_text level x';

where:

  Ev_number is the event number.

    Ev_text is any required text (usually "trace name context forever").

    x is the required level setting corresponding to the desire action, file, or
      other  required data.

For example, to provide more detailed SQL trace information:

ALTER SESSION SET EVENTS '10046 trace name context forever level NN'

where NN:

1 is same as a regular trace.

4 means also dump bind variables

8 means also dump wait information

12 means dump both bind and wait information

Example Uses of the ALTER SESSION Command to Set EVENT Codes

To coalesce free space in a tablespace pre-version 7.3:

ALTER SESSION SET EVENTS 'immediate trace name coalesce level XX'

where:

             XX is the value of ts# from ts$ table for the tablespace.

To coalesce free space in a tablespace defined as temporary:

ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';

where:

   x is the value for file# from ts$ plus 1.

To get the information out of the db block buffers regarding order of LRU chains:

ALTER SESSION SET EVENTS 'immediate trace name buffers level x';

where:

   x  is 1-3 for buffer header order or 4-6 for LRU chain order.

To correct transportable tablespace export hanging (reported on 8.1.6, 8.1.7 on HPUX, a known bug):

ALTER SESSION SET EVENT '10297 trace name context forever, level 1';

To cause "QKA Disable GBY sort elimination". This affects how Oracle will process sorts:

ALTER SESSION SET EVENTS '10119 trace name context forever';

* You can disable the Index FFS using the event 10156. In this case, CBO will lean toward FTS or Index scan.

* You can set the event 10092 if you want to disable the hash joins completely.

It is very easy to see how SMON cleans up rollback entries by using the event 10015. You can use event 10235 to check how the memory manager works internally.

CBO is definitely not a mystery. Use event 10053 to give the detail of the various plans considered, depending on the statistics available; be careful using this for large multi-table joins, as the report can be quite lengthy! The data density, sparse characteristics, index availability, and index depth all lead the optimizer to make its decisions. You can see the running commentary in trace files generated by the 10053 event.