| 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
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'
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).Received on Thu May 08 2003 - 15:03:23 CDT
 |
 |