Re: How to find the exact SQL locking others?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oratune@xxxxxxxxx
- Date: Mon, 2 Jul 2012 16:45:53 +0100
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@xxxxxxxxx> 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@xxxxxxxxx>
> To: oratune@xxxxxxxxx
> Cc: "martin.a.berger@xxxxxxxxx" <martin.a.berger@xxxxxxxxx>; "
> kamusis@xxxxxxxxx" <kamusis@xxxxxxxxx>; oracle_l <Oracle-L@xxxxxxxxxxxxx>
> 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@xxxxxxxxx>
> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
