Re: Block dump - Uncommitted TXN - Help Urgent

On 9/19/06, David Sharples <davidsharples_at_gmail.com> wrote:
>
> why not just look at dba_blockers and dba_waiters - why are you doing
> block dumps?
>
> Find the blocking session and either kill it or call him and and ask him
> to commit the changes
>
>
> On 18/09/06, BN <bnsarma_at_gmail.com> wrote:
> >
> >
> >
> > On 9/18/06, Anjo Kolk <anjo.kolk_at_oraperf.com > wrote:
> > >
> > >
> > > I am sorry to be such a pain, but may be explaining what the problem
> > > is and what you want to do, will help me and others on this list.
> > >
> > > Anjo.
> > >
> > >
> > > On 9/18/06, BN <bnsarma_at_gmail.com> wrote:
> > > >
> > > > Greetings,
> > > >
> > > >
> > > > Oracle 8.1.7.4
> > > >
> > > > I have taken a Block dump,
> > > >
> > > > Where do I look for missing commit?
> > > >
> > > >
> > >

Greetings,

Like I said I am able to identify this info from v$lock (request, lmode, block) and link it
to v$session to get the object details.

Initially I was thining that it was a missing "COMMIT" some where in the app, Later I saw a pattern.

The Blocker (doing a TXN (DML) spanning many tables) was holding the lock a little longer, 10 secs are more. Being a very busy OLTP , this was causing others issues.

I wasn't sure where the Blocker loosing his time, Later in the evening , I noticed that there were many ST enqueue locks (no, we are not using LMTS - For all new tables I am pushing LMTS). I am assuming that this could be an issue. Bumped up the next extent size. The tables were large and extents were in 1000s.

I am also monitoring v$session_wait. Its intermittent and fast. The users complain that they are stuck and if they retry they are back to normal.

I have been sitting on this all day yesterday, took some Level 12 traces and fixed some expensive queries , not sure if they helped or not. Those querues are running faster now.

Again back to monitoring today ...

Appreciate your ides/thoughts ....

-- 
Regards & Thanks
BN

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