ORA-04031: unable to allocate
string bytes
of shared memory
-
Cause:
More shared memory is needed than
was allocated in the shared pool.
-
Action:
If the shared pool is out of memory,
either use the dbms_shared_pool
package to pin large packages,
reduce your use of shared memory, or
increase the amount of available
shared memory by increasing the
value of the INIT.ORA parameters
"shared_pool_reserved_size"
and "shared_pool_size".
If the large
pool is out of memory, increase the
INIT.ORA parameter "large_pool_size".
There are many
valuable resources regarding shared pool sizing,
and ORA-04031 is very commonly encountered in
situations involving the sizing of large pools.
Below is an excerpt from an the article,
Oracle Concepts - Shared Pool and Multi-Threaded
Server (MTS) in which ORA-04031 is addressed
and offered a resolution.
If ORA-04031 is
thrown, consider using the following select:
select
name,
SUM(bytes)
from
V$SGASTAT
where
pool='LARGE
POOL'
group by
ROLLUP(name);
Though the
number of bytes may vary, ORA-04031 is commonly
thrown in operations with configured large
pools.
The exampled
select above offers a summary of the number of
bytes which will reveal the current pool size
and you proximity to the maximum pool size which
is specified within the LARGE_POOL_SIZE
parameter. You will find that when the
LARGE_POOL is increased up to 100%,
ORA-04031 will typically be eliminated.
To help you to
understand more clearly the impact that large
pool sizing can have in resolving ORA-04031, you
may be interested in the statement below:
Sizing the large pool can be complex. The large
pool, if configured must be at least 600
kilobytes in size. Usually for most MTS
applications 600k is enough. However, if PQO
is also used in your Oracle8 environment
then the size of the large pool will
increase dramatically.
The V$SGASTAT dynamic
performance view has a new column in
Oracle8, POOL. The POOL column in the
V$SGASTAT view is used to contain the pool
area where that particular type of object is
being stored. By issuing a summation select
against the V$SGASTAT view a DBA can quickly
determine the size of the large pool area
currently being used.
