— shared_pool_size must be non-zero
select
to_number(v$parameter.value) “value”,
v$sgastat.bytes “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’
and v$sgastat.pool=’shared pool’;
— Regardless of the value of shared_pool_size the following
— query shows the breakdown of shared pool
select sum(ksmchsiz) Bytes, ksmchcls Status
from x$ksmsp
group by ksmchcls;
BYTES STATUS
———- ——–
60039088 recr
111124776 freeabl
1469432 R-freea
367771176 perm
28853864 R-free
1163744 free