 | | | ORA-04031 error while trying to load a PL/SQL object | ORA-04031 error while trying to load a PL/SQL object 2006-02-06 - By Spears, Brian
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@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Hameed, Amir
Sent: Monday, February 06, 2006 4:32 PM
To: oracle-l@(protected)
Subject: ORA-04031 (See ORA-04031.ora-code.com) 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 (See ORA-04031.ora-code.com): unable to allocate 43712 bytes of
shared memory ("shared pool","WIP_TRANSACTIONS_PKG","PL/SQL
MPCODE","BAMIMA: Bam Buffer")
ORA-06508 (See ORA-06508.ora-code.com): PL/SQL: could not find program unit being called
ORA-06512 (See ORA-06512.ora-code.com): 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:
1. 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 (See ORA-04031.ora-code.com) 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?
2. 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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>ORA-04031 (See ORA-04031.ora-code.com) error while trying to load a PL/SQL object</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=327230822-06022006><FONT face=Arial
color=#0000ff size=2>Pin your package to the sharedpool. This seems to resolve
the issue most times. As a rule I pin all the standard
packages.</FONT></SPAN></DIV>
<DIV> </DIV><!-- Converted from text/rtf format -->
<P><SPAN lang=en-us><FONT face=Tahoma color=#0000ff size=2>Brian
Spears</FONT></SPAN> <BR></P><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Hameed,
Amir<BR><B>Sent:</B> Monday, February 06, 2006 4:32 PM<BR><B>To:</B>
oracle-l@(protected)<BR><B>Subject:</B> ORA-04031 (See ORA-04031.ora-code.com) error while trying to load
a
PL/SQL object<BR></FONT><BR></DIV>
<DIV></DIV><!-- Converted from text/rtf format -->
<P><FONT face="Comic Sans MS" size=2>Folks,</FONT> <BR><FONT
face="Comic Sans MS" size=2>I have an 11i (11.5.0)/9.2.0.6 (64-bit) database
running on Solaris 8 with the following shared pool sizing:</FONT> </P>
<P><FONT face="Comic Sans MS" size=2>Shared_pool: 1.2
GB</FONT> <BR><FONT face="Comic Sans MS" size=2>Db_cache_size: 6 GB</FONT> </P>
<P><FONT face="Comic Sans MS" size=2>ODM is enabled and that is why the cache
size is large. </FONT><BR><FONT face="Comic Sans MS" size=2>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:</FONT></P>
<P><FONT face="Comic Sans MS" size=2>-- --</FONT> <BR><FONT face="Comic Sans MS
"
size=2>Cause: wiltbf failed due to ORA-04031 (See ORA-04031.ora-code.com): unable to allocate 43712 bytes of
shared memory ("shared pool","WIP_TRANSACTIONS_PKG","PL/SQL MPCODE","BAMIMA:
Bam
Buffer")<BR>ORA-06508 (See ORA-06508.ora-code.com): PL/SQL: could not find program unit being
called<BR>ORA-06512 (See ORA-06512.ora-code.com): at line 1<BR></FONT><FONT
face="Times New Roman">----</FONT> </P>
<P><FONT face="Comic Sans MS" size=2>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:</FONT></P>
<P><FONT face="Comic Sans MS" size=2>1. 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 (See ORA-04031.ora-code.com) 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?</FONT></P>
<P><FONT face="Comic Sans MS" size=2>2. 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?</FONT></P>
<P><FONT face="Comic Sans MS" size=2>Any feedback will be appreciated.</FONT>
</P>
<P><FONT face="Comic Sans MS" size=2>Regards</FONT> <BR><FONT
face="Comic Sans MS" size=2>Amir</FONT> </P><BR><BR><BR></BODY></HTML>
|
|
|