Event Reference
Event 10013 - Monitor Transaction RecoveryThis 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 HeadersThis 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 StatisticsThis 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 StatisticsThis 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 OperationsThis 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 TraceThis 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: |
Action |
0 |
Disabled |
1 |
Print SQL statements, execution plans and execution statistics. Same as SET = TRUE |
4 |
All level 1 plus bind variables |
8 |
All level 1 plus wait statistics |
12 |
All level 1 plus bind variables and wait statistics |
Event 10053 - Dump DecisionsThis 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: |
Action |
1 |
Print statistics and computations |
2 |
Print computations only |
Event 10060 - Dump PredicatesThis 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;
Levels are: |
Action |
1 |
Table: |
2 |
T1 |
3 |
frofand |
4 |
"T1"."C02"=0 |
The following table summarises the various operations that can be reported by this event
Operation |
Description |
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 DumpsThe amount of library cache dump output for state object dumps can be limited using event 10065
For example:
ALTER SESSION SET EVENTS '10065 trace name context forever, level number';
where
number is one of the following
Level no. |
Description |
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 StatisticsThis 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 ChangesThis 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 StatisticsThis 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 InformationThis event can be used to partition pruning information.
For example:
ALTER SESSION SET EVENTS '10128 trace name context forever, level number';
Levels no. are: |
Action |
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:
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 ReadsThis event can be used to dump consistent reads.
For example:
ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';
Event 10201 - Dump Consistent Read Undo ApplicationThis event can be used to dump consistent read undo application.
For example:
ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';
Event 10220 - Dump Changes to Undo HeaderThis event can be used to dump changes to the undo header (transaction table).
For example:
ALTER SESSION SET EVENTS '10220 trace name context forever, level 1';
Event 10221 - Dump Undo ChangesThis event can be used to dump undo changes applied. Level 7 is the most detailed.
For example:
ALTER SESSION SET EVENTS '10221 trace name context forever, level 7';
Event 10224 - Dump Index Block Splits / DeletesThis event can be used to dump index block splits and deletes detailed.
For example:
ALTER SESSION SET EVENTS '10224 trace name context forever, level 1';
Event 10225 - Dump Changes to Dictionary Managed ExtentsThis event can be used to dump changes to dictionary-managed extents made in the row cache.
For example:
ALTER SESSION SET EVENTS '10225 trace name context forever, level 1';
Event 10241 - Dump Remote SQL ExecutionThis event can be used to dump remotely executed SQL statements.
For example:
ALTER SESSION SET EVENTS '10241 trace name context forever, level 1';
Event 10246 - Trace PMON ProcessThis event can be used to trace the actions of the PMON background process.
This event can only be enabled in the 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 ProcessesThis event can be used to trace dispatcher processes.
This event can be enabled in the using.
event = "10248 trace name context forever, level 10"
In Oracle 9.2 (Windows 2000) the trace is written to a file in udump with the name in the format:
ServiceName_d
DispatcherNumber_
ThreadNumber.trc
For example:
JD92001_d000_1234.trc
Valid levels are 1 to 10 (Metalink Note)
Event 10249 - Trace Shared Server (MTS) ProcessesThis event can be used to trace shared server (MTS) processes.
This event can be enabled in the using:
event = "10249 trace name context forever, level 10"
In Oracle 9.2 (Windows 2000) the trace is written to a file in udump with the name in the format:
ServiceName_s
SharedServerNumber_
ThreadNumber.trc
For example:
JD92001_s000_5678.trc
Valid levels are 1 to 10 (Metalink Note)
Event 10270 - Debug Shared CursorsThis event can be used to enable debugging code in shared cursor management modules.
For example:
event = "10270 trace name context forever, level 10"
Event 10299 - Debug PrefetchingThis event can be used to enable debugging code for table and index block prefetching. It also enables dumping of trace by the CKPT process.
For example:
event = "10299 trace name context forever, level 1"
Event 10357 - Debug Direct PathThis event can be used to enable debugging code for direct path.
For example:
ALTER SESSION SET EVENTS '10357 trace name context forever, level 1';
Event 10390 - Dump Parallel Execution Slave StatisticsThis event can be used to dump parallel slave statistics.
For example:
ALTER SESSION SET EVENTS '10390 trace name context forever, level number';
Level No. |
Action |
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 AllocationThis event can be used to dump parallel granule allocation / assignment statistics.
For example:
ALTER SESSION SET EVENTS '10391 trace name context forever, level number';
Level No. |
Action |
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 StatisticsThis event can be used to dump kxfp statistics after each parallel query.
For example:
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_p
ServerNumber_
ThreadNumber.trc
Below is example output for this event.
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 ProcessThis event can be used to trace the actions of the SMON background process.
This event can be enabled in the using:
event = "10500 trace name context forever, level 1"
Event 10608 - Trace Bitmap Index CreationThis event traces bitmap index creation.
For example:
ALTER SESSION SET EVENTS '10608 trace name context forever, level 10';
Event 10704 - Trace EnqueuesThis 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.
For example:
ALTER SESSION SET EVENTS '10704 trace name context forever, level 1';
Event 10706 - Trace Global Enqueue ManipulationThis event allows RAC global enqueue manipulation to be trace.
For example:
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 .
Event 10708 - Trace RAC Buffer CacheThis event allows RAC buffer cache activity to be traced.
For example:
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 AccessThis event traces bitmap index access. It displays the start ROWID and end ROWID of each bitmap.
For example:
ALTER SESSION SET EVENTS '10710 trace name context forever, level 1';
Event 10711 - Trace Bitmap Index Merge OperationThis event traces the bitmap index merge operation.
For example:
ALTER SESSION SET EVENTS '10711 trace name context forever, level 1';
Event 10712 - Trace Bitmap Index OR OperationThis event traces the bitmap index OR operation.
For example:
ALTER SESSION SET EVENTS '10712 trace name context forever, level 1';
Event 10713 - Trace Bitmap Index AND OperationThis event traces the bitmap index AND operation.
For example:
ALTER SESSION SET EVENTS '10713 trace name context forever, level 1';
Event 10714 - Trace Bitmap Index MINUS OperationThis event traces the bitmap index MINUS operation.
For example:
ALTER SESSION SET EVENTS '10714 trace name context forever, level 1';
Event 10715 - Trace Bitmap Index Conversion to ROWIDs OperationThis event traces the bitmap index conversion to ROWIDs operation.
For example:
ALTER SESSION SET EVENTS '10715 trace name context forever, level 1';
Event 10716 - Trace Bitmap Index Compress/DecompressThis event traces the bitmap index compress/decompress.
For example:
ALTER SESSION SET EVENTS '10716 trace name context forever, level 1';
Event 10717 - Trace Bitmap Index CompactionThis event traces the bitmap index compaction.
For example:
ALTER SESSION SET EVENTS '10717 trace name context forever, level 1';
Event 10719 - Trace Bitmap Index DMLThis event traces the bitmap index DML.
For example:
ALTER SESSION SET EVENTS '10719 trace name context forever, level 1';
Event 10730 - Trace Fine Grained Access PredicatesThis event traces find grained access (RLS) predicates.
For example:
ALTER SESSION SET EVENTS '10730 trace name context forever, level 1';
Event 10731 - Trace CURSOR StatementsThis event traces CURSOR statements.
For example:
ALTER SESSION SET EVENTS '10731 trace name context forever, level number';
Levels no. are: |
Action |
1 |
Print parent query and subquery |
2 |
Print subquery only |
Event 10928 - Trace PL/SQL ExecutionThis event traces PL/SQL execution.
For example:
ALTER SESSION SET EVENTS '10928 trace name context forever, level 1';
Event 10938 - Dump PL/SQL Execution StatisticsThis event dumps PL/SQL execution statistics..
For example:
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 --