Question: In my RAC database I am getting
the ORA-0600 deadlock detected error:
ORA-00060: deadlock detected while waiting for resource
The problem is deadlock trace file is not being generated.
How do I create a trace for for a RAC deadlock condition?
Answer: First, see my notes on how to
create and analyze trace files. I would set a trace event,
using any of these alter session commands, as needed:
SQL> alter session set
SQL> alter session set events 'deadlock trace
SQL> alter session set events '10046 trace
name context forever, level 12';
example. A deadlock is event 60:
SQL> alter session set events '60 trace name hanganalyze level 4';
For RAC, you may want systemstate=266:
SQL> alter session set events '60 trace name
hanganalyze_global ; name systemstate level 266'
docs note that Oracle deadlocks are transient:
ORA-00060: deadlock detected while waiting for
Cause: Transactions deadlocked one another while
waiting for resources.
Action: Look at the
trace file to see the transactions and resources involved. Retry if
Hence, your final solution might be trying to
avoid the deadlocks.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.