RE: DB Block Buffers - Too Much ???

From: Sandeep Kurliye <Sandeep.Kurliye_at_almarai.com>
Date: Sun, 2 Jul 2000 11:49:22 +0300
Message-Id: <10546.111070@fatcity.com>

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

Received on Sun Jul 02 2000 - 03:49:22 CDT