Re: How to find the exact SQL locking others?
Date: Tue, 3 Jul 2012 19:23:18 +0200
Message-ID: <CALH8A92GdhHstHnYi7O73aABffgLMPaC1zDz09bFwDtOBC-JNw_at_mail.gmail.com>
it would be an enhancement request at all. I can either think of a fixed array of n SQL_IDs per transaction - this can waste some space for short transactions and is allways too small for transactions with many different statements. But it's easy to code and cheap regarding latches/mutex.
Other way is to create a real list, but this might hammer SGA a lot. I would prefer a list of arrays - so new memory structures are needed rarely (and only for 'big' transactions) but still all SQL_IDs are stored in order of their execution.
For some reasons Oracle instrumentation seems to be very session-focused. Or is it just me and the way I learned it?
Martin
On Tue, Jul 3, 2012 at 6:28 PM, Leyi Kamus Zhang <kamusis_at_gmail.com> wrote:
> Hi Yong
>
> Yes, I agree with you, It definitely helps application troubleshooting.
> If Oracle can store all the SQL text somewhere for active transactions
> (for example, with Idle event over 5 minutes but still not commit,
> just like another type v$session_longops), it will help us a lot.
>
> --
> Kamus <kamusis_at_gmail.com>
>
> Visit my blog for more : http://www.dbform.com
> Join ACOUG: http://www.acoug.org
>
>
> On Wed, Jul 4, 2012 at 12:11 AM, Yong Huang <yong321_at_yahoo.com> wrote:
> >> I tried to answer this question some time ago and came to the
> conclusion:
> > you can't!
> >
> > I agree. There's no guaranteed way. This is equivalent to trying to
> > disassemble binary executable code back to the exact source code; some
> > tokens in the text will definitely be lost. V$open_cursor will not retain
> > the original DML unless the original session never executed any other SQL
> > after that so there's still a "breakable parse lock" on the cursor. If
> > keeping that SQL is so important, the application may need to log it to a
> > table or file, or use dbms_shared_pool to "keep" it in shared pool
> > (cumbersome and not elegant). I wish Oracle would store these SQLs
> > somewhere, maybe store the last one or two in v$transaction. It
> definitely
> > helps application troubleshooting.
> >
> > Yong Huang
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 03 2012 - 12:23:18 CDT