Re: ORA-04031 and shared pool

"MT" <mtechera_at_wpmc.com> wrote in message news:<9mjhk2$5tg$1_at_news.online.de>...
> 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
> > > >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
> > > >
> > > >
> > > --
> > > 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 --

No support has not mentioned any issues with timed statistics, which we do have set to true. We need the stats for occassional tuning and have had them set to true in our production instance since version 7.0.

I may well ask about it though.

• Mark --