Sev1: ORA-04031 unable to allocate shared memory. Sessions fail and the database grinds to a halt

#Symptoms

• Errors such as ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim heap").
• Logins fail, SQL parses fail, background processes may report errors in the alert log.
• Often builds up over days, or appears suddenly after a release.

#Step 1: read the error (2 minutes)

The pool name in the error matters:

SELECT originating_timestamp, message_text FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-04031%' AND originating_timestamp > SYSTIMESTAMP - INTERVAL '6' HOUR
ORDER BY originating_timestamp DESC FETCH FIRST 20 ROWS ONLY;

#Step 2: buy time

If a sysdba session works, check current sizes:

SELECT component, ROUND(current_size/1024/1024) AS mb, ROUND(min_size/1024/1024) AS min_mb
FROM v$sga_dynamic_components WHERE current_size > 0 ORDER BY current_size DESC;
SHOW PARAMETER sga_target
SHOW PARAMETER shared_pool_size

With ASMM, setting a minimum for the shared pool makes Oracle move memory from the buffer cache, online:

ALTER SYSTEM SET shared_pool_size = 6G SCOPE = BOTH;

This only works if sga_target has room to shrink other components. If sga_target is already at sga_max_size, you cannot grow it without a restart.

Warning

ALTER SYSTEM FLUSH SHARED_POOL frees memory but forces every session to hard parse again, which on a busy system causes a library cache storm. Use it only as a last resort, outside peak, and expect a spike.

#Step 3: find what is filling it

Top shared pool allocations:

SELECT * FROM (
  SELECT pool, name, ROUND(bytes/1024/1024) AS mb FROM v$sgastat WHERE pool = 'shared pool' ORDER BY bytes DESC
) FETCH FIRST 15 ROWS ONLY;

Literal SQL that is never shared (same statement, different literals):

SELECT force_matching_signature, COUNT(*) AS copies, MIN(sql_id) AS example_sql_id,
       ROUND(SUM(sharable_mem)/1024/1024) AS mb
FROM v$sqlarea
WHERE force_matching_signature <> 0
GROUP BY force_matching_signature
HAVING COUNT(*) > 100
ORDER BY copies DESC FETCH FIRST 10 ROWS ONLY;

High version counts (one SQL, many child cursors):

SELECT sql_id, version_count, ROUND(sharable_mem/1024/1024) AS mb, SUBSTR(sql_text, 1, 80) AS sql
FROM v$sqlarea WHERE version_count > 200 ORDER BY version_count DESC FETCH FIRST 10 ROWS ONLY;

Why child cursors are not shared:

SELECT * FROM v$sql_shared_cursor WHERE sql_id = '<sql_id>' AND ROWNUM <= 5;

Free memory trend and resize operations:

SELECT component, oper_type, initial_size/1024/1024 AS from_mb, final_size/1024/1024 AS to_mb, end_time
FROM v$sga_resize_ops ORDER BY end_time DESC FETCH FIRST 20 ROWS ONLY;

Frequent "shrink buffer cache / grow shared pool" cycles show sustained pressure.

#Step 4: fix the cause

Temporary cursor_sharing relief for a single application user via a logon trigger is safer than system-wide:

CREATE OR REPLACE TRIGGER app_cursor_sharing AFTER LOGON ON DATABASE
BEGIN
  IF USER = 'APP_LEGACY' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing = FORCE';
  END IF;
END;
/

Caution

cursor_sharing = FORCE can change execution plans (bind peeking on previously literal values). Test critical queries and keep SQL plan baselines for them.

#Step 5: right-size for the future

• Use the shared pool advisor:

SELECT shared_pool_size_for_estimate/1024/1024 AS size_mb, estd_lc_time_saved_factor, estd_lc_load_time
FROM v$shared_pool_advice ORDER BY shared_pool_size_for_estimate;

• If the SGA is too small overall, plan a restart window to increase sga_max_size and sga_target (check OS memory and HugePages configuration first).

#Step 6: confirm

• No new ORA-04031 in the alert log for 24 hours.
• Shared pool free memory stable (v$sgastat 'free memory').
• Version counts and literal SQL counts falling after the app fix.

#Prevent it next time

• Alert on ORA-04031 in the alert log and on rapid shared-pool growth in v$sga_resize_ops.
• Code review for bind variable use in database access code.
• Set sensible minimums for shared_pool_size and large_pool_size under ASMM.