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: Howard J. Rogers <howardjr_at_www.com>
Date: Tue, 23 Jan 2001 11:41:50 +1100
Message-ID: <3a6cd327$1@news.iprimus.com.au>

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 - 18:41:50 CST

Original text of this message

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