Shared Pool Memory Leak in 12c RAC

A couple of minutes ago I wrote about Parse Times in 12c. When investigating this issue we came across another one. The shared pool was growing and growing, stealing a lot of memory from the buffer cache. We stopped that by setting a minimum size for “db_cache_size” to stop shrinking the buffer cache. It is not that there isn’t enough memory, the MEMORY_TARGET was set to 42GB (the magic number). But the shared pool was consuming more than 20GB of that memory. We stopped the buffer cache from shrinking more and more at around 8GB. Now the shared pool started to steal it’s memory from the PGA.
So we started to identify what area is consuming the memory inside the shared pool:

SQL> select * from (
  2  select name, bytes from v$sgastat where pool ='shared pool' order by 2 desc
  3  )
  4  where rownum <11;

NAME                                      BYTES
-------------------------- --------------------
ges enqueues                      5.981.720.032
ges resource dynamic              5.568.662.280
value block free list             2.283.277.296
ges resource dynamic              1.799.279.656
free memory                         983.839.992
gcs resources                       696.864.960
SQLA                                534.089.824
KGH: NO ACCESS                      399.729.056
gcs shadows                         387.147.200
KGLH0                               268.811.064

Ok, ges things… A quick search in My Oracle Support revealed a bug which exactly met our findings ORA-04031 Errors Occurring with High “ges resource dynamic” & “ges enqueues” Memory Usage In The Shared Pool.

The workaround mentioned in the document helped, now the shared pool is quiet again.

Advertisements