RE: ORA-04031 error while trying to load a PL/SQL object

From: Spears, Brian <BSpears_at_Limitedbrands.com>
Date: Mon, 6 Feb 2006 17:10:01 -0500
Message-ID: <50938E6468CFED40AE0FD7A507D96D340207D23A@EXCHSERV2.Limited.brands.com>

Pin your package to the sharedpool. This seems to resolve the issue most times. As a rule I pin all the standard packages.

Brian Spears

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir Sent: Monday, February 06, 2006 4:32 PM
To: oracle-l_at_freelists.org
Subject: ORA-04031 error while trying to load a PL/SQL object

Folks,
I have an 11i (11.5.0)/9.2.0.6 (64-bit) database running on Solaris 8 with the following shared pool sizing:

Shared_pool: 1.2 GB
Db_cache_size: 6 GB

ODM is enabled and that is why the cache size is large. We run hot backups on this database (using use-managed backup procedure) and flush the shared pool every night after the hot backup (this process is scripted into the backup process). I pin ~ 300 packages into SGA based upon an algorithm in the pinning script. We had an issue this past Friday where some concurrent programs failed and reported the following error in their log files:

Cause: wiltbf failed due to ORA-04031: unable to allocate 43712 bytes of shared memory ("shared pool","WIP_TRANSACTIONS_PKG","PL/SQL MPCODE","BAMIMA: Bam Buffer")

ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
----

The action taken to remediate the problem was that we shutdown the concurrent managers and then flushed the shared pool (without bouncing the database) and the problem went away. I now have the following questions:

I have had this issue about two years ago when I was running 64-bit 8.1.7.4/11.5.6 but at that time Oracle had reported the ORA-04031 in the database alert log file but this time it did not. I was expecting it to report this message in the alert file. Could someone please explain whether this message does not necessarily have to appear in the alert log file or that it should have and that this might be a bug?
I was under the impression that the shared pool algorithm in 9i2 has changed from 8.1.7.4 and that the shared pool fragmentation is not an issue anymore; but it seems that I ran into a shared pool fragmentation scenario and even with the daily flush, the shared pool still got extremely fragmented. Prior to the past week, we used to bounce the database every night after the backup but we stopped doing that about 8 days ago and I believe that it contributed to the shared pool fragmentation. Is flushing the shared pool on daily basis not enough to cleanup fragmentation if one wants to keep the instance running for weeks?

Any feedback will be appreciated.

Regards
Amir

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 06 2006 - 16:10:01 CST