Error ORA-04031 "Bamima: Grow Bam Buffer" At Run Time
Padmanabhan Kumar
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?
Peter Moore
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 | p...@chaff.demon.co.uk | "Either he's dead, or my watch |
| DBA | | has stopped!" - Groucho |
| MAT Transport | +44 (171) 410 6373 | |
|_____________________________________________________________________________|
Robin Parker
>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.
>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?
The syntax is :
execute sys.dbms_shared_pool.keep('user.package_name');
You then need to execute something from the package to load it into memory.
If you declare a global variable in the package you can just assign that
varaible a value to load the whole package into memory.
i.e.
execute package_name.dummy_variable := 1;
Hope this helps
Robin.
walter marek
>This error was encountered at run time, where seemingly a package could
>
Designer/2000. Our UNIX sysadmin had to rebuild the kernel to allow for
larger blocks to be created. I think there was a limit of 32k per block
and he changed it to a max of 64k per block.
Since you don't say what exactly you were doing or on what type of
system, this may not be an answer to your problem.
Walt
walter marek
walter marek
change the block size in the UNIX kernel. He changed the max user process
size from 8192 pages(16k) to 16384 pages (32k) in the UNIX kernel.
Good luck,
Walt
Robin Parker
>BTW do you know if DBMS_SHARED_POOL is documented anywhere apart from the Oracle
>White Paper
>that I've seen it on?
I can't recall seeing any other documentation on it, but I think I do remember
reading somewhere that these utilities may NOT be included in later versions
of Oracle (but I stand to be corrected). Presumably Oracle would then need to
provide some other method of pinning which would then be documented in the
manuals (but then again knowing Oracle .... :) )
Robin
Peter Moore
# >You need to run SYS.DBMS_UTILITIES.KEEP_PACKAGE('package_name');
# >and then compile the package.
#
# >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?
#
#
# The syntax is :
# execute sys.dbms_shared_pool.keep('user.package_name');
#
# You then need to execute something from the package to load it into memory.
#
# If you declare a global variable in the package you can just assign that
# varaible a value to load the whole package into memory.
#
# i.e.
#
# execute package_name.dummy_variable := 1;
#
# Hope this helps
#
# Robin.
#
#
Aah. Thank you Robin.
The curse of only having a connection from home. I couldn't be bothered to log in at work to
check the syntax.
Thanks again for prompting.
BTW do you know if DBMS_SHARED_POOL is documented anywhere apart from the Oracle White Paper
that I've seen it on?
Cheers
Thomas J Kyte
>> SNIP <
>
>>BTW do you know if DBMS_SHARED_POOL is documented anywhere apart from the Oracle
>>White Paper
>>that I've seen it on?
>
>reading somewhere that these utilities may NOT be included in later versions
>of Oracle (but I stand to be corrected). Presumably Oracle would then need to
>provide some other method of pinning which would then be documented in the
>manuals (but then again knowing Oracle .... :) )
>
>Robin
All of the DBMS_* packages are documented in their package specification. For
example, in the database you could issue the query:
select text
from all_source
where name = 'DBMS_SHARED_POOL'
order by line
/
They are also in text format in the $ORACLE_HOME/rdbms/admin subdirectory.
What you would see for the above query would be:
create or replace package dbms_shared_pool is
------------
-- OVERVIEW
--
-- This package provides access to the shared pool. This is the
-- shared memory area where cursors and PL/SQL objects are stored.
----------------------------
-- PROCEDURES AND FUNCTIONS
--
procedure sizes(minsize number);
-- Show objects in the shared_pool that are larger than the specified
-- size. The name of the object is also given which can be used as
-- an argument to either the 'keep' or 'unkeep' calls below. You should
-- issue the SQLDBA or SQLPLUS 'set serveroutput on size xxxxx'
-- command prior to using this procedure so that the results will
-- be displayed.
-- Input arguments:
-- minsize
-- Size, in kilobytes, over which an object must be occupying in the
-- shared pool, in order for it to be displayed.
procedure keep(name varchar2, flag char DEFAULT 'P');
-- Keep an object in the shared pool. Once an object has been keeped in
-- the shared pool, it is not subject to aging out of the pool. This
-- may be useful for certain semi-frequently used large objects since
-- when large objects are brought into the shared pool, a larger
-- number of other objects (much more than the size of the object
-- being brought in, may need to be aged out in order to create a
-- contiguous area large enough.
-- WARNING: This procedure may not be supported in the future when
-- and if automatic mechanisms are implemented to make this
-- unnecessary.
-- Input arguments:
-- name
-- The name of the object to keep. There are two types of objects:
-- PL/SQL objects which are specified by name, and SQL cursor
-- objects which are specified by a two-part number (indicating
-- a location in the shared pool). For example:
-- dbms_shared_pool.keep('scott.hispackage')
-- will keep package HISPACKAGE, owned by SCOTT. The names for
-- PL/SQL objects follows SQL rules for naming objects (i.e.,
-- delimited identifiers, multi-byte names, etc. are allowed).
-- A cursor can be keeped by
-- dbms_shared_pool.keep('0034CDFF, 20348871')
-- The complete hexadecimal address must be in the first 8 characters.
-- The value for this identifier is the concatonation of the
-- 'address' and 'hash_value' columns from the v$sqlarea view. This
-- is displayed by the 'sizes' call above.
-- Currently 'TABLE' and 'VIEW' objects may not be keeped.
-- flag
-- This is an optional parameter. If the parameter is not specified,
-- the package assumes that the first parameter is the name of a
-- package and will resolve the name. It can also be set to 'P' or
-- 'p' to fully specify that the input is the name of a package.
-- In case the first argument is a cursor address and hash-value, the
-- parameter should be set to any character except 'P' or 'p'.
-- Exceptions:
-- An exception will raised if the named object cannot be found.
procedure unkeep(name varchar2, flag char DEFAULT 'P');
-- Unkeep the named object.
-- WARNING: This procedure may not be supported in the future when
-- and if automatic mechanisms are implemented to make this
-- unnecessary.
-- Input arguments:
-- name
-- The name of the object to unkeep. See description of the name
-- object for the 'keep' procedure.
-- Exceptions:
-- An exception will raised if the named object cannot be found.
end;
Note in particular that some sections, like the following:
procedure unkeep(name varchar2, flag char DEFAULT 'P');
-- Unkeep the named object.
-- WARNING: This procedure may not be supported in the future when
-- and if automatic mechanisms are implemented to make this
-- unnecessary.
State that at some time in the future, this procedure may not be supported and why.
Hope this helps
Thomas Kyte
tk...@us.oracle.com
Oracle Government
Peter Sharman
>> SNIP <
>
>>BTW do you know if DBMS_SHARED_POOL is documented anywhere apart from the Oracle
>>White Paper
>>that I've seen it on?
The package creation script is reasonably well documented (dbmspool.sql
in ORACLE_HOME/rdbms/admin on a Unix box).
>I can't recall seeing any other documentation on it, but I think I do remember
>reading somewhere that these utilities may NOT be included in later versions
>of Oracle (but I stand to be corrected). Presumably Oracle would then need to
>provide some other method of pinning which would then be documented in the
>manuals (but then again knowing Oracle .... :) )
Correct. The documentation in the package creation script says that the
pacakge may NOT be included if Oracle can work out some way of doing the
same thing automatically.
>
>Robin
--
Regards
Pete
========================================================================
Peter Sharman Email:
psha...@au.oracle.com
Education Consultant Phone: +61.62.479.366
(int'l)
ORACLE Education (06)247 9366 (local)
"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA
select standard_disclaimer, witty_saying from company_requirements;
========================================================================