Oracle 9i Shared Pool Analysis Queries

-- Shared pool memory currently allocated with sessions executing it, or locking it
select sum(SHARABLE_MEM)/1024/1024 mb
from v$db_object_cache
where pins > 0 or locks > 0;

-- Current shared pool free
col name format a30
select * from v$sgastat
where name = 'free memory' and pool = 'shared pool';

-- Current auto SGA resize operation going on
select * from v$sga_current_resize_ops;

-- Historical auto & manual SGA resize operation
col PARAMETER format a20
select * from v$sga_resize_ops;

-- Free of memory in SGA to allocate to shared_pool_size in future
select * from v$SGA_DYNAMIC_FREE_MEMORY;

-- Current 3 SGA component size
select * from V$SGA_DYNAMIC_COMPONENTS;

-- Current spfile parameters related to shared_pool
show parameter shared_pool

-- Activities in shared_pool_reserved area
select * from v$shared_pool_reserved;

-- Actual allocated shared pool break down
SELECT SUM(BYTES)/1024/1024 mb FROM v$sgastat WHERE pool = 'shared pool';

-- Shared pool's library cache
select namespace, invalidations, reloads, GETHITRATIO, pinhitratio from v$librarycache;

-- Shared pool allocation with more than 5% of total shared pool size
col name format a20
select pool, name, bytes/1024 kb, bytes/738197504*100 pct from v$sgastat
where pool = 'shared pool'
and bytes > 738197504*.05 ;

-- Shared pool advise
select * from v$shared_pool_advice;

-- Rowcache analysis
column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter, sum(gets), sum(getmisses),
100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates
FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;

-- Shared pool cache object list to identify what should be pin
select owner, namespace, sum(loads), max(loads)
from v$db_object_cache
where loads > 10 and KEPT = 'NO'
group by owner, namespace;