Re: ORA-04031 - Which packages should be kept in memory?

From: Alexander Skwar <alexander_at_skwar.name>
Date: Wed, 25 Apr 2007 08:30:43 +0200
Message-ID: <5039556.bHVxtasyXB@kn.gn.rtr.message-center.info>

DA Morgan <damorgan_at_psoug.org> wrote:

> Alexander Skwar wrote:

>> Hello!
>>
>> All of a sudden, I'm getting ORA-04031 error messages on of my Ora9i
>> databases. On various sites on the internet, like
>> http://www.techonthenet.com/oracle/errors/ora04031.php, I find the
>> following:
>>
>> Use the DBMS_SHARED_POOL package to pin large packages.
>>
>> How do I know or decide, which packages I should KEEP in memory
>> with DBMS_SHARED_POOL.KEEP()? I know that it says to keep "large
>> packages" - but I suppose I should only keep those "packages"
>> which are needed. How do I know, which packages are needed? What
>> is "large"?
>>
>> Thanks,
>>
>> Alexander Skwar

> 
> Look at the demos at in Morgan's Library at www.psoug.org under
> DBMS_SHARED_POOL. Also look at DBMS_SESSION.FREE_UNUSED_USER_MEMORY.

Thanks, will do so!

> But I would suggest symptom fighting is not a long term solution. > What version of Oracle (to 3 decimals)

9.2.0.4

> and what is causing the
> problem.

Some homemade application. I don't know the internals of the application. What should I find out about the application?

Error message:

A database error was reported by Oracle. ORA-04031: unable to allocate 20 bytes of shared memory ("shared pool","ALTER SYSTEM FLUSH SHARED_POOL","sql area","logdef : apaclg") ORA-02063: preceding line from ADMM31A
(31ulymsgs007)

I doubt that it's possible to change the SQL statements, as the developers are using some wrappers made available from an application. Those wrappers are not Oracle specific (ie. the same program could be run against a different DBMS, like MS SQL Server, as well).

> That is what needs to be determined.

Okay.

Alexander Skwar Received on Wed Apr 25 2007 - 01:30:43 CDT