Re: buffer busy wait & latch free ( cache buffer chain )

Hi,

it seems we have similar situation today. Our production db hangs suddenly. We couldn't even login
into the db.
Before aborting oracle we found a process like svrmgrl @/tmp/....sql. The body includes single
command 'alter system flush shared_pool'. It seems it was generated by db agent according to our job timetable in the OEM. We recently patched
db up to 8.1.7.2 (Linux) in order to get rid of memory leak in the shared pool...

Regards,
Ed

> One way I can say there are bunch of patches which has to be applied on
> 8.1.7.2,We had nightmares out when we migrated to 8.1.7.2,There are lot of
> work arounds which you will have to get from oracle for library cache,cache
> buffer chains,ora 7445 errors etc etc etc......
> We had an issue like if I give the command alter system flush shared pool
> the system got hung and all you can see is library cache pin and latch free
> waits and only option was to bring the database was bring it down.
> God I can say they have royally messed up in 8.1.7.2.
>
>
> >From: hemantchitale_at_charteredsemi.com
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: RE: buffer busy wait & latch free ( cache buffer chain )
> >Date: Tue, 04 Dec 2001 23:05:19 -0800
> >
> >
> >Your statement "there are several bugs relating to waiting for latch-free
> >on cache-buffer-chains, and the possibility of the instance hanging. " has
> >me nervous.
> >
> >I am aware of 1967363 and have obtained the standalone patch for this to be
> >applied on top of 8.1.7.2 I have
> >seen significant wait times for cache buffer chains latch in my 8.1.7.1
> >test environment and am waiting for the
> >environment to be released to me so that I can upgrade it to
> >8.1.7.2.1+1967363 (our production is still 8.0.5 !!).
> >
> >What other bugs are you talking off ?
> >Hemant
> >
> >
> >"Binley Lim" <Binley.Lim_at_ird.govt.nz> 05/12/2001 06:10 AM
> >Sent by: root_at_fatcity.com
> >
> >Please respond to ORACLE-L
> >
> > To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> > cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group)
> > Subject: RE: buffer busy wait & latch free ( cache buffer
> >chain )
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >If on 8.1.7, there are several bugs relating to waiting for latch-free on
> >cache-buffer-chains, and the possibility of the instance hanging.
> >
> >One is 1967363, and at least two others are still open after applying that
> >patch.
> >
> >Changing cache buffer latches/buckets is not mentioned in those bugs, and
> >since they are underscored, I would approach with caution.
> >
> >
> > >>> dcutrone_at_afip.gov.ar 12/05/01 08:30AM >>>
> >Vivek:
> > To combat the latch free- cache buffer chain problem, I would first
> >check in v$latch_children if the contention is even across all cache buffer
> >chain latches. If this is the case case (most of the time it is not) , I
> >would add more cache buffer latches. If this is not the case, as ARUN
> >suggested, I would find out which buffers are hot and I would investigate
> >WHY they are hot. (Ussually because of a bad SQL-- you should fix them! )
> > According to BBW problem, try to identify the segment involved and the
> >cause (p1=file , p2=block and p3=cause from a 10046 trace) and then
> >according to p3 there are some changes that can be done.
> >
> >HTH
> >Diego
> >
> >----- Original Message -----
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Sent: Tuesday, December 04, 2001 9:35 AM
> >
> >
> > > What are u seeing the buffer busy waits on is it for the read
> >intensivity
> > > and latch free is showing that there are hot blocks why dont you find
> >out
> > > the hot blocks and find what can be done about it
> > >
> > >
> > > >From: "VIVEK_SHARMA" <VIVEK_SHARMA_at_infy.com>
> > > >Reply-To: ORACLE-L_at_fatcity.com
> > > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > >Subject: buffer busy wait & latch free ( cache buffer chain )
> > > >Date: Tue, 04 Dec 2001 01:55:19 -0800
> > > >
> > > >
> > > >In report.txt we are getting the wait
> > > >"buffer busy wait" _& wait on "latch free" for "cache buffer chain"
> > > >
> > > >Could an excessively Large db_block_buffer cause this ?
> > > >
> > > >NOTE - Transaction OLTP in nature
> > > >
> > > >NOTE - freelists is set = 2* cpu_count
> > > >
> > > >Any oher advice ?
> > > >
> >
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Binley Lim
> > INET: Binley.Lim_at_ird.govt.nz
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >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.com
> >--
> >Author:
> > INET: hemantchitale_at_charteredsemi.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >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).
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: ARUN K C
> INET: arun_k_c_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Edward Shevtsov
  INET: ed_at_mb.ru

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).