Re: ORA-04031 and shared pool

There's a bug in 8.1.5 that means when timed_statistics=TRUE then no sharing of SQL statements is possible -so everything will be being re-parsed like crazy (bye-bye Library Cache).

Extraordinary, but true.

Allegedly fixed in 8.1.6 (and I think we might have heard about it if not)

Regards
HJR "Mark D Powell" <mark.powell_at_eds.com> wrote in message news:178d2795.0108300556.28878cb6_at_posting.google.com... > "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 --