Re: Free Memory in relation to SGA

So now I'm a bit confused, because here you say your library hit ratio is 3 9s, yet before you said it was a maximum of 94%. Whatever... chasing hit ratios is an appallingly bad way of tuning, and I'd be more or less happy with what you've got. You don't mention the buffer cache hit ratio, so who knows if it's under pressure or not, and whether an extra 400Mb of buffers would help. I frankly doubt it, but I can't tell with the info provided.

Are you happy with the performance of the database?

I only ask because your first post read 'because my ratios are too low, I want to do X'. But really, who gives a damn about ratios, if Users are happy with the performance of the machine? Chasing ratios is a mirage -even an excellent buffer cache hit ratio can mask some disastrous bits of code.

If you're generally happy with performance, return the 400+Mb to the O/S, and await developments....

Regards
HJR

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


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


> 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.

> > >

> > >

> >

> >

>

>