Re: ORA-4031 shared pool fragmentation

Before increasing the Shared pool size, u need to check if present shared pool is being properly and fully used.

If there is lots of free memory available but nor contigious then there is not point in increasing the shared pool.Check shared pool latch sleeps , if they are high, no need to increase shared pool.

Then u need to optimize use of shared pool. Pin most executed objects, configure shared pool reserved size, query v$shared_pool_reserved for requests misses and requests failure to fine tune ur pins.

The following script was provided by steve

select avg(v.value) shared_pool_size, greatest(avg(s.ksmsslen) - sum(p.ksmchsiz), 0) spare_free, to_char( 100 * greatest(avg(s.ksmsslen) - sum(p.ksmchsiz), 0) / avg(v.value), '99999' ) || '%' wastage from x$ksmss s, x$ksmsp p, v$parameter v where

  s.inst_id = userenv('Instance') and
  t.inst_id = userenv('Instance') and
  u.ksmchcom = 'free memory' and
  v.ksmssnam = 'free memory' and
  w.name = 'shared_pool_size' /

this gives wasted Shared pool space.

--
Posted via dBforums
http://dbforums.com