Oracle Internal Events:
INTRODUCTION: (Introduction)
There are four types of Events:
Immediate dumps
Conditional Dumps
Trace Dumps
Events That Change Database Behaviour
Every event has a number of error messages with Oracle. Such as 10046 and ORA-10046
Every event has a level, which can be below:
Range 1 to 10
Bottom 0x01 0x02 0x04 0x08 0x10
Logo 0 = OFF, 1 = ON
ID number object ID (Object ID), memory address (Memory Address)
It should be noted that Events have changed between every version. There are some events that may have controversial or are not available. They are often replaced by new events. Also pay attention to the current The MESSAGE FILE does not necessarily reflect Events.
Many Events will affect the behavior of the database. Some test Events are extremely likely to cause database DOWN. So, do not do Events on the Pro system without Oracle Support, if you want to do Events, you want to do Events on the PRO system. Make a full backup of a database.
Enabling Events (enable event)
Events can be in the instance level enabled, mainly in the init.ora file:
Event = 'Event TRACE NAME Context Forever, Level Level';
(Red part: Event refers to the level of event number .level specifies the level of events)
You can enable multiple events at a time, you can use the following two ways:
1. Separate with a colon
Event = "10248 Trace Name Context Forever, Level 10: 10249 Trace Name Context Forever, Level 10"
2. Two Events are separated
Event = "10248 TRACE NAME CONTEXT Forever, Level 10"
Event = "10249 Trace Name Context Forever, Level 10" # Some versions of Oracle, Event wants the same case
Events can also use the alter system command to enable at the instance level:
Alter System Set Events 'Event Trace Name Context Forever, Level Level';
Use the following disable at the Instance level
Alter System Set Events 'Event TRACE NAME CONTEXT OFF';
Events can also use the alter session command at the session level to enable:
Alter Session Set Events 'Event Trace Name Context Forever, Level Level';
Use the following command from the session at the SESSION level:
Alter session set events 'Event TRACE NAME CONTEXT OFF'
Events in other sessions to enable with ORADEBUG:
Implement Enable in a process:
ORADEBUG EVENT TRACE NAME CONTEXT Forever, Level Level
Enable in a process:
ORADEBUG SETORAPID 8 (PID process number)
ORADEBUG EVENT TRACE NAME CONTEXT Forever, Level Level
The following command disable:
ORADEBUG EVENT TRACE NAME Context Off Implements Enable in Session:
ORADEBUG session_event Event TRACE NAME CONTEXT Forever, Level Level
Implement Disable in Session:
ORADEBUG session_event event trace name context off
Events can also achieve enable and disable with dbms_system.setev bags.
(To get SID and Serial # from the V $ Session view before doing
Use the following way: Execute dbms_system.set_ev (SID, Serial #, Event, Level, '')
Such as Execute DBMS_SYSTEM.SET_EV (9, 29, 10046, 8, ');
To disable, change the Level to 0, such as: Execute dbms_system.set_ev (9, 29, 10046, 0, ');
Listing All Events: (list all available Events)
Most of Events Number ranges from 10,000 to 10999. You can use the following command dump out
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;
/
In the UNIX system, the Message file is under the bottom directory $ oracle_home / rdbms / mesg / oraus.msg
Message file under the NT system $ Oracle_Home / Rdbms / MESG / ORAUS.MSG
Listing Enabled Events (Listing Enabled Events)
Use the following command to list the current session already enabled Events:
SET ServerOutput on
Declare
l_level number;
Begin
For l_eventin 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;
/
References of Events: (red is most commonly used and more useful for DBA)
Event 10013 - Monitor Transaction Recovery ---- Tracking Transaction Recovery at Startup
Alter Session Set Events '10013 Trace Name Context Forever, Level 1';
Event 10015 - Dump Undo Segment Headers ---- Do DUMP Return Section Information after the transaction is restored
Alter Session Set Events '10015 Trace Name Context Forever, Level 1'
Event 10032 - Dump Sort Statistics ---- Dump Sort Statistics, Level 10 is the most detailed ALTER SESSION set events '10032 Trace Name Context Forever, Level 10';
Event 10033 - Dump Sort Intermediate Run Statistics-Level 10 (do not understand)
Alter Session Set Events '10033 Trace Name Context Forever, Level 10';
Event 10045 - Trace Free List Management Operations - Tracking FreeList
Alter Session Set Events '10045 Trace Name Context Forever, Level 1';
Event 10046 - Enable SQL Statement Trace - Tracking SQL, has a statistics of execution plans, bonding variables, and waiting for Level 12
Alter Session Set Events '10046 Trace Name Context Forever, Level 12';
Level Level Reference as follows:
Level
Action
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 - When analyzing the SQL statement, the selection of the DUMP outlet optimizer, the level Level 1 is most detailed
Alter Session Set Events '10053 Trace Name Context Forever, Level 1';
Refer to Level:
Level
Action
1
Print statistics and computations
2
Print Computations ONLY
Event 10060 - Dump Predicates --- (Reference http://www.juliandyKe.com/)
Event 10065 - Restrict Library Cache Dump Output for State Object Dumps- (Reference http://www.juliandyKe.com/)
Event 10079 - Dump SQL * Net Statistics --- Dump SQL * NET statistics
Alter Session Set Events '10079 Trace Name Context Forever, Level 2'
Event 10081 - Trace High Water Mark Changes - Tracking HWM Changes
Alter Session Set Events '10081 Trace Name Context Forever, Level 1';
Event 10104 - Dump Hash Join Statistics-Dump Hash Join statistics, with Level 10
Alter Session Set Events '10104 Trace Name Context Forever, Level 10';
Event 10128 - DUMP Partition Pruning Information - Dump Partition Table Information
Alter Session Set Events '10128 Trace Name Context Forever, Level Level'; Level Reference
Level
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
In 9.0.1 or later, the following table is required after Level 2:
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 --- Dump
Alter Session Set Events '10200 Trace Name Context Forever, Level 1'
Event 10201 - Dump consistent read undo application --- (do not understand)
Alter Session Set Events '10201 Trace Name Context Forever, Level 1';
Event 10220 - dump changes to undo header-dump out of UNDO header information
Alter Session Set Events '10220 Trace Name Context Forever, Level 1'
Event 10221 - Dump undo changes-dump undo change
Alter Session Set Events '10221 Trace Name Context Forever, Level 7';
Event 10224 - Dump Index Block Splits / Deletes-DUMP Code Bill Split and Delete Information
Alter Session Set Events '10224 Trace Name Context ForeVer, Level 1';
Event 10225 - Dump changes to dictionary management extents - DUMP Out of Row Cache, Dictionary Dictionary of ExtensS
Alter Session Set Events '10225 Trace Name Context Forever, Level 1'
Event 10231 - Set the damaged data block (when doing Exp, if there is a bad block, set this if there is a bad block, set this, so that some data can be used)
Alter System Set Events '10231 TRACE NAME CONTEXT ForeVER, Level 10'
EVENT 10241 - DUMP Remote SQL EXECUTION-DUMP Remote SQL statement execution information
Alter Session Set Events '10241 Trace Name Context Forever, Level 1'; Event 10246 - Trace PMON Process --- can only do in Init.ora, you can't do with ALTER SYSTEM
Event = "10246 TRACE NAME CONTEXT Forever, Level 1"
Event 10248 - TRACE DISPATCHER Processes --- in Init.ora (9 IDUMP to UDUMP Directory)
Event = "10248 TRACE NAME CONTEXT Forever, Level 10"
Event 10249 - TRACE SHARED Server (MTS) Processes --- in Init.ora (9 IDUMP to UDUMP Directory)
Event = "10249 TRACE NAME CONTEXT Forever, Level 10"
Event 10270 - Debug Shared Cursors- (do not understand)
Event = "10270 TRACE NAME CONTEXT Forever, Level 10"
Event 10299 - debug prefeetching --- (Reference http://www.juliandyKe.com/)
Event = "10299 TRACE NAME CONTEXT Forever, Level 1"
Event 10357 - Debug Direct Path --- (Reference http://www.juliandyKe.com/)
Alter Session Set Events '10357 Trace Name Context Forever, Level 1'
Event 10390 - Dump Parallel Execution Slave Statistics - (Reference http://www.juliandyKe.com/)
Alter Session Set Events '10390 Trace Name Context Forever, Level Level'
Event 10391-Dump Parallel Execution Granule Allocation ---
(Refer to http://www.juliandy.COM/)
Alter Session Set Events '10391 Trace Name Context Forever, Level Level';
Event 10393 - Dump Parallel Execution Statistics - (Reference http://www.juliandyKe.com/)
Alter Session Set Events '10393 Trace Name Context Forever, Level 1';
Event 10500 - TRACE SMON Process - in Init.ora
Event = "10500 TRACE NAME Context Forever, Level 1"
Event 10608 - Trace Bitmap Index Creation - Tracking two-bit map index
Alter Session Set Events '10608 Trace Name Context Forever, Level 10'
Event 10704 - TRACE ENQUEUES - Tracking Query
Alter Session Set Events '10704 Trace Name Context Forever, Level 1';
Event 10706 - Trace Global Enqueue Manipulation- (refer to http://www.juliandy.com/)alter session set events '10706 Trace Name Context Forever, Level 1';
Event 10708 - Trace Rac Buffer Cache - Buffer Cache tracking RAC
Alter Session Set Events '10708 Trace Name Context Forever, Level 10';
Event 10710 - Trace Bitmap Index Access - (Reference http://www.juliandyKe.com/)
Alter Session Set Events '10710 Trace Name Context Forever, Level 1';
Event 10711 - Trace Bitmap Index Merge Operation-
Alter session set events '10711 Trace Name Context Forever, Level 1'
Event 10712 - Trace Bitmap Index or Or Operation-
Alter Session Set Events '10712 Trace Name Context Forever, Level 1';
Event 10713 - Trace Bitmap Index and Operation-
Alter Session Set Events '10713 Trace Name Context Forever, Level 1'
Event 10714 - Trace Bitmap Index Minus Operation-
Alter Session Set Events '10714 Trace Name Context Forever, Level 1';
Event 10715 - Trace Bitmap Index Conversion To Rowids Operation-
Alter Session Set Events '10715 Trace Name Context Forever, Level 1';
Event 10716 - Trace Bitmap Index Compress / Decompress-
Alter Session Set Events '10716 Trace Name Context Forever, Level 1';
Event 10717 - Trace Bitmap Index Compact-
Alter Session Set Events '10717 Trace Name Context Forever, Level 1';
Event 10719 - TRACE Bitmap Index DML-
Alter Session Set Events '10719 Trace Name Context Forever, Level 1';
Event 10730 - TRACE FINE Grading Access Predicates-
Alter Session Set Events '10730 Trace Name Context Forever, Level 1';
Event 10731 - TRACE CURSOR STATEMENTS
Alter Session Set Events '10731 Trace Name Context Forever, Level Level';
Levels Are:
Level
Action
1
Print Parent Query and SubQuery
2
Print SubQuery ONLY
Event 10928 - Trace PL / SQL EXECUTION-
Alter Session Set Events '10928 Trace Name Context Forever, Level 1';
Event 10938 - Dump Pl / SQL EXECUTION STATISTICS-
Alter Session Set Events '10938 Trace Name Context Forever, Level 1';
Some other Events:
Alter session set events 'immediate trace name flush_cache'; - Flush the Buffer Cache
Source http://www.juliandyKe.com/
The above is some reference materials when doing DBA.
