Re: ORA-04031 Errors - Unsharable SQL

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..."