Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DB Block Buffers - Too Much ??? - X$KCBRBH

RE: DB Block Buffers - Too Much ??? - X$KCBRBH

From: Gait, Christopher <cgait_at_condor.nrl.navy.mil>
Date: Thu, 13 Jul 2000 10:20:38 -0400
Message-Id: <10557.111952@fatcity.com>


Please note that using the X$KCBRBH and extended statistics is now obsolete. I believe it was obsoleted by archiceture changes in 8.0.x. So if you are running something before 8, have at it!

Regards,
Christopher Gait
Oracle DBA

(Semi)permanent address: cjgait_at_yahoo.com http://www.geocities.com/athens/3623

"Death is nature's way of saying: NEXT!"

> -----Original Message-----
> From: Sandeep Kurliye [mailto:Sandeep.Kurliye_at_almarai.com]
> Sent: Sunday, July 02, 2000 6:06 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: DB Block Buffers - Too Much ???
>
>
>
> Well... I am not expert in tuning.....
>
> 2-3 days before, I was reading an article - how to tune buffer cache?
>
> It says - 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: Deepak Sharma [SMTP:sharmakdeep_at_yahoo.com]
> Sent: Wednesday, June 21, 2000 10:56 PM
> To: Multiple recipients of list ORACLE-L
> Subject: DB Block Buffers - Too Much ???
>
> Recently on one of our test systems the Buffer Cache
> Hit Ratio was showing 80%. I increased
> db_block_buffers from 6000 to 16000, and the after
> that the hit ratio has dropped to 55%. It seemed
> strange to me that increasing db_block_buffers should
> actually decrease performance, until I read Oracle
> tuning tips from Richard Niemiec, where he mentions
> too much db_block_buffers is not good too, as it may
> lead to swapping. My question is how do you determine
> whether the buffers are too low or too high ?
>
> SQL> select state, count(*) from x$bh group by state;
>
> STATE COUNT(*)
> ---------- ----------
> 1 13047
> 3 2953
>
> Does state = '1' mean this memory is not being used
> and if so, should I decrease the buffers ?
>
> -- Deepak
>
> =====
> Oracle DBA,
> Minneapolis, MN
> USA
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages with Yahoo! Messenger.
> http://im.yahoo.com/
> --
> Author: Deepak Sharma
> INET: sharmakdeep_at_yahoo.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 Thu Jul 13 2000 - 09:20:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US