oracleevent相关操作.doc

oracle事件及相关操作介绍IntroductionThere 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 EventsEvents 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 102 - List events on consecutive lines e.g. event = 10248 trace name context forever, level 10 event = 10249 trace name context forever, level 10Note 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 levelFor example to set event 10046, level 12 in Oracle process 8 use ORADEBUG SETORAPID 8 ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12To disable an event in a process use ORADEBUG EVENT event TRACE NAME CONTEXT OFFTo enable an event in a session use ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL levelFor example ORADEBUG SESSION_EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12To disable an event in a session use ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFFEvents 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 EventsMost 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.msgTo print detailed event messages (Unix only) use the following script event=10000 while $event -ne 10999 do event=expr $event + 1 oerr ora $event doneListing Enabled EventsTo 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 Reference10013 - Monitor Transaction Recovery10015 - Dump Undo Segment Headers10032 - Dump Sort Statistics10033 - Dump Sort Intermediate Run Statistics10045 - Trace Free List Management Operations10046 - Enable SQL Statement Trace10053 - Dump Optimizer Decisions10060 - Dump Predicates10065 - Restrict Library Cache Output for State Object Dumps10079 - Dump SQL*Net Statistics10081 - Dump High Water Mark Changes10104 - Dump Hash Join Statistics10128 - Dump Partition Pruning Information10200 - Dump Consistent Reads10201 - Dump Consistent Read Undo Application10220 - Dump Changes to Undo Header10221 - Dump Undo Changes10224 - Dump Index Block Splits / Deletes10225 - Dump Changes to Dictionary Managed Extents10241 - Dump Remote SQL Execution10246 - Trace PMON Process10248 - Trace Dispatcher Processes10249 - Trace Shared Server (MTS) Processes10270 - Debug Shared Cursors10357 - Debug Direct Path10390 - Dump Parallel Execution Slave Statistics10391 - Dump Parallel Execution Granule Allocation10393 - Dump Parallel Execution Statistics10500 - Trace SMON Process10608 - Trace Bitmap Index Creation10704 - Trace Enqueues10706 - Trace Global Enqueue Manipulation10708 - Trace RAC Buffer Cache10710 - Trace Bitmap Index Access10711 - Trace Bitmap Index Merge Operation10712 - Trace Bitmap Index OR Operation10713 - Trace Bitmap Index AND Operation10714 - Trace Bitmap Index MINUS Operation10715 - Trace Bitmap Index Conversion to ROWIDs10716 - Trace Bitmap Index Compress / Decompress10717 - Trace Bitmap Index Compaction10719 - Trace Bitmap Index DML10730 - Trace Fine Grained Access Predicates10731 - Trace CURSOR Statements10928 - Trace PL/SQL Execution10938 - Trace PL/SQL Execution StatisticsEvent 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 LevelAction1Print SQL statements, execution plans and execution statistics4As level 1 plus bind variables8As level 1 plus wait statistics12As level 1 plus bind variables and wait statisticsEvent 10053 - Dump Optimizer 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 LevelAction1Print statistics and computations2Print computations onlyEvent 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;C1C21Table:2T13frofand4T1.C02=0The following table summarises the various operations that can be reported by this event OperationDescriptionfptconstFolding constantsfptrnumRemove ROWNUM predicatesfptwhrRemove all WHERE predicates except remaining ROWNUM predicatesfrofkks (rowid lookup)ROWID lookupfrofkksi (and-equal lookup)start keyfrofkkei (and-equal lookup)end keyfroiandindex-only predicatefrofkksmi (sort-merge)sort-merge keyfrosand (sort-merge)sort-merge predicatesfrojand (sort-merge)join predicatesfrofkksi (index start key)index start keyfrofkkei (index stop key)index end keyfrofand (hash part)table predicate (hash)froiand (index only filter)index only predicatefrofandtable predicatefroutandouter join predicatesEvent 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 ALTER SESSION SET EVENTS 10065 trace name context forever, level level;where level is one of the following LevelDescription1Address of library object only2As level 1 plus library object lock details3As level 2 plus library object handle and library objectLevel 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 level;Levels are LevelAction0x0001Dump pruning descriptor for each partitioned object0x0002Dump partition iterators0x0004Dump optimizer decisions about partition-wise joins0x0008Dump ROWID range scan pruning informationThere 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 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 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) 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 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 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 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 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 init.ora file using event = 10246 trace name context forever, level 1The 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 init.ora file using event = 10248 trace name context forever, level 10In 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.trce.g. JD92001_d000_1234.trcValid 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 init.ora file using event = 10249 trace name context forever, level 10In 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.trce.g. JD92001_s000_5678.trcValid 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 event = 10270 trace name context forever, level 10Event 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. event = 10299 trace name context forever, level 1Event 10357 - Debug Direct PathThis 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 StatisticsThis event can be used to dump parallel slave statistics ALTER SESSION SET EVENTS10390 trace name context forever, level level;Levels are (from messages) LevelAction0x0001Slave-side execution messages0x0002Coordinator-side execution messages0x0004Slave context state changes0x0008Slave ROWID range bind variables and xty0x0010Slave fetched rows as enqueued to TQ0x0020Coordinator wait reply handling0x0040Coordinator wait message buffering0x0080Slave dump timing0x0100Coordinator dump timing0x0200Slave dump allocation file number0x0400Terse format for debug dumps0x0800Trace CRI random sampling0x1000Trace signals0x2000Trace parallel execution granule operations0x4000Force compilation by slave 0Event 10391 - Dump Parallel Execution Granule AllocationThis 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) LevelAction0x0001Dump summary of each object scanned in parallel0x0002Full dump of each object except extent map0x0004Full dump of each object including extent map0x0010Dump summary of each granule generators0x0020Full dump of granule generators except granule instances0x0040Full dump of granule generators including granule instances0x0080Dump system information0x0100Dump reference object for the query0x0200Gives timing in kxfralo0x0400Trace affinity module0x0800Trace granule allocation during query execution0x1000Trace object flush0x2000UnknownEvent 10393 - Dump Parallel Execution StatisticsThis 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