On 9/19/06, David Sharples <davidsharples@xxxxxxxxx> 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@xxxxxxxxx> wrote: > > > > On 9/18/06, Anjo Kolk <anjo.kolk@xxxxxxxxxxx > 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@xxxxxxxxx> 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
