Re: ORA-04031 Errors - Unsharable SQL

"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 - 13:03:16 CDT