RE: investigating database lockup

Steve,

I have seen such a hang occur when there is a lot of untuned buffer-get intensive SQLs on a 8.1.7.3 database. There is a nasty 'cache chain buffer' latch issue in 8.1.7.3. You haven't mentioned the subversion, so you might consider an upgrade to 8.1.74 if this is true and the situation reoccurs. I have also seen severe contention for the shared pool/library cache latches bring the system to a virtual halt. You mentioned many of the v$ tables, but I think you missed out one that can be very useful: V$SESSION_WAIT. A rollup of count by event will reveal volumes about what's going on currently. Steve Adams has a 'waiters' sql in his site that will also help. You can then build monitors around such scripts that will alert you before they reach a threshold. Besides, there is a ML note that can help in determining apparent hangs - top among them are the oradebug hanganalyze command.. Let me know and I can dig up the note number.

Hth,
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my future!

• The opinions and statements above are entirely my own and not those of my employer or clients **

-----Original Message-----
Sent: Thursday, January 30, 2003 11:43 AM To: Multiple recipients of list ORACLE-L

This morning I have experienced a couple of strange lockups of our database. The symptoms were the database was nonresponsive to any activity, freeze on login to sqlplus, active sessions freeze as soon as they interact with the db. There is no alert log activity. The systems cpu usage soars to 100%, and an oracle process, once a shared server and once a dedicated server, is dominating the usage(info gathered from top).  

The first time I killed the shared server process, and the database picked up and operated normally. Then a half hour later the dedicated connection popped up. The dedicated connection was owned by one of our oltp processes that pretty well runs without issue 24/7, so I decided to bounce the db to hopefully clear up the issue.  

Our system is Oracle 8.1.7 on Solaris 8. In short, I am hoping to get some ideas in tracking down what happened. I have some sql scripts that I have used to identify problem sessions/sql in the past. They focus on v$session, v$sess_io, and v$sqltext. Unfortunately, these did me little good because while the trouble was occurring I couldn't interact with the database while the sessions were active.  

I am soliciting actively for any Hints, Tips, or Ideas you may have,  

Thanks,
Steve McClure

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).