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.