Re: How to find the exact SQL locking others?
Date: Wed, 4 Jul 2012 21:50:15 +0300
Message-ID: <CAMHX9J+D8j_cv41MpHw5PhEVEyCa1feKa-9xRxzR8b2aO7nS8w_at_mail.gmail.com>
*Resending due to overquote...*
Everyone else has already said why you can't see the "blocking SQL statement" ... it's because it's not a SQL statement which is blocking other sessions update, but a transaction (in form of row-level lock byte -> ITL entry -> Undo segment header slot). If Oracle had to keep track of every different SQL which has updated a row in a block, then you'd need much more space in the lock byte (or at least multiple different ITL entry variations) and that would be inefficient ....
Before filing an enhancement request with Oracle - I think what you really should be asking is "why hasn't the blocking transaction been committed yet"
On Wed, Jul 4, 2012 at 4:58 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> 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.
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 04 2012 - 13:50:15 CDT