ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^212","kglseshtTable")

We recently encountered the following error in 19c, which typically occurs when the database needs additional shared memory. In most of the cases setting MAX_SGA_SIZE to a higher value will resolve the issue.

Below are some possible cause

•  Insufficient memory allocated via initialization parameters
•  Fragmentation in app design
•  Auto tuning issues
•  A Bug causing the issue
•  Memory leaks

ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^334","kglseshtTable")
< ORA-00604: error occurred at recursive SQL level 1 < ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")
< ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^394","kglseshtTable")


The error message will provide the amount of memory unavailable, memory pool facing the issue and failed allocation details

 

I would highly suggest running below query to get the optimal value for SGA_TARGET
Select * from V$SGA_TARGET_ADVICE

Note that in order to initialization parameters to take into effect we need to bounce the instance.

AGLT>oerr ora 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams 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
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
// parameter MAX_SGA_SIZE.

Refer below links for Oracle support notes

• This Oracle support note provides information about ORA-04031 related bugs and which release they were fixed

OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" (Doc ID 4031.1)

• This Oracle note provides detailed troubleshooting and diagnosing details

Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1)

• This Oracle note provides detailed understanding and tuning of the of the shared pool

NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool

Thanks & Regards,

https://oracleracexpert.com