determining bind values in deadlock situations

Environment: 10.2.0.3 RAC/ASM on Solaris 10

I'm trying to find an efficient way to get the bind values for statements that 
are involved in a deadlock situation.

So far, I'm able to get the bind values for statements that are part of the 
session that encounters the ora-60 by setting event='name errorstack level 2, 
forever'.

The problem is forcing an errorstack on the other session that is involved in 
the deadlock, the one that does not get the ora-60.

I have come with several possibilities, but none of them seem to be even 
efficient or provide the correct bind values:

1. Write some code that parses the lmd0 trace, finds the session of interest 
and dump an errorstack.
Problem:  requires writing a lmd0 parser.

2. Enable sql level auditing on the tables of interest.
Problem: captures huge number of statements and that could cause other issues 
(ie storage, performance overhead,...).

3. Enable 10046 trace database wide
Problem: same as #2

4. AFTER SERVERERROR trigger that queries v$sql_bind_capture on when ora 60 is 
raised
Problem: bind values in v$sql_bind_capture are captured only every 15 minutes 
(default setting for _cursor_bind_capture_interval)

5. dump errorstack for all sessions
Problem: Don't know how to do that except for looping through gv$session and 
dumping error stacks one session at a time...

I have opened an SR, but the analyst is no help, he didn't even know that 
errorstack level 2 contains bind values...


Regards,

Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations
Jewelry Television
Mobile: 865 335 7652
Email: vlado.barun@xxxxxxx<mailto:vlado.barun@xxxxxxx>

Other related posts: