Re: Large DB buffer, low cache hit ratio

From: Howard J. Rogers <howardjr_at_www.com>
Date: Thu, 25 Jan 2001 07:42:33 +1100
Message-ID: <3a6f3e10@news.iprimus.com.au>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message

news:94mldh$nf6$1_at_soap.pipex.net...

> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:3a6cd327$1_at_news.iprimus.com.au...
> > A couple of comments.
> <snip>
>
> > 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.
>
> Do you have some docs/papers to justify this rule of thumb? (The shared
 pool
> not the sga size.) Also for the 16k block size recommendation for NT. I've
> been humming and hawing about NT block size for a while but can't find NT
> specific advice. Ixora for example seems to have plenty of Unix as opposed
> to NT advice.
>

Hi Niall:

Ixora has a page on 'why large block size'. At it's top, it says :

QUOTE ON....
Because the database block size is fixed at database creation, this is one decision that is important to get right the first time. For file system based datafiles, without direct I/O, the database block size must match the file system buffer size exactly, as explained in our tip Why Raw Datafiles. For most Oracle databases under Unix, this means that the database block size must be 8K (because the file system buffer size is invariably 8K).

However, if your database is raw, or if direct I/O is available, then you have the luxury of choosing a larger database block size. There are many advantages to using a large block size, but the most significant is the saving in I/O for index based access paths.

QUOTE OFF... NT uses direct I/O (at least as far as I remember), so the advice appears to means that NT block sizes should be bigger than 8K -which gets you to 16K (though I suppose 32K is a possibility. Never gone that big myself).

As to the relative sizes of Shared Pool and Buffer Caches... I can't find the particular document I was thinking of when I wrote that. All my Instances have started off that way -90M for the Shared Pool, 32M for the Buffer Cache (4000 x 8K). Of course, once you start tuning the thing, who knows how it will end up -that depends, obviously, very much on the type of hammering you give your Instance. But I've found this a reasonable starting point.

Regards

HJR
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>
Received on Wed Jan 24 2001 - 14:42:33 CST