Re: determining bind values in deadlock situations

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Tue, 3 Mar 2009 16:05:43 +0100
Message-ID: <486b2b610903030705p6889d1edpde7aa36d03bc216d_at_mail.gmail.com>

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'.

If you can get them like this you can set the errorstack instance wide:

alter system set events '60 trace name errorstack level 2';

Cheers

Stefan

---

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

On Sat, Feb 21, 2009 at 2:33 AM, Barun, Vlado <Vlado.Barun_at_jtv.com> wrote:

> 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_at_jtv.com
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 03 2009 - 09:05:43 CST