Re: Free Memory in relation to SGA

My current dictionary cache hit ratio is at 81%, thinking of ways to actually get this figure up. The library cache is optimum; 99.9% From what I understand, 400+ MB free is from the total SGA, and not from the shared pool memory? So maybe I should actually free up the RAM back to the O/S or put the 400+MB to good use in either the data buffer or ? (Which comes back to the question of whether this 400+MB will be used when Oracle processes require the extra memory when running large programs/statements) Or is there something I am misunderstanding here?

What would be your recommendation, Howard? Total SGA (2.4GB out of total 6GB; with data buffer at 1.7GB and shared Pool at 700MB) ) is just abit over 1/3 of system's physical memory.

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:a77j7o$mre$1_at_lust.ihug.co.nz...
> 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.
> >
> >
>
>
Received on Tue Mar 19 2002 - 21:13:12 CST