RE: investigating database lockup

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: root_at_fatcity.com [mailto:root_at_fatcity.com] 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: 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rich Holland
  INET: holland_at_guidancetech.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).