Re: ORA-04031 Errors - Unsharable SQL

Sybrand Bakker wrote:
>
> "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

A suggestion - many moons ago I worked at a place where we would send out 'worst SQL of the week' to the development group. If it wasn't fixed within a few days, we named and shamed the person responsible.

Loved that place....

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."