Re: Dictionary Cache Size Formula ???

"Ibrahim DOGAN" <ibrahim.dogan_at_lowes.com> wrote in message news:33634314.0303200720.8cd967e_at_posting.google.com...
> We have very low dict. cache hit ratio, around 80%.

So? Who cares??

Hit ratios are a daft way to tune at the best of times, and the dictionary cache hit ratio is one of the daftest.

Even if you subscribe to the school of thought that they actually mean anything, the ratio on the dictionary cache in all the standard texts is supposed to be about 80 to 85%. So you're about right, and I wouldn't waste any more time worrying about it.

>
> Dict cache size is 6M and shared pool is 237MB.
>
> I have no clue about the internal formula Oracle applies to 'shared
> pool' to set dict cache size

The algorithm Oracle uses to size the dictionary cache goes something like this: 'allocate as much memory to the dictionary cache as it needs, and whatever is left, give to the library cache'. So the rather more important question is: how's your LIBRARY cache? If it is fine and dandy, then the dictionary cache must be too, regardless of what the ratios allegedly tell you.

>but even if we double the shared pool,
> dict cache size doesn't budge much (7-8M)

...which is Oracle's way of telling you that it doesn't *need* more memory in that particular cache.

>and we still get >very low
> dict cache hit ratio?
>
> Any workaround to directly increase the size of dict cache?

No. And there's not even a need to do so. Get your library cache right first, and the dictionary cache will inevitably follow.

But the general advice is: stop worry about hit ratios, and start looking at the wait events that are happening on your database. Those are far more problematic, and fixing them up really does make a difference to your database.

Regards
HJR Received on Thu Mar 20 2003 - 13:01:08 CST