Re: Enqueue Diagnosis

mccmx_at_hotmail.com (Matt) wrote in
news:cfee5bcf.0308130317.75036b6c_at_posting.google.com:

> I have an 8.1.7 database which is starting to bottleneck on Enqueue
> waits.
>
> Every couple of days we will experience a long 'TX Mode 6' wait for
> anything from 5 mintutes to 40 minutes.
>
> I am almost 100% sure that the waits are due to a long running batch
> job which updates/deletes many rows before commiting. The application
> does not issue a 'select for update' when querying records so I have
> ruled out the problem of users opening a record for update and leaving
> their desktop without commiting the changes.
>
> I am having real problems diagnosing these waits because lock waits
> are something that is very cryptic to interpret in Oracle.
>
> I have implemented a plsql script which enables a10046 trace (level 8)
> on any session waiting for an enqueue.
>
> This will give me some feedback about what the waiter was querying at
> the time of the long enqueue wait, but doesnt tell me who is blocking
> the row.
>
> I can get this information from another script which lists holders and
> waiters of locks.
>
> What I need is a way of calling my holders/waiters sql script from my
> plsql script which is called whenever a long enqueue wait is
> occurring. I need get the output of the SQL statement only when my
> plsql procedure determines that there is an active enqueue wait.
>
> Any ideas on this one....??
>
> Matt
>

What's wrong with querying dba_waiters/dba_blockers? Run the query from a shell script and email yourself the output. Received on Wed Aug 13 2003 - 08:03:22 CDT