Re: How to find the exact SQL locking others?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 2 Jul 2012 16:45:53 +0100
Message-ID: <CABe10sYYQyF+JFHCnQRrLSoN3Efe1myVLHEcdZKhAw1qSu7=rA_at_mail.gmail.com>

Also what do you/we want v$sql to contain if I run UPDATE T1 SET C1 = 'NEW VALUE' WHERE ID = 1; and there are one or more triggers on T1 that modify other tables and hence block people. I'd suggest that v$sql containing the sql_id of the SQL issued by the session is sensible, but that it may not be that SQL that is causing the issues. In general reporting the blocking session *is *enough to identify what is going on, I suspect that where it isn't enough there will usually be one or more confounding factors that make identification of the exact SQL somewhat problematic.

On Mon, Jul 2, 2012 at 4:24 PM, David Fitzjarrell <oratune_at_yahoo.com> wrote:

> And V$SESSION also proviees PREV_SQL_ID to report the prior sql statement
> from that session. That being said I'm coming up with a NULL value for
> SQL_ID from an active blocking session. Apparently SQL_ID isn't updated
> when I originally thought it would be.
>
> David Fitzjarrell
>
>
>
> ________________________________
> From: Alex Fatkulin <afatkulin_at_gmail.com>
> To: oratune_at_yahoo.com
> Cc: "martin.a.berger_at_gmail.com" <martin.a.berger_at_gmail.com>; "
> kamusis_at_gmail.com" <kamusis_at_gmail.com>; oracle_l <Oracle-L_at_freelists.org>
> Sent: Monday, July 2, 2012 7:30 AM
> Subject: Re: How to find the exact SQL locking others?
>
> SQL_ID from V$SESSION shows current SQL_ID for a session, a blocking
> session might be on a totally different statement (from the one which
> blocked someone else) when you look it up.
>
> If you're still not convinced, try the above example but flush your
> shared pool at the end.
>
> On Mon, Jul 2, 2012 at 9:21 AM, David Fitzjarrell <oratune_at_yahoo.com>
> wrote:
> > V$SESSION_BLOCKERS provides the information to identify the blocking
> session as it reports the sid and serial# of blocked sessions as well as
> the sid and serial# of the blocking session. This information along with
> SQL_ID from V$SESSION and the V$SQLAREA view would, as far as I can tell,
> report on the blocking SQL statement. So it appears that Oracle does keep
> track of this information (if not directly then indirectly).
> >
> > David Fitzjarrell
> >
>
> --
> Alex Fatkulin,
> http://afatkulin.blogspot.com
>
> Enkitec,
> http://www.enkitec.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


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

Received on Mon Jul 02 2012 - 10:45:53 CDT