Events
Introduction
There are four types of numeric events
- Immediate dumps
- Conditional dumps
- Trace dumps
- Events that change database behaviour
Every event has a number which is in the Oracle error message range e.g. event 10046 is ORA-10046
Each event has one or more levels which can be
- range e.g. 1 to 10
- bitmask e.g. 0x01 0x02 0x04 0x08 0x10
- flag e.g. 0=off; 1=on
- identifier e.g. object id, memory address etc
Note that events change from one release to another. As existing events become deprecated and then obsolete, the event number is frequently reused for a new event. Note also that the message file sometimes does not reflect the events in the current release.
Many events change the behaviour of the database. Some testing events may cause the database to crash. Never set an event on a production database without obtaining permission from Oracle support. In addition, never set an event on a development database without first making a backup.
Enabling Events
Events can be enabled at instance level in the init.ora file using
event='event trace name context forever, level level';
Multiple events can be enabled in one of two ways
1 - Use a colon to separate the event text e.g.
event = "10248 trace name context forever, level 10:10249 trace name context forever, level 10"
2 - List events on consecutive lines e.g.
event = "10248 trace name context forever, level 10"
event = "10249 trace name context forever, level 10"
Note that in some versions of Oracle, the keyword "event" must be in the same case (i.e. always uppercase or always lowercase).
Events can also be enabled at instance level using the ALTER SYSTEM command
ALTER SYSTEM SET EVENTS
'event trace name context forever, level level';
Events are disabled at instance level using
ALTER SYSTEM SET EVENTS
'event trace name context off';
Events can also be enabled at session level using the ALTER SESSION command
ALTER SESSION SET EVENTS
'event trace name context forever, level level';
Events are disabled at session level using
ALTER SESSION SET EVENTS
'event trace name context off';
Events can be enabled in other sessions using ORADEBUG
To enable an event in a process use
ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example to set event 10046, level 12 in Oracle process 8 use
ORADEBUG SETORAPID 8
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
To disable an event in a process use
ORADEBUG EVENT event TRACE NAME CONTEXT OFF
To enable an event in a session use
ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example
ORADEBUG SESSION_EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
To disable an event in a session use
ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFF
Events can be also enabled in other sessions using DBMS_SYSTEM.SETEV
The SID and the serial number of the target session must be obtained from V$SESSION.
For example to enable event 10046 level 8 in a session with SID 9 and serial number 29 use
EXECUTE dbms_system.set_ev (9,29,10046,8,'');
To disable event 10046 in the same session use
EXECUTE dbms_system.set_ev (9,29,10046,0,'');
Listing All Events
Most events are numbered in the range 10000 to 10999. To dump all event messages in this range use
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP ;
END;
/
On Unix systems event messages are in the formatted text file
$ORACLE_HOME/rdbms/mesg/oraus.msg
To print detailed event messages (Unix only) use the following script
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
Listing Enabled Events
To check which events are enabled in the current session
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line ('Event '||TO_CHAR (l_event)||
' is set at level '||TO_CHAR (l_level));
END IF;
END LOOP ;
END;
/
|
| 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 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
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 -- |
1075
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
