| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: buffer cache -once again
I've been trying to get multiblock_read_test.sql to work. I downloaded
trace_file_name.sql (prereq) and commented out the correct portions.
When I run multiblock_read_test.sql, everything works but it fails on
the sed command at the end. I run the sed command from the command line
and it works fine. The error message is:
sed: Cannot find or open file /app/oracle/admin . . .
The file named in the error message is there and when I cut/paste the sed command it works (correctly I think).
The only thing I can think of is version difference. All of these scripts say 7.3, 8.0 or 8.1 and this is a 9.2 database. Does 9.2 do something different with writing trace files and leave them open and that is preventing the script from accessing the file?
I'll puzzle this one some more tomorrow. Thoughts/comments/advice welcome.
Stephen
>>> niall.litchfield_at_dial.pipex.com 05/08/03 02:23PM >>>
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
>
-- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert INET: StephenAndert_at_firsthealth.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).Received on Thu May 08 2003 - 19:31:40 CDT
 |
 |