RE: ORA-04031 error while trying to load a PL/SQL object
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- To: <Amir.Hameed@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Feb 2006 14:21:18 -0800
Amir,
An ORA-4031 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 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 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 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)
Co-Author: Oracle Database 10g Insider Solutions
http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/
** 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hameed, Amir
Sent: Monday, February 06, 2006 1:32 PM
To: oracle-l@xxxxxxxxxxxxx
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:
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 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
Other related posts:
