Appendix A: Oracle Database 10g Diagnostic Events
Events in Oracle can be broadly classified into two groups: wait events and diagnostic events. This appendix explores Oracle diagnostic events.
Oracle Diagnostic Events
Oracle diagnostic events can be considered the most powerful tools for DBAs who are involved in debugging. Most of these events are primarily used to produce additional diagnostic information, to change Oracle ‚ s behavior, and to trace the inner workings of Oracle because these events can enable some of the undocumented features of Oracle.
| Note ‚ | Diagnostic events should be handled with care because they can change the default behavior of the Oracle Database engine and will render the database useless and unsupported if used improperly. |
Types of Diagnostic Events
Oracle diagnostic events can be classified in four major categories based on their usage as discussed in the following sections.
Immediate Dump Events
Immediate dump events will dump the diagnostic information in a trace file as soon as the command is issued. Normally immediate dumps are used to dump the system state, process state, and file headers (controlf, redo_hdrs, file_hdrs). Immediate dumps cannot be invoked by setting the EVENT parameter in the init.ora file. That is because the parameter file is read only during the instance startup. Immediate dumps can be better triggered by either ALTER SESSION SET EVENTS command or the oredebug utility.
alter session set events 'immediate trace name systemstate level 8';
The preceding command creates a trace file, which contains detailed information about every Oracle process active on the system, as well as the details about the resources held/ requested by those processes. Oracle Support often requests the systemstate dump while debugging the database hang issues.
Another example of an immediate dump is shown here:
alter session set events 'immediate trace name controlf level 10';
More details about the different types of dumps are available in Appendix C.
On Error Dump Events
On error dump events are similar to immediate dump events but they are invoked only when the error occurs. They are used to dump the system state, process state, or error stack when a particular Oracle error occurs. On error dumps are normally set using the EVENT parameter in the init.ora file. Following example shows an entry in the init.ora file to dump the error stack to a trace file when ORA-04020 (deadlock detected when waiting for an object) is encountered . Usually the level is set between 1 and 10. It controls the amount of information written to the trace file.
event= 4020 trace name errorstack level 1
Following are valid values for the level for an error stack dump:
-
1 Error stack and function call stack
-
2 Level 1 plus process state
-
3 Level 2 plus the context area (with cursors )
On error dumps are mostly used to identify the cause of the particular error, and Oracle Support may ask you to dump the error stack for further investigation. For example, to diagnose the shared pool memory leak (ORA-04031) will need the following event set in the parameter file:
event=4031 trace name error stack level 10
Change Behavior Events
Change behavior events are normally set using the EVENT parameter in the init.ora file. These events are very powerful events and should be used with care. These events are used to enable or disable certain functionalities of the Oracle kernel. Unlike other diagnostic events, these events do not have level numbers because these events do not create a trace file with diagnostic information written to it.
Change behavior events have an additional keyword: forever . Without the forever keyword, the event is fired only once and no subsequent actions are triggered. Change behavior events use a reserved set of numbers to bring about the change in Oracle ‚ s behavior.
For example, the following event disables the automatic shrinking of rollback segments by SMON process, which usually happens every 12 hours:
event = "10512 trace name context forever"
Since change behavior events are used to enable or disable certain features of Oracle RDBMS, they cause potential data loss or data corruption if used incorrectly. They can also be used to change Oracle kernel operations. For example, the event 10170 can be used to change the bitmap index costing algorithms.
Bitmap index access cost is calculated as the sum of the index access cost and table access cost for those blocks. Here index access cost will be a function of blevel and the number of leaf blocks containing the key. Once the rowids are fetched from the leaf blocks, the bitmap is constructed and table access cost is estimated. The table access cost is calculated based on the number of blocks to fetch to get all the keys. This mainly depends on the selectivity.
In the old costing model, it is assumed that all the keys could be found in same block. That is, the number of block visits is calculated by dividing the number of rows by the rows per block. So the number of blocks multiplied by the selectivity of a block is assumed to be equal to the total number of rows satisfying the condition.
For example, if you want to retrieve 100 rows from a table that has 10,000 blocks with an average of 10 rows per block, the old costing model will compute 10 blocks as the I/O cost (100 rows/10 rows per block). So the table access I/O cost is approximately 10. With the enhanced costing model, it assumes 80 percent of the rows are in the same block and the remaining 20 percent of the rows are split across all blocks (which is quite possible). In this case, the cost will be (0.8*100/10) + (0.2*10000) = 8+2000 = 2008. The difference is quite significant.
Let us take another case where an index has 100,000 rows from a table with an average of 50 rows per block (for a total of 2,000 blocks) and the result set expects 1,000 rows. In the old model, the I/O cost will be 20. In the new costing model, the cost will be (0.8*20) + (0.2*2000), which is 56 blocks.
In most cases, the enhanced (new) costing model, which is based on the Watkins formula, works reasonably well in estimating the bitmap access costs. However, the old model outperforms the new model when partitioned tables are used and when star transformation is used. In these cases, most of the rows (or all of them) will be from the same partition, and the old costing model will be more suitable than the new enhanced costing approach. Sometimes the new costing model artificially inflates the bitmap index cost where bitmap indexes could be used for transformation. The old costing mode can be enabled by setting the event 10170.
The preceding is a simple nondestructive example of the change behavior type events. There are many more change behavior events, but they are potentially dangerous to the databases. These events should be used in accordance with Oracle Support.
Process Trace Events
Process trace events are used to get additional diagnostic information when the process is running. These events are normally harmless, and they do generate a trace file. For user processes, the trace file is created in the directory defined by USER_DUMP_DEST parameter in the init.ora file. The trace file for background processes will be written to the directory defined by the BACKGROUND_DUMP_DEST parameter in the init.ora file . The level keyword is used to control the amount of diagnostic information written to the trace file.
For example, the following event is used to trace the SQL statements executed in the session:
SQL> alter session set events '10046 trace name context forever, level 1';
Event levels for event code 10046 are
-
1 Enable SQL statement tracing (the default if no level is specified)
-
4 As level 1 plus bind variable information
-
8 As level 1 plus wait event statistics
-
12 As level 1 plus bind variables plus wait statistics (highest level)
Oracle trace event 10046 is the most popular event among the performance practitioners . There is an enhancement request pending with Oracle Corporation (Req # 590463, EXTERNALISE THE EVENT 10046 BY A PARAMETER), and this event may be externalized by a parameter in future versions.
Cost Based Optimizer (CBO) operations can be traced using the Oracle trace event 10053. This event dumps the inner workings of CBO into a trace file, which is particularly helpful in understanding why CBO is not using the index or why CBO has taken a certain access path when the other path seems optimal.
Setting Diagnostic Events
Oracle diagnostic events can be set using the EVENT parameter in the init.ora file. You can also dynamically set the events using ALTER SESSION SET EVENTS command through SQL*Plus. Events can also be set using the dbms_system.set_ev procedure and the oradebug utility.
In the previous sections, you saw a few examples showing the use of EVENT parameter in the init.ora file and the ALTER SESSION command to set a particular event.
Multiple events can be set in the init.ora parameter file. When using only one EVENT parameter entry, a colon must separate multiple events, and they must all appear on a single line. However, a line continuation character (\) can be used to list them on multiple physical lines.
When using multiple EVENT parameter entries, they must be grouped together, and no other initialization parameter can be embedded within the EVENT parameter entries.
Following are all valid examples of setting two events:
event="\
4031 trace name context forever, level 10:\
10046 trace name context forever, level 12"
event="4031 trace name context forever, level 10: 10046 trace name context forever, level 12"
event="4031 trace name context forever, level 10"
event="10046 trace name context forever, level 12"
In the following example, only the second event (10512) is set and the first one is ignored:
# To start SQL trace and Disable SMON posting
event="10046 trace name context forever, level 12"
user_dump_dest=/d00/app/oracle/admin/PROD/udump
event="10512 trace name context forever"
Setting Events Using DBMS_SYSTEM Package Procedure
You can set events using an Oracle supplied package procedure called DBMS_SYSTEM.SET_EV. The DBMS_SYSTEM package must be created by running Oracle-supplied SQL script titled dbmsutil.sql while logged in as SYS user. The script is available under $ORACLE_HOME/rdbms/admin directory. Privileged users should be given execute permission on the package.
The procedure set_ev is defined as follows :
procedure set_ev(si binary_integer, se binary_integer,
ev binary_integer, le binary_integer, nm varchar2);
Table A-1 describes the parameters used in the set_ev procedure.
| Parameter | Description |
|---|---|
| si (SID) | Session ID of the target session |
| se (Serial#) | Serial # of the target session from V$SESSION |
| ev | event_code or event number, (for example, 10053 to trace CBO, 65535 for ‚IMMEDIATE') |
| le | Level of information to dump. The higher the level, the more details you get. In certain cases (block dump, for example), level represents the address space. |
| nm | What to do when the event is triggered. For example, dump the error stack. Default is ‚' (NULL) which means ‚“context forever. ‚½ |
You can use the following syntax to execute this procedure:
exec sys.dbms_system.set_ev (sid,serial#,event_code,level,'event_action');
The following example shows how to enable event 10046 at level 12 in session 10 with serial 63:
execute dbms_system.set_ev(10,63,10046,12,);
The following example shows how to disable it:
execute dbms_system.set_ev(10,63,10046,0,);
Setting Event Using oradebug Utility
The undocumented utility oradebug can be used to set an event for someone else ‚ s session (or for your own session).
The following example shows how to use oradebug to set an event in your own session:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 8
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/product/10.1.0/admin/V10HP/udump/v10hp2_ora_9463.trc
An event can be set in someone else ‚ s session using oradebug utility when you know the session ‚ s SPID value from V$PROCESS view.
The following example shows how to trace a session with SPID value of 3574:
SQL> oradebug setospid 3574
Oracle pid: 15, Unix process pid: 3574, image: oracle@hptest (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever, level 8
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/V10HP/udump/v10hp_ora_3574.trc
SQL>
Event Specification Syntax
You must follow a particular syntax when setting events. The parameter keyword name is "event", with its value of string type. The string value is typically quoted and has the following syntax:
<event name> <action>{:<event name> <action>}. . . The ‚event name ‚ is a symbolic name associated with the event, or, optionally an event number. There is a special name: "immediate", for an immediate and unconditional event. Oracle does not wait for somebody to post it. If a number is specified, it is taken to be an Oracle error number. If a name is specified, the parser looks it up in an event name table if it is not "immediate".
The ‚action ‚ is defined as the ‚action keyword ‚ plus the ‚action qualifiers ‚ . The ‚action ‚ keyword can be either ‚trace ‚ , ‚ debugger ‚ or ‚crash ‚ . The ‚action qualifier ‚ depends on the ‚action ‚ keyword.
Table A-2 summarizes the syntax for various types of diagnostics events.
| Type | Trace Syntax | ‚ | ‚ | ‚ | ‚ |
|---|---|---|---|---|---|
| ‚ | < event name > | < action > | ‚ | ‚ | ‚ |
| ‚ | ‚ | < action keyword > | ‚name' | < trace name > | < action qualifier > |
| Immediate dumps | immediate | trace | name | blockdump | level 432043242 |
| On error | 4031 | trace | name | heapdump | level 10 |
| Change behavior | 10512 | trace | name | context | forever level 12 |
| Trace something | 10046 | trace | name | context | forever level 8 |
A special trace name is "context", which means a context-specific trace that does not cause a debug dump operation to be invoked. Instead, it returns to the caller who posted the event and informs it whether context tracing is active for that event, and if so, what the trace level is. The trace level is used internally by the dump routines to control the level of detail in the dump. By convention, the level of detail increases with the level number, with the lowest level being 1. The caller is then responsible for invoking whatever dump operation it wants to, with whatever parameters are relevant.
Another special trace name is "all", which expands to mean all trace names that were declared at compile time through the "ksdtradv" macro, as well as the (last) "context" trace.
If more than one trace is associated with an event, at most one can be "context". Correspondingly, the last action associated with an event posting is to return the level number associated with the context-dependent trace. The event numbers need to be unique among different callers . Additionally, for trace specifications, you can specify an event named "immediate". For this case, no trace qualifiers except the trace level qualifier must be specified.
A duplicate event specification with the same action supercedes the old specification. Specifications of different actions for the same event may coexist, with the action taken according to the following precedence:
-
Context-independent traces in order of declaration
-
Context-specific trace
-
Debugger call
-
Oracle crash
If the keyword immediate is specified as the first keyword in the event syntax, then it is an unconditional event and the structure specified by the next qualifiers is dumped immediately after the command is issued.
The keyword trace indicates that the output will be dumped to the trace file. The other keywords in the second parameters are crash and invoke the debugger and are usually used internally by Oracle Development and Oracle Support.
If the keyword immediate is not used as a first argument in the event specification, you need to indicate how long the specified tracing should be enabled. Specifying the keyword forever will keep the tracing enabled for the lifetime of the session, or instance, depending on whether the event is set from the init.ora file or at a session level using the ALTER SESSION command. The last optional keyword, level, defines the granularity of the trace. If no level is specified, the default minimum tracing is enabled. Setting level to zero will turn the trace off. In some cases, the level takes some other values, for example a decimal data block address during data block dumps and index tree dumps. It specifies bit patterns representing the heap number in heap dumps.
Setting Multiple Actions for an Event
Multiple actions can also set for a single event. The following multiple actions are possible:
-
TRACE Dump the trace file
-
CRASH Crash the process
-
INVOKE THE DUBUGGER Invoke the debugger
Table A-3 shows the parameters supported by TRACE. Table A-4 shows the parameters supported by CRASH. Table A-5 shows the parameters supported by DEBUGGER.
| Parameter Name | Description |
|---|---|
| Level n | The level keyword is used in multiple contexts. Normally, the higher the level the more details you get. |
| After n times | Starts the trace after the specified event has occurred n times. |
| Lifetime n | Traces only n number of times. |
| Forever | Continues the action forever. |
| Type increment | Sets trace level to maximum. |
| Type decrement | Disables the trace level (level 0). |
| Off | Turns off tracing. |
| Parameter Name | Description |
|---|---|
| After n times | Crashes the process after n times. |
| Off | Turns off CRASH (disables). |
| Caution ‚ | The details about CRASH and DEBUGGER are shown purely for academic purpose. Using these parameters in a production database will cause a lot of overhead to the Oracle Database engine and may lead the database into an unsupported state. |
In some cases, level represents a different context, such as block dumps, where it indicates the decimal block address of the DBA. In other cases it represents the object ID (for example, in DROP_SEGMENTS events it represents the tablespace number).
Internal Workings of the Events
Process events and session events group internal events. Process events are events initialized during the startup time of that process, whereas session events can be started and controlled anytime using the ALTER SESSION command. During checking for what events are set, the session events are checked first, followed by process events. The following simple pseudo code explains the search order of events:
if <session_event_set> is YES
execute session_event
else if
<process_event_set> is YES
session inherits the process event.
No event set.
| Parameter Name | Description |
|---|---|
| After n times | Starts the debugger after n times. |
| Lifetime n | Invokes the debugger after n times and turns off the debugger after that. |
| Forever | Invokes the debugger every time the event occurs. |
| Off | Turns off CRASH (disables). |
An event set into one session is not visible to other sessions because the information about that event is kept in the Private Global Area (or Process Global Area, or simply PGA), which is not shared among the sessions. Because of this private nature, there is no easy way to find the existence of a tracing event from another session other than going to the trace files. However, you can use an undocumented utility in the dbms_system.read_ev package and retrieve the information for the current session.
A simple example of dbms_system.read_ev call follows:
set serveroutput on
declare
event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event 'to_char(i)' set at level '
to_char(event_level));
end if;
end loop;
end;
/
Other than using the dbms_system.read_ev , the oradebug dump events command can be used to get the details about the events set in a particular session. The following example illustrates the use of this command.
To find out events set in your own session:
SQL>oradebug setmypid
Statement processed.
SQL> oradebug event 10053 trace name context forever, level 1
Statement processed.
SQL> oradebug dump events 1
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/V10HP/udump/v10hp_ora_11262.trc File /u01/app/oracle/admin/V10HP/udump/v10hp_ora_11262.trc contains following:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.1.0
System name: HP-UX
Node name: hptest
Release: B.11.11
Version: U
Machine: 9000/889
Instance name: V10HP
Redo thread mounted by this instance: 2
Oracle process number: 25
Unix process pid: 11262, image: oracle@hptest (TNS V1-V3)
*** 2004-03-15 12:31:09.546
*** SERVICE NAME:(SYS$USERS) 2004-03-15 12:31:09.525
*** SESSION ID:(154.8244) 2004-03-15 12:31:09.525
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
4C0200B8 10053 1 4c020148 0 0
TR Name TR level TR address TR arm TR life TRtype
CONTEXT 1 0 -1 2 135266304
Numbers in bold in the preceding output show that event 10053 was set at trace level 1 by this session.
| Note ‚ | The preceding example is for illustration purposes only. To use oradebug you must connect as sysdba . |
If you want to find out if someone else ‚ s session has set any event, you can use the session ‚ s OSPID (or ORAPID) number with oradebug as shown in the following example, where session 153, with an OSPID 11687, is checked if it had set any events:
SQL> oradebug setospid 11687
Oracle pid: 27, Unix process pid: 11687, image: oracle@hptest (TNS V1-V3)
SQL> oradebug dump events 1
Statement processed.
The trace file contained following:
*** 2004-03-15 12:52:03.518
*** SERVICE NAME:(SYS$USERS) 2004-03-15 12:52:03.515
*** SESSION ID:(153.7407) 2004-03-15 12:52:03.515
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
4C020160 10170 1 4c0201f0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 1 0 -1 2 1207959552
Here event 10170 is set at trace level 1 for the session.
Oracle diagnostic events should be used with care and under the supervision by Oracle Support. They can be used to get detailed trace information from the Oracle kernel during debugging. Please keep in mind that using such events is unsupported by the Oracle Corporation. These events should be never set in production databases unless advised by Oracle Support. Happy (but cautious) tracing!
EAN: 2147483647
Pages: 114