Re: ORA-04031 yet 40% of 'free memory' in V$SGASTAT

Been in this exact situation, as recently as last week on 9.2.0.5.3.

have you checked Metalink for articles on this?
(They are helpful)

you're going to need to take a couple of approaches on this.

Long term:
identify and rewrite non-sharable SQL to properly use bind variables.

Short term:
pin frequently used packages (and other objects) using dbms_shared_pool.keep
(you'll need to put this in a startup trigger for it to be persistent)
increase the size of the shared_pool_reserved_size set the init parameter cursor_sharing=force if all else fails, flush the shared_pool periodically (automatically).

such changes should be tested first, but if your users are getting ora-4031 errors, some triage has to occur immediately.

Before you do anything, I'd glean from v$sql what the worst offenders are in terms of non-reusable sql.

There are a large number of articles on this topic available on the internet.

hth

-bdbafh Received on Thu Mar 31 2005 - 15:41:03 CST