If you don't use Java, don't allocate memory for it. You can shrink it to
32K:
java_pool_size = 32768 (on 817 you can set it to 0, but behind the scenes
Oracle will change it to 32768 anyways)
--
Terry Dykstra
Canadian Forest Oil Ltd.
"Mark D Powell" <mark.powell_at_eds.com> wrote in message
news:178d2795.0108290604.1ba782a6_at_posting.google.com...
> Pete Finnigan <pete_at_peterfinnigan.demon.co.uk> wrote in message
news:<f0cy1PArdLj7EwcU_at_peterfinnigan.demon.co.uk>...
> > Hi
> >
> > You could use
> >
> > alter session set events 'immediate trace name library_cache level 10';
> >
> > This will dump the sql from the shared pool
> >
> > cheers
> >
> > Pete Finnigan
> > www.pentest-limited.com
> >
> > In article <9mgsa2$ie7$1_at_news.online.de>, MT <mtechera_at_wpmc.com> writes
> > >Hi Everyone:
> > >
> > >I'm working on 8.1.7. I have a SHARED_POOL of 75 MB
> > >(my SHARED_POOL_RESERVED_SIZE = 7.5 MB) and am still getting that
dreaded
> > >ORA-04031. This happens although I can see that the free memory in the
shared
> > >pool
> > >is over 20%.
> > >
> > >I have tried as an emergency measure to use:
> > >
> > >ALTER SYSTEM FLUSH SHARED_POOL;
> > >
> > >but this does not reduce the shared memory usage.
> > >
> > >Does anyone know why the shared pool may refuse to flush? I have tried
this on
> > >an
> > >identical system and the shared pool usage drops immediately.
> > >
> > >Does anyone know why, when the shared pool still has over 20% free
space,
> > >Oracle gives 04031? I though that it used an LRU algorithm. If this is
the case,
> > >why aren't older queries simply being aged out?
> > >
> > >I am also now in the hunt for failure to use bind variables. Does
anyone have
> > >any
> > >good scripts or links to scripts that can help me pull out everything
in the
> > >shared pool
> > >for analysis?
> > >
> > >Any help and ideas are welcome.
> > >
> > >Regards to all,
> > >Mario
> > >
> > --
> > Pete Finnigan
> > IT Security Consultant
> > PenTest Limited
> >
> Mario, we had have the same problem since upgrading to version 8.1.7.
> We have entered a two tars with Oracle support and they have been
> little help. We have verified all our shared memory settings. We ran
> the SQL Oracle provided to see SQL that is causing other SQL to flush
> out of the pool and no luck there. We upped our shared pool by 20M
> over version 8.0.5 (not including separate 20M for java, which we do
> not use) and no help. We show 20% free when jobs start dieing.
>
> We can run about three weeks before we have a problem. But once we
> have a problem jobs just die with 04031 or 06508 and the only way to
> clear the problem is to bounce the system. Flushing the pool does not
> work. We pin all our large packages at instance startup plus most of
> the heavy hit Oracle provided packages. If we do weekly maintenance
> and bounce the system every week we do not get the errors, but leave
> the system running for about three weeks and production dies.
>
> We are going with the weekend bounce. What a pain.
>
> -- Mark D Powell --
Received on Wed Aug 29 2001 - 10:41:42 CDT
