Re: ORA-04031 Errors - Unsharable SQL

It might be helpful to tell us which Oracle version you're on ... I had a couple of 8.1.7.0 databases on AIX & HPUX who frequently had this error -
a flush of the shared pool didn't help - the persistent memory kept on growing. Apparantly
there's a memory leak in 8.1.7.0 that's fixed with the 8.1.7.2 patchset...

Then again - you might be on of those guys who's still running 7.2.3 ....

cheers

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ts3teki2sqpn79_at_news.demon.nl...
>
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:3BC1E83C.2D10_at_yahoo.com...
> > Patrick wrote:
> > >
> > > I have a database that supports a custom developed application and it
is
> > > currently suffering from a large number of ORA-04031 errors. I have
> read
> > > everything I could find on MetaLink and am reasonably assured that I
> have found
> > > the problem. I believe that one of the stored procedures is
unsharable.
> > > The sysmptoms I am seeing is that the free memory in the SGA is
rapidly
> > > decreasing at the same time that the sql area is rapidly increasing.
> Upon
> > > querying the sql area, I foind that one procedure appears around 600
> times in
> > > less than hour. Each procedure call is being considered as a seperate
> SQL
> > > statement, probably because of the use of literals in the call.
> > > My question is, does anyone have any good resources/documentation for
> fixing
> > > such a problem? The app was developed by an internal development team
> at one of
> > > our remote locations. This is their first foray into both SQL and
JAVA
> > > programming. The application uses servlets and JSP's with WebLogic as
> the app
> > > server. At this point I am most concerned with them making the
> procedure call
> > > sharable, so documentatio that describes this in detail would be most
> desirable.
> > > Currently, I must flush the sahreed pool every 6 hours to prevent the
> ORA-04031
> > > errors. If anyone has a better short term solution I would be most
> interested
> > > in hearing it. Thanks in advance for any input you can provide.
> > >
> > > later ........
> > > Patrick
> >
> > Very simply - literals = problems. Forget about your shared pool and
> > take a look at your CPU time and waits on latches. If you don't use
> > bind variables you are asking for grief...
> >
> > If you're on 8.1.x, you *may* get some joy with 'cursor_sharing'
> > parameter, but test extensively first - there are some side-effects.
> >
> > Better still, shoot the developers - if they are not using bind
> > variables, they should not be working with Oracle.
> >
> > hth
> >
> > --
> > ==============================
> > Connor McDonald
> >
> > http://www.oracledba.co.uk
> >
> > "Some days you're the pigeon, some days you're the statue..."
>
>
> Connor,
> could you please send this to *all* Oracle/Java developers in my firm?
>
> Regards,
>
> Sybrand Bakker
> Senior Oracle DBA
>
>
>
Received on Mon Oct 08 2001 - 15:06:44 CDT