Re: ORA-04031 Errors - Unsharable SQL

Hello Patrick,

You should also look on metalink at bug 1649372. It describes the same symptoms. It can be fixed by setting the undocumented parameter: _db_handles_cached=0.

HTH Berno

Patrick schreef:

> 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
Received on Wed Oct 10 2001 - 01:22:52 CDT