ORA-04031: unable to allocate s bytes of shared memory

ORA-04031: unable to allocate s bytes of shared memory

The Oracle shared pool contains Oracle's library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database as well as other purposes. Hence, the shared pool is a key component, so it's necessary for the Oracle database administrator to check for shared pool contention.

The main components of the shared pool are the library cache, the dictionary cache, and, depending on your configuration, the result cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. The result cache stores the results of queries and PL/SQL function results. Many of the caches in the shared pool automatically increase or decrease in size, as needed, including the library cache and the dictionary cache. Old entries are aged out of these caches to accommodate new entries when the shared pool does not have free space.

A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.
A number of features make large memory allocations in the shared pool: for example, the shared server, parallel query, or Recovery Manager. Oracle recommends segregating the SGA memory used by these features by configuring a distinct memory area, called the large pool.

Allocation of memory from the shared pool is performed in chunks. This allows large objects (over 5k) to be loaded into the cache without requiring a single contiguous area, hence reducing the possibility of running out of enough contiguous memory due to fragmentation.
Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5k. To allow these allocations to occur most efficiently, Oracle segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool.

Reason for the Error:
~~~~~~~~~~~~~~~~~~~~~~~~~~~
-    Heavy fragmentation of the shared pool - Oracle is trying to load something into the sga, and the free space available is so fragmented that there is not enough contiguous free space to hold the piece trying to be loaded.
-    Not enough memory in shared pool – The parameters for shared pool are not set properly due to which shared pool have not enough memory to support the operation.
-    Too many pinned packages - If you have pinned lots of packages with dbms_shared_pool.keep, they have not leave enough room for new work.

The Oracle documentation has these notes on the ORA-04031 error:
ORA-04031: unable to allocate nn 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".
The ORA-04031 error also happens when you are using automatic memory management, and the memory specified is too small.


Resolution for the Error:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Try to increase shared_pool_size in init.ora file. If it seems large enough then improve segmentation in shared pool by reserving part of shared pool for large objects using parameter SHARED_POOL_RESERVED_SIZE. It is possible to decide what "large object" is by using parameter “SHARED_POOL_RESERVED_MIN_ALLOC”.

You can issue “alter system flush shared_pool” in the database and re-execute the failed operation.
Note: It clears the cached Data Dictionary, Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

Oracle Support is also providing Automatic Diagnostic tools. We just need to attach and provide the associated trace file generated by ORA-04031. The DBA should look at the current SGA dynamic components and SGA resize operations first before trying to give more memory to SGA/shared pool.

Fragmentation of shared pool memory is also another possible cause of Error.
So even it appears you have free space in shared pool, you can't allocate enough space for a query. If the problem is due to shared pool fragmentation then no matter how much memory you give to shared pool, eventually the problem will come back again if you don't deal with the source of the problem.
If your pool is fragmented then no matter how many times you increase your SHARED_POOL_SIZE parameter and bounce the database, the problem will come again after sometime. In this case it will be better if you approach the root cause for the fragmentation and try to resolve that issue to permanently fix this problem. Like one cause for fragmentation is not using bind variables in your sql statements or complex SELECT queries that run with huge list of input parameters to IN operator and with OR concatenation. The shared pool is broken up into pieces. The bigger it is the harder it is to maintain. If you are not using binds, and you are hitting 4031, the right way to go is "down" not "up" that is reduce your shared pool size, but everyone goes "up" and that pushes the problem back a bit -- but makes it "bigger" and if you are hard parsing like mad - you are making HUGE requests (latching -- serialization) on the shared pool -- people looking for memory are locked out by people using memory, things start to "crawl" (to the point where people say "it just stopped", it hasn't stopped, it is just really slow).


Views to monitor sga memory:

v$sga, v$sgastat, v$sga_dynamic_components, v$sga_dynamic_free_memory, v$shared_pool_advice and v$shared_pool_reserved.

Script for monitoring Oracle Automatic Shared Memory Management:

SELECT
component,
oper_type,
oper_mode,
parameter,
initial_size/1024/1024 "Initial_MB",
TARGET_SIZE/1024/1024  "Target_MB",
FINAL_SIZE/1024/1024   "Final_MB",
status,
TO_CHAR(start_time, 'dd-mon-yyyy hh24:mi:ss') started,
TO_CHAR(end_time, 'dd-mon-yyyy hh24:mi:ss') ended
FROM v$sga_resize_ops
ORDER BY started
/

# Above will tell you that how much resizing operation are being performed on dynamic components of the SGA and whether they were successful or result in an error.

select
   component,
   current_size/1024/1024 "CURRENT_SIZE",
   min_size/1024/1024 "MIN_SIZE",
   user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
   last_oper_type "TYPE"
from
   v$sga_dynamic_components;


col BYTES_MB format 99999.99
select pool, name, bytes/1024/1024 as "BYTES_MB"
from v$sgastat
where pool='shared pool'
and name='free memory'
/

-    select sum(value) from v$sga;
-    select sum(bytes) from v$sgastat;
-    select sum(current_size) from v$sga_dynamic_components;
-    select * from v$sga_dynamic_free_memory;