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:
> INET: Jared.Still_at_radisys.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: 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).