Database Reference
In-Depth Information
The call stack trace included in the trace file at event level 4 is less useful. It shows which C
function an ORACLE server process was in at the time a deadlock was detected. If you encounter
deadlocks in an application for the first time, it is a good idea to temporarily set event 10027 at
level 2 with
ALTER
SYSTEM
as follows:
SQL> ALTER SYSTEM SET EVENTS '10027 trace name context forever, level 2';
This will increase your chances of finding the root cause of deadlocks. If the setting shall
persist across instance startups, you need to use the initialization parameter
EVENT
.
EVENT="10027 trace name context forever, level 2"
As soon as you have obtained enough system state dumps for further analysis, you may
reduce the event level to 1. Since locks are released after an ORA-00060 trace file is written,
event 10027 at level 1 makes sure that sessions can respond more quickly. In my testing, I
observed that trace files at this level were one hundred times smaller than trace files with
default settings. The supported event levels and the trace information included at each level
are summarized in Table 5-1.
1
Table 5-1.
Event 10027 and Trace File Contents
Contents/Level
Default
Level 1
Level 2
Level 4
Cached cursors
yes
no
yes
yes
Call stack trace
no
no
no
yes
Deadlock graph
yes
yes
yes
yes
Process state
yes
no
yes
yes
yes, for all sessions
1
SQL statements
yes
yes
yes
Session wait history
yes
no
yes, for all sessions
yes
System state
no
no
yes
no
1.
SQL statements for all sessions are in the system state dump section.
