RE: DB_BLOCK_BUFFERS

From: Sandeep Kurliye <Sandeep.Kurliye_at_almarai.com>
Date: Mon, 7 Aug 2000 22:02:36 +0300
Message-Id: <10582.113999@fatcity.com>

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

Received on Mon Aug 07 2000 - 14:02:36 CDT