Re: Dictionary Cache Size Formula ???

ibrahim.dogan_at_lowes.com (Ibrahim DOGAN) wrote in message news:<33634314.0303200720.8cd967e_at_posting.google.com>...
> We have very low dict. cache hit ratio, around 80%.
>
> 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 but even if we double the shared pool,
> dict cache size doesn't budge much (7-8M) and we still get very low
> dict cache hit ratio?
>
> Any workaround to directly increase the size of dict cache?
>
> Thanks in advance
> i.d.

Ibrahim, there's no way to directly size the dictionary cache. If your application has to access a lot of objects, you have to increase shared_pool_size to increase dictionary cache (so not wasting time blindly searching in library cache becomes more important, i.e. avoid literals in SQLs, avoid public synonyms etc). Generally low recursive calls corresponds to low dictionary cache miss. But I don't think there's a way to avoid those recursive calls given certain applications. You may also consider using locally managed tablespaces. That might actually lower your dictionary cache hit ratio, but it also lowers dictionary cache hits. So the ratio becomes less meaningful and you gain by giving more memory to other parts of shared pool, mostly library cache.

Some people believe increasing _row_cache_cursors reduces dictionary cache miss. Steve Adams's notes clearly tell us that's not so.

Yong Huang Received on Fri Mar 21 2003 - 12:25:52 CST