RE: DB_BLOCK_BUFFERS

Sandeep/List,
Be VERY careful when you set this parameter. It will impact performance tremendously (I think that's why this parameter is now obsolete in 8i).

Sah Kohsuwan
Oracle DBA
COMFORCE Corporation
516-437-3300 ext. 352
skohsuwan_at_comforce.com

> -----Original Message-----
> From: Sandeep Kurliye [SMTP:Sandeep.Kurliye_at_almarai.com]
> Sent: Monday, August 07, 2000 4:17 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: DB_BLOCK_BUFFERS
>
>
> John,
>
> Which oracle version you are working on?
>
> I am having a solution for Oracle 7.3.4.
>
> Few days before, I read this article somewhere.
>
> If hit ratio is lower than 80%, you may want to test out the impact of
> adding more buffes. Oracle provides a utility for this test.
>
> 1. Set the parameter DB_BLOCK_LUR_EXTENDED_STATISTICS to the number of
> buffers your want to add.
>
> Db_block_lru_extended_Statistics = 200
>
> 2. Start the database up for normal use.
> 3. After a period of normal running, query the virtual table X$KCBRBH.
>
> This table containts two columns of interest:
> 1. INDX - An identifier for each 'new' buffer, starting with 0.
> 2. COUNT - The number of extra cache hits that would be gained by
> adding this buffer.
>
> Example:
>
> To find the number of additional cache misses you would incur by
> increasing
> the cache size, for example from 100 to 120 buffers, you could use the
> following query.
>
> SELECT SUM(count) ach
> FROM x$kcbrbh
> WHERE indx < 20
> /
>
> You can then find the impract on the cache hit ration by including the
> additional cache hits in the following formula.
>
> Cache Hit Ratio = (consistent gets + db block gets + ach- physical reads )
> /
> (Consistent gets + db block gets).
>
> If the ratio was previously less than 80%, but is now above this figure,
> you
> should add the extra buffers.
>
> Using DB_BLOCK_LRU_EXTENDED_STATISTICS has processing costs, proportional
> to
> the number set. Remember also that only the sys user can see the X$
> virtual
> tables.
>
>
> HTH.
>
> Regards,
>
> Sandeep.
> Riyadh, Saudi Arabia.
>
>
> -----Original Message-----
> From: John Lewis [SMTP:jlewis_at_punchnetworks.com]
> Sent: Monday, August 07, 2000 11:10 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: DB_BLOCK_BUFFERS
>
> In lieu of something scientific , raise it slowly until
> you level out at your hit% you want.
>
> -----Original Message-----
> Sent: Monday, August 07, 2000 9:56 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Using T.O.A.D. I found our database has a Buffer Cache Hit Rate of
> just
> udner 76%.
> The advise is that the db_block_buffers may need to be increased.
>
> Our db_block_size is 8K.
> db_block_buffers is currently set to 4096
>
> Any advise as to how to determine the optimal size for
> db_block_buffers ?
>
> Thanx for any hints !
>
>
> Greets,
>
> Kirsten
>
> --
> Author: Weerd de E.C. Kirsten
> INET: Kirsten.deWeerd_at_Oranjewoud.nl
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> 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).
> --
> Author: John Lewis
> INET: jlewis_at_punchnetworks.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> 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).
> --
> Author: Sandeep Kurliye
> INET: Sandeep.Kurliye_at_almarai.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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
Received on Mon Aug 07 2000 - 14:57:26 CDT