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

Re: DB_BLOCK_BUFFERS

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Mon, 7 Aug 2000 11:57:48 -0700 (PDT)
Message-Id: <10582.113997@fatcity.com>


--0-1804289383-965674668=:264
Content-Type: text/plain; charset=us-ascii

 Increase the db_block_buffers until hit ratio levels off.

Something else to consider is that an application that does a lot of scans will probably have a low hit ratio no matter what. Our data warehouse runs between 70% and 90%. 76% may not be something to be alarmed about especially in a DSS system.

Something else to try besides increasing block buffers is to identify small(ish) tables that are frequently scanned. By caching them in at the MRU end of the LRU list you can probably increase the hit ratio. I recently did this on a peoplesoft HRMS database and increased the hit ratio from < 80% to 99% by pinning the PSPRCSRQST table in memory. You do this with "alter table table-name cache;". Alternatively you can put them into the keep buffer pool in Oracle8i.

  "Weerd de E.C. Kirsten" <Kirsten.deWeerd_at_Oranjewoud.nl> wrote: 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).


---------------------------------
Do You Yahoo!?
Kick off your party with Yahoo! Invites.
--0-1804289383-965674668=:264
Content-Type: text/html; charset=us-ascii


<P> Increase the db_block_buffers until hit ratio levels off.</P>
<P>Something else to consider is that an application that does a lot of scans will probably have a low hit ratio no matter what. Our data warehouse&nbsp;runs between 70% and 90%. 76% may not be something to be alarmed about especially in a DSS system.</P>
<P>Something else to try besides increasing block buffers is to identify small(ish) tables that are frequently scanned. By caching them in&nbsp;at the MRU end&nbsp;of the LRU list you can probably increase the hit ratio. I recently did this on a peoplesoft HRMS database and increased the hit ratio from &lt; 80% to 99% by pinning the PSPRCSRQST table in memory. You do this with "alter table table-name cache;". Alternatively you can put them into the keep buffer pool in Oracle8i.<BR></P>
<P>&nbsp; <B><I>"Weerd de E.C. Kirsten" &lt;Kirsten.deWeerd_at_Oranjewoud.nl&gt;</I></B> wrote: <BR>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">Using T.O.A.D. I found our database has a Buffer Cache Hit Rate of just<BR>udner 76%.<BR>The advise is that the db_block_buffers may need to be increased.<BR><BR>Our db_block_size is 8K.<BR>db_block_buffers is currently set to 4096<BR><BR>Any advise as to how to determine the optimal size for db_block_buffers ?<BR><BR>Thanx for any hints !<BR><BR><BR>Greets,<BR><BR>Kirsten<BR><BR>-- <BR>Author: Weerd de E.C. Kirsten<BR>INET: Kirsten.deWeerd_at_Oranjewoud.nl<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list!
you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br> Kick off your party with Yahoo! Invites.
Received on Mon Aug 07 2000 - 13:57:48 CDT

Original text of this message

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