ora-04031

orafaq

set lines 300
select to_char(to_number(v.KSPPSTVL), '999999999') min_to_go_to_reserved, to_char(s.request_failures, '99999999') ora04031
, to_char(s.last_failure_size, '999999999999') failure_size, trunc(s.free_space/1024/1024) free_reserved_m, max_free_size largest
, to_char(stat.bytes, '999999999') shs_pool_free
from x$ksppi n,
x$ksppsv v,
v$shared_pool_reserved s
, v$sgastat stat
where n.indx = v.indx
and n.ksppinm = '_shared_pool_reserved_min_alloc'
and stat.pool = 'shared pool'
and stat.name = 'free memory'
;

Issue if REQUEST_FAILURES > 0:
Shared pool - fragmentation? or low memory
if LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
else Shared pool reserved -
fragmentation?
if max_free_size < failure_size and free_space > failure_size
or low memory

-- Free shared memory hist
select t.BEGIN_INTERVAL_TIME, s.bytes
from DBA_HIST_SGASTAT s,
dba_hist_snapshot t
where s.pool = 'shared pool' and s.name = 'free memory'
and t.snap_id = s.snap_id
order by 1;