##  DBA Command Post - shared_pool.sql


select S.name, 
           S.bytes "Free Bytes", 
       round((S.bytes/P.value)*100,2) Perc_Free,
       P.value / (1024*1024) SB_Pool_Mg
from   sys.v_$parameter P,
       sys.v_$sgastat S
where  S.name = 'free memory'
and    P.name = 'shared_pool_size'
/

select sum(loads),
       sum(EXECUTIONS),  
       round((sum(loads) / sum(EXECUTIONS)) * 100, 4) Load_Perc
from   sys.v_$db_object_cache
/

select decode(STATUS,'FREE','Free','Used') Status, 
       count(*) 
from   v$bh 
group  by decode(STATUS,'FREE','Free','Used')
/