Enqueue Diagnosis

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 Received on Wed Aug 13 2003 - 06:17:18 CDT