Re: Free Memory in relation to SGA

A dictionary cache hit ratio of anything over 85% is actually pretty good (you'll never get much more than that, because, unlike the library cache if the 'keep' procedure is used, you can't pre-load the dictionary cache, and therefore the first queries after startup are inevitably going to induce cache misses). A poor library cache hit ratio will be the product of poor (ie, unshareable) SQL, and increasing the shared pool size isn't going to help that at all: the only thing that can boost that ratio is re-writing your apps' code so that it makes more use of bind variables. If you had a poor reloads to pins ratio, then increasing the size might be of some use, but not if it's the library cache hit ratio.

What's more, think about it: if 400+ Mb of your *existing* shared pool is sitting around free, it means your shared pool size is already too big. Free memory in a cache is wasted memory.

I'd be thinking about reducing my shared pool and investing the memory elsewhere -the buffer cache, say. Assuming that the SGA as a whole is not already more than about 1/3rd of available RAM.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"twerty" <2desp_at_iname.com> wrote in message
news:a7714b$j4hfi$1_at_ID-51406.news.dfncis.de...


> Hi,

>

>     When I do a select * from v$sgastat, I find that my free memory in the

> SGA amounts to 484MB. As my library cache ratio hit and dictionary cache

> ratio hit are not as high as I would like them to be (between 90-94%), I

am


> planning to use this particular free memory in the SGA (484 MB). My

question


> is, does Oracle actually use this free memory in the SGA for its work

> processes? Or can I safely say that it is not a problem assigning the

> remaining say, 400 or 484MB of free space to my Shared Pool Memory and the

> Buffer Cache (which isn't of a very high quality either).

>

> Many Thanks in Advance for the answers.

>

>