db_block_lru_extended_statistics is obsolete in Oracle 8i, and otherwise
not recommended on production systems. Use with caution.
Sandeep Kurliye wrote:
>
> 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
> (or the name of mailing list you want to be removed from). You may
Received on Mon Jul 03 2000 - 00:12:50 CDT
