Shared Pool Starvation After an SGA Target Was Set Too High

Start with the arithmetic of the host. 200 GB of SGA plus a 64 GB PGA target is 264 GB on a 256 GB machine, so the configuration overcommits physical memory before the instance does any work. Paging follows, and ORA-04030 in the batch sessions is the symptom of a process that cannot get memory rather than of a shared pool bug.

The ORA-04031 has a second, independent driver. With SGA_TARGET set and SHARED_POOL_SIZE left at zero, the shared pool has no meaningful floor, so ASMM is free to shrink it whenever the buffer cache advisor pushes the other way. The alert log resize messages and V$MEMORY_DYNAMIC_COMPONENTS showing the shared pool at roughly 24 GB while the buffer cache grows are the signature of reallocation, not leakage. Confirm with V$MEMORY_RESIZE_OPS and V$MEMORY_DYNAMIC_COMPONENTS over time.

Leakage would look different: the component would grow without a matching shrink elsewhere, and V$SGASTAT would show a specific subpool growing while the number of sessions and cursors stayed flat. Here the KGLHD growth is explained by literal SQL text inflating the library cache, so compare V$SQLAREA version counts, V$SQL_SHARED_CURSOR, and the library cache reload rate before concluding anything about a leak.

Fix the overcommit first, because it is the cheapest and most certain correction. Bring SGA_TARGET down to about 160 GB and PGA_AGGREGATE_TARGET to about 48 GB, leaving roughly 48 GB for the OS, the instance background processes, and page cache. Then set SHARED_POOL_SIZE to a floor such as 40 GB; with ASMM, a nonzero component parameter acts as a lower bound, so ASMM can still tune upward but cannot shrink the pool below the floor again.

Address the literal SQL while you are there. Setting cursor_sharing to FORCE is an immediate mitigation, but the durable answer is bind variables in the application, because forced sharing has its own selectivity and child-cursor costs. Then recheck V$SGASTAT free memory and the reload rate.

HugePages must be handled deliberately. The number of pages must be reserved at the OS level (vm.nr_hugepages in /etc/sysctl.conf, verified in /proc/meminfo) before the instance restarts, and sized for the whole SGA plus a margin. HugePages cannot be used with automatic memory management, so MEMORY_TARGET must remain 0, which it already is here. If pages are reserved but the SGA is later increased beyond them, the instance may fail to start or silently fall back to 4 KB pages, so the page count and the SGA_TARGET have to be changed together.

Before the change, use V$SGA_TARGET_ADVICE, which estimates DB time at each candidate SGA size, and V$SHARED_POOL_ADVICE, which estimates parse time saved at larger pool sizes. For the PGA, V$PGA_TARGET_ADVICE shows the cache hit percentage at each target, so the 48 GB value is chosen against a curve rather than guessed. V$MEMORY_TARGET_ADVICE only applies if automatic memory management is in use, which it is not here.

After the change, judge by three things that were all failing before: no ORA-04031 or ORA-04030 entries in the alert log across a full peak window, no further shrink resizes of the shared pool in V$MEMORY_RESIZE_OPS, and a stable amount of free memory in V$SGASTAT rather than a floor of under 200 MB. Watch V$LIBRARYCACHE for reloads and the get hit ratio, and V$SQLAREA for a growing count of child cursors per SQL ID, which would show whether literal SQL is still flooding the library cache.

Finally confirm the OS side: paging activity should stop, and HugePages_Free should stay near HugePages_Total, meaning the SGA is actually backed by large pages rather than silently falling back.