Re: How to find the exact SQL locking others?
- From: Alex Fatkulin <afatkulin@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Mon, 2 Jul 2012 12:17:13 -0400
You don't need triggers for that...
if session issues 1:
update t1 set c1=y where id =x <= this will later block people
update t2 set ....
update t3 set ....
update t4 set....
session 2:
update t1 set c1=z where id=x <= this gets blocked on the very first
statement issued by session 1
sql_id/prev_sql_id for session 1 will be useless. The first statement
executed by session 1 might event be out of shared pool by now!
On Mon, Jul 2, 2012 at 11:45 AM, Niall Litchfield
<niall.litchfield@xxxxxxxxx> wrote:
> 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.
>
--
Alex Fatkulin,
http://afatkulin.blogspot.com
Enkitec,
http://www.enkitec.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
