RE: How to find the exact SQL locking others?
Date: Wed, 4 Jul 2012 09:58:25 -0400
Message-ID: <002801cd59ed$15c69730$4153c590$_at_rsiz.com>
JL's blog link is a brilliantly short and clear demo directly proving the point.
Further, it is instructive in demonstrating by implication that in order to be able to identify lock holding sql in the general case a list of incomplete transactions in which the sql had participated would have to be maintained and a count higher than zero would be the basis for refusing to boot the sql from memory. I haven't worked through the details of even the order of expense this might require or the latch heat potential of such a hypothetical counter, nor do I quite grok whether this could accrue to a deadly embrace amongst remaining shared pool size and expandability, existing in flight transactions, and the need to parse something new to clear space in the pool.
I suspect it is something that could be programmed, but I also suspect a better answer is a design framework for order of tables and rows for systems of changes (inserts, updates, deletes) and maintaining proper meta data for your applications suites so that a logical walk through the applications winnows the possibilities to a manageable number of statements that can then be instrumented (of course have the design framework in place in the first place might have led you to exactly the instrumentation you would now be adding.)
JL: brilliant! (no surprise there)
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Tuesday, July 03, 2012 5:04 PM
To: oracle_l
Subject: Re: How to find the exact SQL locking others?
"You can't" is the correct generic answer.
There is no guaranteed link between the locked row(s) and the statement.
In fact, as the following link shows
http://jonathanlewis.wordpress.com/2009/04/19/locking-sql/ , the statement
need not be in memory even though the rows are still locked.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Powell, Mark" <mark.powell2_at_hp.com> To: "oracle_l" <Oracle-L_at_freelists.org> Sent: Monday, July 02, 2012 5:51 PM Subject: RE: How to find the exact SQL locking others?
"You can't" is the answer I have seen Oracle support post on this question
in the past. However because the statement is uncommitted I would expect
that the cursor will be listed in v$open_cursor for the blocking session.
You just have to manually try to determine which open cursor is the one of
interest.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 04 2012 - 08:58:25 CDT