Re: buffer cache -once again

This is on several SAP systems with DD managed extents.

Std extent size? What's that? ;)

I'll run it again to be sure, I could be misrembering the results.

Jared

On Sunday 11 May 2003 02:16, Niall Litchfield wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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).