 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
 |
Donald K. Burleson Oracle trace event numbers |
Oracle trace event numbers
The University of Illinois has created this set of Oracle trace event numbers:
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: | Action |
| 0 | Disabled |
| 1 | Print SQL statements, execution plans and execution statistics. Same as ALTER SESSION SET sql_trace = 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 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: | Action |
| 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. 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 Dumps
The 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 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 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 Reads
This 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 Application
This 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 Header
This 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 Changes
This 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 / Deletes
This 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 Extents
This 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 Execution
This 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 Process
This event can be used to trace the actions of the PMON background process.
This event can only be enabled in the PFILE 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 PFILE 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_dDispatcherNumber_ThreadNumber.trc
For example:
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 PFILE 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_sSharedServerNumber_ThreadNumber.trc
For example:
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.
For example:
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.
For example:
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.
For example:
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.
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 Allocation
This 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 Statistics
This 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_pServerNumber_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 Process
This event can be used to trace the actions of the SMON background process.
This event can be enabled in the PFILE using:
event = "10500 trace name
context forever, level 1" Event 10608 - Trace Bitmap Index Creation
This event traces bitmap index creation.
For example:
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.
For example:
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.
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 PFILE.
Event 10708 - Trace RAC Buffer Cache
This 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 Access
This 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 Operation
This 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 Operation
This 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 Operation
This 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 Operation
This 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 Operation
This 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/Decompress
This 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 Compaction
This event traces the bitmap index compaction.
For example:
ALTER
SESSION SET EVENTS '10717 trace name context forever, level 1';
Event 10719 - Trace Bitmap Index DML
This 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 Predicates
This event traces find grained access (RLS) predicates.
For example:
ALTER SESSION SET EVENTS '10730 trace name context
forever, level 1'; Event 10731 - Trace CURSOR Statements
This 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 Execution
This event traces PL/SQL execution.
For example:
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..
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 --
|
|
Copyright © 1996 - 2009 by
Burleson Enterprises, Inc. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.