Re: Calling in the Cavalry - Hanging 8i database

"Johne_uk" <edgarj_at_tiscali.co.uk> wrote in news:1116111399.210696.205240_at_g14g2000cwa.googlegroups.com:

> I've never noticed (or had reported) any other OS procesess that have
> hung other than Oracle. There is also a Sybase dbase running on the
> same box and we never have any problems with this. When the dbase hangs
> I've never actually tested for any deadlocks using the internal

If the situation were a TRUE deadlock, this would be noted in the alertSID.LOG file. What you might have are ENQUEUE waits; (not logged).

Here is a true (horror) story.
An "secretary" started to update a record in the EMPLOYEE table, and then went to lunch.Employee records are small & the block size is LARGE with many records in a block. EMPLOYEE table had INITRANS=1. Therefore all other sessions which wanted this block went into ENQUEUE wait. But of course some of them had locks on other tables; also with INITRANS=1; which caused other sessions to "hang". Over the course of very few minutes the whole application essentially froze.

As in your case, bouncing the DB clears out all the sessions in ENQUEUE wait and business as usual resumes; until the next time somebody started a UPDATE transaction & then went off to a meeting without any ROLLBACK or COMMIT.

My fix was to "reorg" heavily updated tables while specifying INITRANS=15. After that, the hangs never returned. Received on Sat May 14 2005 - 19:21:54 CDT