SHARED POOL

SHARED POOL



------------------------------------------------------
col value format 999,999,999,999 heading "Shared Pool Size"
col bytes format 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' and
v$sgastat.pool='shared pool'; 

--------------------------------------------

select sum(sharable_mem/1024/1024) from v$db_object_cache;

---------------------------------------------

set lines 200
set pages 200
col OWNER for a30
col NAME for a30
col NAMESPACE for a30
col TYPE for a30
select OWNER, NAME, NAMESPACE, TYPE, SHARABLE_MEM, KEPT FROM V$DB_OBJECT_CACHE WHERE OWNER in ('LBUNAREP','LBUEUREP');

--------------------------------------------

SELECT (1-(sum(getmisses)/sum(gets))) * 100 "Dict Cache Hit Ratio" FROM v$rowcache; 

--------------------------------------------

Alter system flush shared_pool;

--------------------------------------------

alter system set shared_pool_size=210m;

--------------------------------------------