Re: How to find the exact SQL locking others?

From: Leyi Kamus Zhang <kamusis_at_gmail.com>
Date: Thu, 5 Jul 2012 03:27:39 +0800
Message-ID: <CAPtFprp-de-11h-pRKgg=Jb3nbxa+n2+tHAqVKoTJE3B+j6vjA_at_mail.gmail.com>

Hi Tanel
Of course you are correct on why we can't find the SQL due to the oracle locking essential. I accept the truth, and indeed at most situation, knowing the locked object and rows is enough to investigate the issue cause. But I have to say, the most efficient way connect database and application is SQL, if we can know the SQL text, we can answer the question "why hasn't the blocking transaction been committed yet" more quickly - is a application design issue? or just a simple performance issue?

--
Kamus <kamusis_at_gmail.com>

Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org


On Thu, Jul 5, 2012 at 2:50 AM, Tanel Poder <tanel_at_tanelpoder.com> wrote:



> *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-l

>

>

>



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

Received on Wed Jul 04 2012 - 14:27:39 CDT