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

Home -> Community -> Usenet -> c.d.o.server -> Re: Large DB buffer, low cache hit ratio

Re: Large DB buffer, low cache hit ratio

From: James Williams <willjamu_at_mindspring.com>
Date: Tue, 23 Jan 2001 03:03:01 GMT
Message-ID: <3a6cf456.1436596@nntp.mindspring.com>

On Tue, 23 Jan 2001 11:41:50 +1100, "Howard J. Rogers" <howardjr_at_www.com> wrote:

>A couple of comments.
>
>First tuning a database doesn't just mean speed or good hit ratios, but the
>most effective use of resources to achieve reasonable speed and hit ratios.
>
>Throwing 70% of available RAM at Oracle is not a good start.
>
>Having 4K block sizes is an even worse one.
>
>Having 5/7ths of your Instance dedicated to the buffer cache is probably the
>worst.
>
>Go back to basic rules-of-thumb. SGA should be around 300-350Mb on your
>system, in total. Next, the Shared Pool should be/could be aound twice to
>three times the size of your buffer cache. And I hope your log buffer is
>trivial. Hence, I'd be looking at a Shared Pool of around 200Mb and a
>Buffer Cache of around 100Mb (mileage might vary). Log buffer of around
>1Mb.
>
>*THEN* I'd start tuning properly -which means making sure you are getting a
>95% hit rate on the Library Cache first (best hope your developers knew what
>they were doing). A miss on the Library Cache is far more expensive than a
>miss on the Buffer Cache.
>
>Add more memory to each part of the SGA in turn only when doing so produces
>a measurable improvement in the hit rates. If you increase the Library
>Cache, and the hit rate doesn't get significantly better, reverse that last
>increase, and move on to tuning the Buffer Cache. Repeat ad nauseam.
>
>If you get the time, you might also reasonably consider re-creating the
>database with a proper block size -8K for most Unixes and 16K for NT.
>
>Regards
>HJR
>
>
><yunhuiyang_at_my-deja.com> wrote in message
>news:94ib6g$cgj$1_at_nnrp1.deja.com...
>> Hi, all,
>>
>> The OS has 1G mem. SGA is about 700M, DB_BLOCK_BUFFERS is about 500M
>> (133230*4K) in bytes. The size of whole database data files is less
>> than 50G. So the DB_BLOCK_BUFFERS is more than 1% of the total database
>> size. While the database buffer cache hit ratio only 24%. Please advice
>> what happens here.
>>
>> Thank you.
>>
>> Helen
>>
>>
>> Sent via Deja.com
>> http://www.deja.com/
>
>
Received on Mon Jan 22 2001 - 21:03:01 CST

Original text of this message

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