Shared_pool defragmentation problem
We have frequent shared pool allocation problems at different times, sometimes it causes downtime:
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknown object","KGLH0^de3fd698","kglHeapInitialize:temp")
ORA-04031: unable to allocate 20504 bytes of shared memory ("shared pool","unknown object","sga heap(3,0)","KTI SGA freeable small po")
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknown object","KGLH0^74726419","kglHeapInitialize:temp")
ORA-04031: unable to allocate 168 bytes of shared memory ("shared pool","PKG_PRICING_DEVIATION","KGLH0^74726419","kglxscol")
During the problem, shared pool had 3GB of free memory, but still was not able to use and tried to increase, which failed. We have identified that shared pool had a defragmentation and sessions were not able to find large enough chunks of memory to satisfy request. (This was reported by ORA-4031 Troubleshoot tool, see the picture bellow)