Reduce SGA size

[jamiek]

oops!
sorry...how come copy and paste doesn't work here...

[sambavan]

I have gone ahead and edited the code for you. In the future if you would like to use the copy and paste option, use the

[  CODE   ]

option...

NOTE: If you feel that I have violated your rights on this forum, by editing your post, please let me know and I'll refrain from doing it.

Sam

[Edited by sambavan on 10-03-2001 at 06:18 PM]

[jamiek]

Thanks sam..any comments on this output?

[roobaron]

After reducing the SGA via above steps. All you need to do is make sure that memory use is as efficient as possible.
If the application using the db has a lot of packages(procedures,functions) check to see what the number of executions is for each and pin the most used into the SGA.
Why? this will reduce the overhead to reload into memory.

Have Fun

roo

Here are some alternative scripts to check free and freeable memory

Try this script for SGA

PROMPT R-free = Shared pool reserved size
PROMPT R-freea = reserved memory
PROMPT free = This is the amount of contiguous free memory available
PROMPT freeabl = Used memory is freeable
PROMPT perm = Freed memory not yet available
PROMPT recr = Reserved

select sum(ksmchsiz) Bytes, ksmchcls Status
from sys.x$ksmsp
group by ksmchcls;

And this for buffer_cache (requires catparr.sql to have been run)

PROMPT State 0 = Buffers that have NOT been used
PROMPT State 1 = Buffers that have been used

select state, count(*)
from x$bh
group by state;



select
decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK
STATUS", count(*)
from x$bh
group by
decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state);