determining bind values in deadlock situations
Date: Fri, 20 Feb 2009 20:33:51 -0500
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:
- Write some code that parses the lmd0 trace, finds the session of interest and dump an errorstack. Problem: requires writing a lmd0 parser.
- 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,...).
- Enable 10046 trace database wide Problem: same as #2
- 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)
- 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...
Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations Jewelry Television
Mobile: 865 335 7652