Re: ORA-04031 and shared pool

Hi Mark:

I have read somewhere that there was a problem when TIMED_STATISTICS = TRUE We need this setting because we are constatntly tuning the SQL in our production system. Did you hear anything about this in talking with the Oracle people?

Regards,
Mario

PS
I doubt bouncing will be an option for us due to the nature of the app. On the other hand, it is definitely better than users getting the 4031 error at what appears to be random intervals.

"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 - 15:02:40 CDT