Event Reference
10013 - Monitor Transaction Recovery
10015 - Dump Undo Segment Headers
10032 - Dump Sort Statistics
10033 - Dump Sort Intermediate Run Statistics
10045 - Trace Free List Management Operations
10046 - Enable SQL Statement Trace
10053 - Dump Optimizer Decisions
10060 - Dump Predicates
10065 - Restrict Library Cache Output for State Object Dumps
10079 - Dump SQL*Net Statistics
10081 - Dump High Water Mark Changes
10104 - Dump Hash Join Statistics
10128 - Dump Partition Pruning Information
10200 - Dump Consistent Reads
10201 - Dump Consistent Read Undo Application
10220 - Dump Changes to Undo Header
10221 - Dump Undo Changes
10224 - Dump Index Block Splits / Deletes
10225 - Dump Changes to Dictionary Managed Extents
10241 - Dump Remote SQL Execution
10246 - Trace PMON Process
10248 - Trace Dispatcher Processes
10249 - Trace Shared Server (MTS) Processes
10270 - Debug Shared Cursors
10357 - Debug Direct Path
10390 - Dump Parallel Execution Slave Statistics
10391 - Dump Parallel Execution Granule Allocation
10393 - Dump Parallel Execution Statistics
10500 - Trace SMON Process
10608 - Trace Bitmap Index Creation
10704 - Trace Enqueues
10706 - Trace Global Enqueue Manipulation
10708 - Trace RAC Buffer Cache
10710 - Trace Bitmap Index Access
10711 - Trace Bitmap Index Merge Operation
10712 - Trace Bitmap Index OR Operation
10713 - Trace Bitmap Index AND Operation
10714 - Trace Bitmap Index MINUS Operation
10715 - Trace Bitmap Index Conversion to ROWIDs
10716 - Trace Bitmap Index Compress / Decompress
10717 - Trace Bitmap Index Compaction
10719 - Trace Bitmap Index DML
10730 - Trace Fine Grained Access Predicates
10731 - Trace CURSOR Statements
10928 - Trace PL/SQL Execution
10938 - Trace PL/SQL Execution Statistics
10013 - Monitor Transaction Recovery
10015 - Dump Undo Segment Headers
10032 - Dump Sort Statistics
10033 - Dump Sort Intermediate Run Statistics
10045 - Trace Free List Management Operations
10046 - Enable SQL Statement Trace
10053 - Dump Optimizer Decisions
10060 - Dump Predicates
10065 - Restrict Library Cache Output for State Object Dumps
10079 - Dump SQL*Net Statistics
10081 - Dump High Water Mark Changes
10104 - Dump Hash Join Statistics
10128 - Dump Partition Pruning Information
10200 - Dump Consistent Reads
10201 - Dump Consistent Read Undo Application
10220 - Dump Changes to Undo Header
10221 - Dump Undo Changes
10224 - Dump Index Block Splits / Deletes
10225 - Dump Changes to Dictionary Managed Extents
10241 - Dump Remote SQL Execution
10246 - Trace PMON Process
10248 - Trace Dispatcher Processes
10249 - Trace Shared Server (MTS) Processes
10270 - Debug Shared Cursors
10357 - Debug Direct Path
10390 - Dump Parallel Execution Slave Statistics
10391 - Dump Parallel Execution Granule Allocation
10393 - Dump Parallel Execution Statistics
10500 - Trace SMON Process
10608 - Trace Bitmap Index Creation
10704 - Trace Enqueues
10706 - Trace Global Enqueue Manipulation
10708 - Trace RAC Buffer Cache
10710 - Trace Bitmap Index Access
10711 - Trace Bitmap Index Merge Operation
10712 - Trace Bitmap Index OR Operation
10713 - Trace Bitmap Index AND Operation
10714 - Trace Bitmap Index MINUS Operation
10715 - Trace Bitmap Index Conversion to ROWIDs
10716 - Trace Bitmap Index Compress / Decompress
10717 - Trace Bitmap Index Compaction
10719 - Trace Bitmap Index DML
10730 - Trace Fine Grained Access Predicates
10731 - Trace CURSOR Statements
10928 - Trace PL/SQL Execution
10938 - Trace PL/SQL Execution Statistics
Event 10013 - Monitor Transaction Recovery
This event can be used to trace transaction recovery during startup
For example
Event 10015 - Dump Undo Segment Headers
This event can be used to dump undo segment headers before and after transaction recovery
This event can be used to dump undo segment headers before and after transaction recovery
For example
Event 10032 - Dump Sort Statistics
This event can be used to dump sort statistics. Level 10 is the most detailed
This event can be used to dump sort statistics. Level 10 is the most detailed
For example
Event 10033 - Dump Sort Intermediate Run Statistics
This event can be used to dump sort intermediate run statistics. Level 10 is the most detailed
This event can be used to dump sort intermediate run statistics. Level 10 is the most detailed
For example
Event 10045 - Trace Free List Management Operations
This event can be used to trace free list management operations
This event can be used to trace free list management operations
For example
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.
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
Levels are
1 Print SQL statements, execution plans and execution statistics
4 As level 1 plus bind variables
8 As level 1 plus wait statistics
12 As level 1 plus bind variables and wait statistics
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
Levels are
1 Print statistics and computations
2 Print computations only
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
To enable this event use
This example uses the following object
With event 10060 set to level 1, when the following statement is executed for the first time
It is parsed and the results written to kkoipt_table
The results can be selected using the statement
1 Table:
2 T1
3 frofand
4 "T1"."C02"=0
The following table summarises the various operations that can be reported by this event
fptconst Folding constants
fptrnum Remove ROWNUM predicates
fptwhr Remove all WHERE predicates except remaining ROWNUM predicates
frofkks (rowid lookup) ROWID lookup
frofkks[i] (and-equal lookup) start key
frofkke[i] (and-equal lookup) end key
froiand index-only predicate
frofkksm[i] (sort-merge) sort-merge key
frosand (sort-merge) sort-merge predicates
frojand (sort-merge) join predicates
frofkks[i] (index start key) index start key
frofkke[i] (index stop key) index end key
frofand (hash part) table predicate (hash)
froiand (index only filter) index only predicate
frofand table predicate
froutand outer join predicates
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
where level is one of the following
1 Address of library object only
2 As level 1 plus library object lock details
3 As level 2 plus library object handle and library object
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
This event can be used to SQL*Net statistics. Level 2 is the most detailed
For example
Event 10081 - Trace High Water Mark Changes
This event can be used to trace high water mark changes
This event can be used to trace high water mark changes
For example
Event 10104 - Dump Hash Join Statistics
This event can be used to hash join statistics. Level 10 is the most detailed
This event can be used to hash join statistics. Level 10 is the most detailed
For example
Event 10128 - Dump Partition Pruning Information
This event can be used to partition pruning information
This event can be used to partition pruning information
For example
Levels are
0x0001 Dump pruning descriptor for each partitioned object
0x0002 Dump partition iterators
0x0004 Dump optimizer decisions about partition-wise joins
0x0008 Dump ROWID range scan pruning information
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
Event 10200 - Dump Consistent Reads
This event can be used to dump consistent reads
This event can be used to dump consistent reads
Event 10201 - Dump Consistent Read Undo Application
This event can be used to dump consistent read undo application
This event can be used to dump consistent read undo application
Event 10220 - Dump Changes to Undo Header
This event can be used to dump changes to the undo header (transaction table)
This event can be used to dump changes to the undo header (transaction table)
Event 10221 - Dump Undo Changes
This event can be used to dump undo changes applied. Level 7 is the most detailed
This event can be used to dump undo changes applied. Level 7 is the most detailed
Event 10224 - Dump Index Block Splits / Deletes
This event can be used to dump index block splits and deletes detailed
This event can be used to dump index block splits and deletes detailed
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
This event can be used to dump changes to dictionary-managed extents made in the row cache
Event 10241 - Dump Remote SQL Execution
This event can be used to dump remotely executed SQL statements
This event can be used to dump remotely executed SQL statements
Event 10246 - Trace PMON Process
This event can be used to trace the actions of the PMON background 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
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 used to trace dispatcher processes
This event can be enabled in the init.ora file using
In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format
e.g.
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 used to trace shared server (MTS) processes
This event can be enabled in the init.ora file using
In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format
e.g.
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
This event can be used to enable debugging code in shared cursor management modules
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.
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 10357 - Debug Direct Path
This event can be used to enable debugging code for direct path
This event can be used to enable debugging code for direct path
Event 10390 - Dump Parallel Execution Slave Statistics
This event can be used to dump parallel slave statistics
This event can be used to dump parallel slave statistics
Levels are (from messages)
0x0001 Slave-side execution messages
0x0002 Coordinator-side execution messages
0x0004 Slave context state changes
0x0008 Slave ROWID range bind variables and xty
0x0010 Slave fetched rows as enqueued to TQ
0x0020 Coordinator wait reply handling
0x0040 Coordinator wait message buffering
0x0080 Slave dump timing
0x0100 Coordinator dump timing
0x0200 Slave dump allocation file number
0x0400 Terse format for debug dumps
0x0800 Trace CRI random sampling
0x1000 Trace signals
0x2000 Trace parallel execution granule operations
0x4000 Force compilation by slave 0
Event 10391 - Dump Parallel Execution Granule Allocation
This event can be used to dump parallel granule allocation / assignment statistics
Levels are (from messages)
0x0001 Dump summary of each object scanned in parallel
0x0002 Full dump of each object except extent map
0x0004 Full dump of each object including extent map
0x0010 Dump summary of each granule generators
0x0020 Full dump of granule generators except granule instances
0x0040 Full dump of granule generators including granule instances
0x0080 Dump system information
0x0100 Dump reference object for the query
0x0200 Gives timing in kxfralo
0x0400 Trace affinity module
0x0800 Trace granule allocation during query execution
0x1000 Trace object flush
0x2000 Unknown
Event 10393 - Dump Parallel Execution Statistics
This event can be used to dump kxfp statistics after each parallel query
Note that in Oracle 9.2 for parallel execution trace is written to files with names of the format
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 used to trace the actions of the SMON background process
This event can be enabled in the init.ora file using
Event 10608 - Trace Bitmap Index Creation
This event traces bitmap index creation.
This event traces bitmap index creation.
Event 10704 - Trace Enqueues
This event dumps information about which enqueues are being obtained
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
Event 10706 - Trace Global Enqueue Manipulation
This event allows RAC global enqueue manipulation to be trace
This event allows RAC global enqueue manipulation to be trace
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
This event allows RAC buffer cache activity to be traced
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
This event traces bitmap index access. It displays the start ROWID and end ROWID of each bitmap
Event 10711 - Trace Bitmap Index Merge Operation
This event traces the bitmap index merge operation.
This event traces the bitmap index merge operation.
Event 10712 - Trace Bitmap Index OR Operation
This event traces the bitmap index OR operation.
This event traces the bitmap index OR operation.
Event 10713 - Trace Bitmap Index AND Operation
This event traces the bitmap index AND operation.
This event traces the bitmap index AND operation.
Event 10714 - Trace Bitmap Index MINUS Operation
This event traces the bitmap index MINUS operation.
This event traces the bitmap index MINUS operation.
Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation
This event traces the bitmap index conversion to ROWIDs operation
This event traces the bitmap index conversion to ROWIDs operation
Event 10716 - Trace Bitmap Index Compress/Decompress
This event traces the bitmap index compress/decompress
This event traces the bitmap index compress/decompress
Event 10717 - Trace Bitmap Index Compaction
This event traces the bitmap index compaction.
This event traces the bitmap index compaction.
Event 10719 - Trace Bitmap Index DML
This event traces the bitmap index DML.
This event traces the bitmap index DML.
Event 10730 - Trace Fine Grained Access Predicates
This event traces find grained access (RLS) predicates
This event traces find grained access (RLS) predicates
Event 10731 - Trace CURSOR Statements
This event traces CURSOR statements
This event traces CURSOR statements
Levels are
1 Print parent query and subquery
2 Print subquery only
Event 10928 - Trace PL/SQL Execution
This event traces PL/SQL execution
Event 10938 - Dump PL/SQL Execution Statistics
This event dumps PL/SQL execution statistics.
This event dumps PL/SQL execution statistics.
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 ----------------------------------------------------------------------------------
--PL/SQL TRACE INFORMATION IS NOW IN THE DATABASE
-- To create the trace tables, use the script
-- rdbms/admin/tracetab.sql under ORACLE_HOME
$ORACLE_HOME/rdbms/mesg/oraus.msg