Free space in SGA

[cleegt]

Dear All,

Is there anyway I can tell how much free space is left in SGA area? For example, if I want to increase the size of the buffer cache or the shared pool, I would like to know how much space in sga is available for me.

Thanks,

-- Chris

[gbrabham]

---- How much memory is left for SHARED_POOL_SIZE: ----

col value for 999,999,999,999 heading 'Shared Pool Size'
col bytes for 999,999,999,999 heading 'Free Bytes'

select to_number(v$parameter.value) value, v$sgastat.bytes,
(v$sgastat.bytes/v$parameter.value)*100 Percent_Free
from v$sgastat, v$parameter
where v$sgastat.name = 'free memory'
and v$parameter.name = 'shared_pool_size';

---------- or ---------------
select sum(ksmchsiz) Bytes, ksmchcls Status
from x$ksmsp
group by ksmchcls
/



HTH
Gregg

[cleegt]

Thanks for the reply. What I want to find out is how much space is left in sga_max_size. So, if I need to increase the size of a particular cache buffer or the shared pool, I know how much more I can increase the size. Any idea?

Thanks,

-- Chris

[prasadvd]

Hi

You can check sga free by the following.

select name,
sgasize/1024/1024 "Allocated (M)",
bytes/1024/1024 "Free (K)",
round(bytes/sgasize*100, 2) "% Free"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory'
/

[st2003]

SQL> select name,
2 sgasize/1024/1024 "Allocated (M)",
3 bytes/1024/1024 "Free (K)",
4 round(bytes/sgasize*100, 2) "% Free"
5 from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
6 where f.name = 'free memory'
7 /

NAME Allocated (M) Free (K) % Free
-------------------------- ------------- ---------- ----------
free memory 1101.26323 186.887745 16.97
free memory 1101.26323 50 4.54

Which one is correct?

Thanks, ST2003