RE: buffer cache -once again

No.

On a SAN attached Compaq box at work I get 128, which with an 8k block size, is = 1mb or SSTIOMAX. My box at home has a max effective and achieved MBRC of 64 (or 512k). 20 blocks isn't a standard extent size for you by any chance?

Niall

> -----Original Message-----
> From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
> Sent: 09 May 2003 00:44
> To: ORACLE-L_at_fatcity.com
> Cc: niall.litchfield_at_dial.pipex.com
> Subject: RE: buffer cache -once again
>
>
> All the windows boxes I've tried this on report a value of 20 blocks.
>
> Have you seen similar results?
>
> Jared
>
>
>
>
>
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com>
> Sent by: root_at_fatcity.com
> 05/08/2003 02:23 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: buffer cache -once again
>
>
> Whilst you are at Steve Adam's site check out
> http://www.ixora.com.au/scripts/io_opt.htm and you will find
> a couple of scripts to determine what MBRC you are actually
> achieving - assuming Unix - but I intend to modify it a bit
> so it will work on windows. I believe that the reason for the
> changed behaviour that folk are reporting/discussing on 9i is
> that it too uses achieved, rather than set, values of MBRC to
> determine execution plans. I can't comment on whether this is
> actually the case - but it seems reasonable.
>
> What happens to execution plans when you change the IO
> subsystem would be an interesting exercise as well.
>
> Niall
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of
> > Jared.Still_at_radisys.com
> > Sent: 08 May 2003 21:03
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: buffer cache -once again
> >
> >
> > You can get some idea of how many FTS are taking place
> > with this query:
> >
> > select
> > decode(class,
> > 1,'User',
> > 2,'Redo',
> > 4,'Enqueue',
> > 8,'Cache',
> > 16,'OS',
> > 32,'Parallel Server',
> > 64,'SQL',
> > 128,'Debug'
> > ) class_name
> > ,name
> > ,value
> > from v$sysstat
> > where class = 64
> > order by class_name, name;
> >
> >
> > If you do set MBRC to 128, you will need to adjust
> > optimizer_index_caching and optimizer_index_cost_adj.
> >
> > If you don't, the CBO will develop a sudden liking for FTS,
> > which may not be what you want.
> >
> > Please refer to some of the guru's websites for playing with
> > these init parameters. http://www.jlcomp.demon.co.uk/ and
> > http://www.ixora.com.au come to mind.
> >
> > This is on 8i. It changes a bit on 9i with CPU costing, and
> > IIRC, you can set MBRC on 9i without so much trouble.
> >
> > Jared
> >
> >
> >
> >
> >
> >
> >
> >
> > Arvind Kumar <arvindk_at_sqlstarintl.com>
> > Sent by: root_at_fatcity.com
> > 05/08/2003 02:51 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: RE: buffer cache -once again
> >
> >
> > zhu chao,
> >
> > is there any problem if i increase the value of
> > db_file_multiblock_read_count to its maximum value 128 , or
> > its require special attention to be given at some other part also.
> >
> > -----Original Message-----
> > Sent: Thursday, May 08, 2003 2:07 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi,
> > With db file scattered read, if it is not a datawarehouse
> > db, you must tune your SQL. It means there is a lot of full
> > table scan/full index scan.
> > If the full table scan is necessary, then increasing
> > db_file_multiblock_read_count is good. Though it is still the
> > no.1 wait event, pay attention to the ratio of this event
> > waited.If the ratio decreased, it means you are doing good.right?
> > Tune some os parameter like stripe size, maxphys(in
> solaris, not
> > knowing
> > the corresponding parameter in aix) will also help.
> >
> > Regards
> > zhu chao
> > msn:chao_ping_at_163.com
> > www.cnoug.org(China Oracle User Group)
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, May 08, 2003 2:36 PM
> >
> >
> > > Dennis,
> > >
> > > earlier my buffer cache was 300MB that time the cache
> hit ratio
> > > was
> > 81%
> > > ,so i thought to increase the size by 100mb and incresed
> > .but again
> > > the cache hit ratio is 81%.means the problem is not the
> > buffer cache.
> > >
> > > the top wait event is 'db file scattered read' ,i
> > increased the
> > > db_file_multiblock_read_count from 16 to 64 but still this
> > the top one
> > wait
> > > event.
> > >
> > > db version is 8.0.5 ,OS is AIX 4.3 on RS/6000.
> > >
> > >
> > > pls suggest if need to correct something ...
> > >
> > > Thanks
> > >
> > >
> > > Arvind
> > >
> > >
> > >
> > > -----Original Message-----
> > > Sent: Tuesday, May 06, 2003 7:42 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Arvind
> > > What makes you suspect you've configured your buffer cache too
> > > large?
> > A
> > > better question might be "how can I tell if my buffer cache is
> > > properly sized?". Start by checking your wait times. What
> > are your top
> > > 3 waits?
> > Also,
> > > what is your (cough, cough) average buffer hit ratio?
> > >
> > > Dennis Williams
> > > DBA, 60%OCP, 100% DBA
> > > Lifetouch, Inc.
> > > dwilliams_at_lifetouch.com
> > >
> > >
> > > -----Original Message-----
> > > Sent: Tuesday, May 06, 2003 4:37 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Dear All,
> > >
> > > how can i check if my buffer cache is bigger than necessary
> > > ?oracle
> > db
> > > version is 8.1.7.
> > >
> > >
> > > Thanks
> > >
> >
> >
> >
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.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).