Error ORA-04031 "Bamima: Grow Bam Buffer" At Run Time

From: Peter Moore <pt_at_chaff.demon.co.uk>
Date: 1995/06/17
Message-ID: <250718469wnr_at_chaff.demon.co.uk>#1/1


In article: <3rs4v7$nqb_at_spruce.citicorp.com> pkumar_at_gftpd-mail.citicorp.com (Padmanabhan Kumar) writes:
# This error was encountered at run time, where seemingly a package could
# not be loaded from the database into the shared memory pool. The package and
# its body are of the total size of abount 110K. The shared pool size is 9MB.
#
# Any answer to this problem?
#

What happens is that the SQL cache gets 'fragmented'. If a piece of code hasn't been used for a while it gets aged out under a LRU algorithm. If, next time it is needed, there is not enough contiguous space in the shared pool you get this error (or something like it).

There used to be an error (in 7.0.15 or before, I believe) which meant that code was getting aged out when it shouldn't be. That could cause this problem. So you could try getting a more up-to-date version of the database.

More likely (we have the same situation) your package is used 2 or 3 times a day and is getting aged out 'coorectly'. Your best bet is to 'pin' the package in the Shared pool.

You need to run SYS.DBMS_UTILITIES.KEEP_PACKAGE('package_name'); and then compile the package.

We have added a list of these 'keep' statments to our DB startup scripts. They ensure that the package will _never_ be aged out.

PS - you may need to check the syntax of that statement - I'm doing it from memory and couldn't find the procedure in the DBA Guide! Perhaps someone could jump in and help out?

HTH Peter.

-- 

|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
| PETER  MOORE  | pt_at_chaff.demon.co.uk       | "Either he's dead, or my watch |
|     DBA       |                            |  has stopped!" - Groucho       |
| MAT Transport | +44 (171) 410 6373         |                                |
|_____________________________________________________________________________|
Received on Sat Jun 17 1995 - 00:00:00 CEST

Original text of this message