Did you happen to dig up the ML note number for Steve? I'd be interested in reading it; I've had some hangs in < 8.1.7.4 systems that I've "fixed" by rebooting. Getting out and slamming all the doors seemed to fix things, but I *HATE* those solutions, never knowing the root cause of the problem....
Also, if you have any pointers to more info about using oradebug in general, that would be helpful; I haven't ever used this tool, though it sounds incredibly useful. :) Thanks! Rich > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of John > Kanagaraj > Sent: Friday, January 31, 2003 8:49 PM > To: Multiple recipients of list ORACLE-L > Subject: 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rich Holland INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
