Long term : Have the application use bind variables. If you are pinning : database startup is the best time to pin. Are you pinning everyday and including new objects? Using algorithm? What does the algorithm do? If you have such a big SGA : then maybe pin all active PL/SQL on startup and forget it. Can you tell the purpose of your flush? Please note that flush may not get rid of fragmentation. Thanks
John Kanagaraj <john.kanagaraj@(protected)> wrote: Amir,
An ORA-4031 (See ORA-4031.ora-code.com) error on an Apps database needs to be considered in the same way as a 'normal' database. It is probably caused some program or process that executes a large number of small, non-resusable SQL. One normally thinks of user written SQL, but I have seen a severe case of ORA-4031 (See ORA-4031.ora-code.com) caused by a cronjob that regrants SELECT access to a read-account. The SQL generated thousands of 'grant select on <> to read_only_account' on all objects and held the shared pool/lib cache latch for long periods, while polluting it with small statements that broke up contiguos free space. Do you encounter lots of lib cache/shared pool latching? If so, you must trace them to see where/why they occur and you might discover the source of the 4031s.... YMMV!
Fyi, ORA-4031 (See ORA-4031.ora-code.com) is considered a 'user error', not a system error for whatever reason. You need to have an entry in the init.ora using the event parameter for an ORA-4031 (See ORA-4031.ora-code.com) error to be logged in the alert log. If you can check the older init.ora for the 8i instance, you might see this... Btw, there was a nice article from COE in either Metalink or OTN about the shared pool recently (don 't have the URL but it was mentioned in this list).
Let the list know if you come across something, since that is how we all learn!
John Kanagaraj <>< DB Soft Inc Phone: 408-970-7002 (W)
** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **
-- ---- ---- ---- ---- ---- ----- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Hameed, Amir Sent: Monday, February 06, 2006 1: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
-- ---- ---- ---- ---- ---- ----- Relax. Yahoo! Mail virus scanning helps detect nasty viruses! <div>Long term : Have the application use bind variables.</div> <div>If you are pinning : database startup is the best time to pin. Are you pinning everyday and including new objects? Using algorithm? What does the algorithm do ? If you have such a big SGA : then maybe pin all active PL/SQL on startup and forget it. <BR>Can you tell the purpose of your flush? Please note that flush may not get rid of fragmentation. Thanks</div> <div><BR><B><I>John Kanagaraj <john.kanagaraj@(protected)></I></B> wrote:</div> <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"> <META content="MSHTML 6.00.2900.2802" name=GENERATOR> <DIV dir =ltr align=left><SPAN class=690004321-06022006><FONT face=Arial color=#0000ff size=2>Amir,</FONT></SPAN></DIV> <DIV dir=ltr align=left><SPAN class=690004321 -06022006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=690004321-06022006><FONT face=Aria l color=#0000ff size=2>An ORA-4031 (See ORA-4031.ora-code.com) error on an Apps database needs to be considered in the same way as a 'normal' database. It is probably caused  ;some program or process that executes a large number of small, non-resusable SQL. One normally thinks of user written SQL, but I have seen a severe case of ORA-4031 (See ORA-4031.ora-code.com) caused by a cronjob that regrants SELECT access to a read-account. The SQL generated thousands of 'grant select on <> to read_only_account' on all objects and held the shared pool/lib cache latch for long periods, while polluting it with small statements that broke up contiguos free space. Do you encounter lots of lib cache/shared pool latching? If so, you must trace them to see where/why they occur and you might discover the source of the 4031s.... YMMV!</FONT></SPAN></DIV> <DIV dir=ltr align=left><SPAN class=690004321 -06022006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=690004321-06022006><FONT face=A rial color=#0000ff size=2>Fyi, ORA-4031 (See ORA-4031.ora-code.com) is considered a 'user error', not a system error for whatever reason. You need to have an entry in the init.ora using the event parameter for an ORA-4031 (See ORA-4031.ora-code.com) error to be logged in the alert log. If you can check the older init.ora for the 8i instance, you might see this... Btw, there was a nice article from COE in either Metalink or OTN about the shared pool recently (don't have the URL but it was mentioned in this list).</FONT></SPAN>< /DIV> <DIV dir=ltr align=left><SPAN class=690004321-06022006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=690004321-06022006><FONT face=Arial color=#0000ff size=2>Let the list know if you come across something, since that is how we all learn!</FONT></SPAN ></DIV> <DIV dir=ltr align=left><SPAN class=690004321-06022006><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=690004321-06022006> <DIV align=left><F ONT face=Arial size=2>John Kanagaraj <><<BR>DB Soft Inc<BR>Phone: 408-970 -7002 (W)</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV align=left><FONT face=Arial size=2>Co-Author: Oracle Database 10g Insider Solutions</FONT></DIV> <DIV align=left><FONT face=Arial size=2><U><FONT color= #0000ff size=2>http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/</U>< /FONT></FONT></DIV> <DIV align=left> <DIV align=left><FONT face=Arial size=2>< /FONT> </DIV> <DIV align=left><FONT face=Arial size=2>** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **</FONT></DIV></DIV></SPAN></DIV><FONT face=Arial color=#0000ff size=2></FONT><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 1: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 --> <div><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> </div> <div><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> </div> <div><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></div> <div><FONT face="Comic Sans MS" size=2>-- --< /FONT> <BR><FONT face="Comic Sans MS" size=2>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")<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> </div> <div><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> </div> <div><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></div> <div><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></div> <div><FONT face="Comic Sans MS" size=2>Any feedback wi ll be appreciated.</FONT> </div> <div><FONT face="Comic Sans MS" size=2>Regards< /FONT> <BR><FONT face="Comic Sans MS" size=2>Amir</FONT> </div><BR><BR><BR>< /BLOCKQUOTE><BR><p> <hr size=1>Relax. Yahoo! Mail <a href="http://us.rd.yahoo.com/mail_us/taglines/viruscc/*http://communications .yahoo.com/features.php?page=221">virus scanning</a> helps detect nasty viruses!
