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
max_dump_file_size=unlimited;
SQL> alter session set events 'deadlock trace
name hanganalyze_global';
SQL> alter session set events '10046 trace
name context forever, level 12';
Or this
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'
The
docs note that Oracle deadlocks are transient:
ORA-00060: deadlock detected while waiting for
resource
Cause: Transactions deadlocked one another while
waiting for resources.
Action: Look at the
trace file to see the transactions and resources involved. Retry if
necessary.
Hence, your final solution might be trying to
avoid the deadlocks.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
