| |
 |
|
ORA-04031: unable to allocate string bytes of shared
memory("string","string","string","string") tips
Oracle Error Tips by Burleson Consulting
|
Oracle docs note this about ORA-04031:
ORA-04031: unable to allocate
string bytes
of shared memory ("string","string","string","string")
-
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.
For the full article regarding ORA-04031 and
large pool sizing, check out this link:
http://www.dba-oracle.com/concepts/shared_pool_mts.htm
Here is another example from
Oracle
Forums.
Question:
To resolve ORA-04031, I need to manually
allocate memory for the shared pool, but the ORA-04031 is still occurring.
How can I properly allocate the value to resolve ORA-04031?
Answer:
With SGA_TARGET, the shared_pool_size
is overlooked by the parameter file. IIf this is causing ORA-04031 to be
thrown, you need to run statspack reports so that you know there is space to
allocate, even if the shared pool looks like it has enough free space.
The shared pool fragment should be trouble-shot for the source of ORA-04031, and
you may want to try to increase the SHARED_POOL_SIZE and restart the
database.
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
