• Welcome
• Projects
• Presentations
• Research

Event Reference

Event 10013 - Monitor Transaction Recovery

This event can be used to trace transaction recovery during startup

For example:

ALTER SESSION SET EVENTS
'10013 trace name context forever, level 1';

Event 10015 - Dump Undo Segment Headers

This event can be used to dump undo segment headers before and after transaction recovery

For example:

ALTER SESSION SET EVENTS
'10015 trace name context forever, level 1';

Event 10032 - Dump Sort Statistics

This event can be used to dump sort statistics. Level 10 is the most detailed

For example:

ALTER SESSION SET EVENTS
'10032 trace name context forever, level 10';

Event 10033 - Dump Sort Intermediate Run Statistics

This event can be used to dump sort intermediate run statistics. Level 10 is the most detailed

For example:

ALTER SESSION SET EVENTS
'10033 trace name context forever, level 10';

Event 10045 - Trace Free List Management Operations

This event can be used to trace free list management operations

For example

ALTER SESSION SET EVENTS
'10045 trace name context forever, level 1';

Event 10046 - Enable SQL Statement Trace

This event can be used to dump SQL statements executed by a session with execution plans and statistics. Bind variable and wait statistics can optionally be included. Level 12 is the most detailed.

For example:

ALTER SESSION SET EVENTS
'10046 trace name context forever, level 12';

Levels are:

Event 10046 level 4 trace only captures the first bind variable in an array.

Consider the following example:

EXECUTE dbms_monitor.session_trace_enable (binds=>TRUE);

CREATE TABLE driver (key VARCHAR2(4), name VARCHAR2(30));

DECLARE
  TYPE driver_tab IS TABLE OF driver%ROWTYPE;
  d driver_tab := driver_tab ();
BEGIN
  d.extend (6);
  d(1).key := 'LHAM'; 	d(1).name := 'Lewis Hamilton';	
  d(2).key := 'NROS'; 	d(2).name := 'Nico Rosberg';
  d(3).key := 'SVET'; 	d(3).name := 'Sebastian Vettel';
  d(4).key := 'FALO'; 	d(4).name := 'Fernando Alonso';
  d(5).key := 'DRIC'; 	d(5).name := 'Daniel Ricciardo';
  d(6).key := 'JBUT'; 	d(6).name := 'Jenson Button';

  FORALL j IN 1..3	
    INSERT INTO driver VALUES (d(j).key,d(j).name);

  FORALL j IN 4..6	
    INSERT INTO driver VALUES (d(j).key,d(j).name);
END;
/

Trace will only contain bind variable values for two rows:

BINDS #139676743602632:
Bind#0
    ...
    value="LHAM"
Bind#1
    ...
    value="Lewis Hamilton"
...

BINDS #139676743602632:
Bind#0
    ...
    value="FALO"
Bind#1
    ...
    value="Fernando Alonso"

The trace output by the 10046 event only includes the first row of each array insert. The remaining rows are not traced.

Note that this has consequences for load generation / simulation tools such as LoadRunner and HammerDB which base their customized input on 10046 trace.

Event 10053 - Dump Optimizer Decisions

This event can be used to dump the decisions made by the optimizer when parsing a statement. Level 1 is the most detailed

For example:

ALTER SESSION SET EVENTS
'10053 trace name context forever, level 1';

Levels are:

Optimizer decisions are only written to the trace file when a statement is hard-parsed.

In Oracle 11.2 and above (possibly earlier) the DBMS_SQLDIAG DUMP_TRACE procedure can be used to dump 10053 trace (optimizer or compiler) for a specific SQL statement. DUMP_TRACE does not execute the SQL statement to generate the trace file.

Event 10060 - Dump Predicates

This event can be used to force the optimizer to dump predicates to a table It is available in Oracle 7.1.3 and above, and was still working in Oracle 9.2.

This event requires the following table to be created in the schema of the user parsing the statement

CREATE TABLE kkoipt_table
(
    c1 INTEGER,
    c2 VARCHAR2(80)
);

To enable this event use:

ALTER SESSION SET EVENTS
'10060 trace name context forever, level 1';

This example uses the following object:

CREATE TABLE t1 (c01 NUMBER, c02 NUMBER);

With event 10060 set to level 1, when the following statement is executed for the first time:

SELECT c01 FROM t1 WHERE c02 = 0;

It is parsed and the results written to kkoipt_table

The results can be selected using the statement:

SELECT c1,c2 FROM kkoipt_table ORDER BY c1;

The following table summarises the various operations that can be reported by this event

Event 10065 - Restrict Library Cache Dump Output for State Object Dumps

The amount of library cache dump output for state object dumps can be limited using event 10065

ALTER SESSION SET EVENTS '10065 trace name context forever, level level';

where level is one of the following

Level 3 is the default

Event 10079 - Dump SQL*Net Statistics

This event can be used to SQL*Net statistics. Level 2 is the most detailed

For example:

ALTER SESSION SET EVENTS
'10079 trace name context forever, level 2';

Event 10081 - Trace High Water Mark Changes

This event can be used to trace high water mark changes

For example:

ALTER SESSION SET EVENTS
'10081 trace name context forever, level 1';

Event 10104 - Dump Hash Join Statistics

This event can be used to hash join statistics. Level 10 is the most detailed

For example:

ALTER SESSION SET EVENTS
'10104 trace name context forever, level 10';

Event 10128 - Dump Partition Pruning Information

This event can be used to partition pruning information

For example:

ALTER SESSION SET EVENTS
'10128 trace name context forever, level level';

Levels are:

There are further levels (up to 4096?)

In Oracle 9.0.1 and above, a table must be created before level 2 of this event can be set.

The table definition is as follows

CREATE TABLE kkpap_pruning
(
    partition_count     NUMBER,
    iterator            VARCHAR2(32),
    partition_level     VARCHAR2(32),
    order_pt            VARCHAR2(12),
    call_time           VARCHAR2(12),
    part#               NUMBER,
    subp#               NUMBER,
    abs#                NUMBER
);

Event 10200 - Dump Consistent Reads

This event can be used to dump consistent reads

ALTER SESSION SET EVENTS
'10200 trace name context forever, level 1';

Event 10201 - Dump Consistent Read Undo Application

This event can be used to dump consistent read undo application

ALTER SESSION SET EVENTS
'10201 trace name context forever, level 1';

Event 10220 - Dump Changes to Undo Header

This event can be used to dump changes to the undo header (transaction table)

ALTER SESSION SET EVENTS
'10220 trace name context forever, level 1';

Event 10221 - Dump Undo Changes

This event can be used to dump undo changes applied. Level 7 is the most detailed

ALTER SESSION SET EVENTS
'10221 trace name context forever, level 7';

Event 10224 - Dump Index Block Splits / Deletes

This event can be used to dump index block splits and deletes detailed

ALTER SESSION SET EVENTS
'10224 trace name context forever, level 1';

Event 10225 - Dump Changes to Dictionary Managed Extents

This event can be used to dump changes to dictionary-managed extents made in the row cache

ALTER SESSION SET EVENTS
'10225 trace name context forever, level 1';

Event 10241 - Dump Remote SQL Execution

This event can be used to dump remotely executed SQL statements

ALTER SESSION SET EVENTS
'10241 trace name context forever, level 1';

Event 10246 - Trace PMON Process

This event can be used to trace the actions of the PMON background process

This event can only be enabled in the init.ora file using:

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

The ALTER SYSTEM command does not appear to work for this event

There only appears to be one level for this event (levels 5 and 10 appear to generate the same output as level 1)

Event 10248 - Trace Dispatcher Processes

This event can be used to trace dispatcher processes

This event can be enabled in the init.ora file using:

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

In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format:

ServiceName_dDispatcherNumber_ThreadNumber.trc

e.g.:

JD92001_d000_1234.trc

Valid levels are 1 to 10 (Metalink Note)

Event 10249 - Trace Shared Server (MTS) Processes

This event can be used to trace shared server (MTS) processes

This event can be enabled in the init.ora file using

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

In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format:

    ServiceName_sSharedServerNumber_ThreadNumber.trc

e.g.:

JD92001_s000_5678.trc

Valid levels are 1 to 10 (Metalink Note)

Event 10270 - Debug Shared Cursors

This event can be used to enable debugging code in shared cursor management modules

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

Event 10299 - Debug Prefetching

This event can be used to enable debugging code for table and index block prefetching. It also enables dumping of trace by the CKPT process.

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

Event 10357 - Debug Direct Path

This event can be used to enable debugging code for direct path

ALTER SESSION SET EVENTS
'10357 trace name context forever, level 1';

Event 10390 - Dump Parallel Execution Slave Statistics

This event can be used to dump parallel slave statistics

ALTER SESSION SET EVENTS
'10390 trace name context forever, level level';

Levels are (from messages)

Event 10391 - Dump Parallel Execution Granule Allocation

This event can be used to dump parallel granule allocation / assignment statistics

ALTER SESSION SET EVENTS
'10391 trace name context forever, level level';

Levels are (from messages):

Event 10393 - Dump Parallel Execution Statistics

This event can be used to dump kxfp statistics after each parallel query

ALTER SESSION SET EVENTS
'10393 trace name context forever, level 1';

Note that in Oracle 9.2 for parallel execution trace is written to files with names of the format

ServiceName_pServerNumber_ThreadNumber.trc

This is an example of the output for this event. The output has been modified for readability

kxfpdst         
      dumping statistics        
      ---------------------------                   
               Query Sessions         1              
          Total Messages Sent         0               
           Data Messages Sent       948         
         Stream Messages Sent       917         
         Dialog Messages Sent        26      
           Null Messages Sent         0    
   Fast Shared Memory Streams       669    
      Fast Distributed Stream         0    
      Stream Mode Credit Ping         0    
         Unknown Credit Pings         0    
          Single Credit Pings       252      
          Double Credit Pings         0       
          Triple Credit Pings         0     
        Multiple Credit Pings         0   
      Total Messages Dequeued         0    
       Data Messages Dequeued        31    
       Null Messages Dequeued         0      
           Immediate Dequeues         1        
              Posted Dequeues        31        
           Timed-out Dequeues         0      
            Implicit Dequeues       255     
          Total Dequeue Waits        85     
       Total Dequeue Timeouts        44    
    Dequeues for Credit (geb)        77     
    Dequeues for Credit (free)        0    
    Dequeues for Credit (enq)        39

Event 10500 - Trace SMON Process

This event can be used to trace the actions of the SMON background process

This event can be enabled in the init.ora file using

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

Event 10608 - Trace Bitmap Index Creation

This event traces bitmap index creation.

ALTER SESSION SET EVENTS
'10608 trace name context forever, level 10';

Event 10704 - Trace Enqueues

This event dumps information about which enqueues are being obtained

When enabled it prints out arguments to calls to ksqcmi and ksqlrl and the return values

ALTER SESSION SET EVENTS
'10704 trace name context forever, level 1';

Event 10706 - Trace Global Enqueue Manipulation

This event allows RAC global enqueue manipulation to be trace

ALTER SESSION SET EVENTS
'10706 trace name context forever, level 1';

The amount of output can be limited using the unsupported parameter '_ksi_trace'.

This parameter specifies the lock types that should be included e.g. TM, TX etc. They are specified as a string e.g. 'TMTX'

The parameter '_ksi_trace' can only be set in the initialisation file.

Event 10708 - Trace RAC Buffer Cache

This event allows RAC buffer cache activity to be traced:

ALTER SESSION SET EVENTS
'10708 trace name context forever, level 10';

This diagnostic applies only to RAC clusters (not single-instance)

Event 10710 - Trace Bitmap Index Access

This event traces bitmap index access. It displays the start ROWID and end ROWID of each bitmap

ALTER SESSION SET EVENTS
'10710 trace name context forever, level 1';

Event 10711 - Trace Bitmap Index Merge Operation

This event traces the bitmap index merge operation.

ALTER SESSION SET EVENTS
'10711 trace name context forever, level 1';

Event 10712 - Trace Bitmap Index OR Operation

This event traces the bitmap index OR operation.

ALTER SESSION SET EVENTS
'10712 trace name context forever, level 1';

Event 10713 - Trace Bitmap Index AND Operation

This event traces the bitmap index AND operation.

ALTER SESSION SET EVENTS
'10713 trace name context forever, level 1';

Event 10714 - Trace Bitmap Index MINUS Operation

This event traces the bitmap index MINUS operation.

ALTER SESSION SET EVENTS
'10714 trace name context forever, level 1';

Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation

This event traces the bitmap index conversion to ROWIDs operation

ALTER SESSION SET EVENTS
'10715 trace name context forever, level 1';

Event 10716 - Trace Bitmap Index Compress/Decompress

This event traces the bitmap index compress/decompress

ALTER SESSION SET EVENTS
'10716 trace name context forever, level 1';

Event 10717 - Trace Bitmap Index Compaction

This event traces the bitmap index compaction.

ALTER SESSION SET EVENTS
'10717 trace name context forever, level 1';

Event 10719 - Trace Bitmap Index DML

This event traces the bitmap index DML.

ALTER SESSION SET EVENTS
'10719 trace name context forever, level 1';

Event 10730 - Trace Fine Grained Access Predicates

This event traces find grained access (RLS) predicates

ALTER SESSION SET EVENTS
'10730 trace name context forever, level 1';

Event 10731 - Trace CURSOR Statements

This event traces CURSOR statements

ALTER SESSION SET EVENTS
'10731 trace name context forever, level level';

Levels are:

Event 10928 - Trace PL/SQL Execution

This event traces PL/SQL execution

ALTER SESSION SET EVENTS
'10928 trace name context forever, level 1';

Event 10938 - Dump PL/SQL Execution Statistics

This event dumps PL/SQL execution statistics.

ALTER SESSION SET EVENTS
'10938 trace name context forever, level 1';

This event currently generates the following output:

--NOTICE  --------------------------------------- 
--PL/SQL TRACE INFORMATION IS NOW IN THE DATABASE
-- To create the trace tables, use the script  --
-- rdbms/admin/tracetab.sql under ORACLE_HOME  --