RE: determining bind values in deadlock situations

  • From: "Tanel Poder" <tanel@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 4 Mar 2009 02:37:33 +0200

On a related topic,
 
It's possible to make Oracle to automatically dump more stuff when ORA-60 or
any other error happens, with KSD diagnostic events...
 
You can issue something like this at system level for example (after
thinking what you want to achieve and what events levels are safe):
 
SQL> alter session set events '60 trace name hanganalyze_global level 4,
forever; name systemstate level 522, lifetime 1';
 
Session altered.

That would give you global hanganalyze every time the error occurs and a
local systemstate dump only the first time it happens in a session. System
state dumps process stacks thanks to level 512 + 10, that can be useful for
diagnosing low-level deadlocks and hangs.
 
Btw, knowing the current SQL statement and bind variable values for both
sessions might still not give the full picture of the root cause of the
deadlock. You know what for the sessions are currently waiting, but you
don't necessarily know when, why and by which statement these locks were
taken for (and why they are still being held).
 
I just published a related blog entry about the diagnostic event setting
syntax and some more complex constructs:
 
http://blog.tanelpoder.com/2009/03/03/the-full-power-of-oracles-diagnostic-e
vents-part-1-syntax-for-ksd-debug-event-handling/
 
 
--
Regards,
Tanel Poder
 <http://blog.tanelpoder.com/> http://blog.tanelpoder.com





  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Scott
Sent: 03 March 2009 20:43
To: Vlado.Barun@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: determining bind values in deadlock situations


The big question is how do you know you are getting a deadlock?

ORA-60, RAC or not will still get generated. It may take longer in RAC
because the detection method does change. However you can still get
non-table deadlocks in RAC that are not always dumped in to a tracefile.
Then you need to run hanganalyze or a systemstate dump and look for open
chains. There serveral bugs in 10g that related to library cache related
deadlocks that are not always indicated in a tracefile.

Scott



  _____  

From: "Barun, Vlado" <Vlado.Barun@xxxxxxx>
To: Jared Still <jkstill@xxxxxxxxx>
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 2, 2009 7:21:12 PM
Subject: RE: determining bind values in deadlock situations



Jared, 

 

Was your test done in a non-RAC environment? 

I can find the rowid's the way you mentioned in a non-RAC environment, but
not in a RAC environment. 

 

There is no trace file generated for a deadlock in udump on any of my RAC
nodes.

 

Are you able to get a deadlock trace file in a RAC environment?

 

Thank you for the time you are spending on this.

 

Regards,

 

Vlado Barun, M.Sc.

Sr. Manager, Database Engineering and Operations

Jewelry Television

Mobile: 865 335 7652

Email: vlado.barun@xxxxxxx

 

From: Jared Still [mailto:jkstill@xxxxxxxxx] 
Sent: Monday, March 02, 2009 1:28 PM
To: Barun, Vlado
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: determining bind values in deadlock situations

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2009