GC Freelist Session Waits causing slowness and hangs

One of the best things in my job:
I learn from you folks out there. Everyday.

Credits here go to Maciej Tokar who did explain the below topic to me via LinkedIn – thanks a lot, Maciej!

Locks are not being closed fast enough, resulting in gc freelist waits

You can find a reference for Global Cache Freelist in the Oracle Documentation. This issue here can or will lead to database being slow, up to complete hangs. Based on my research it looks as the issue is not related to RAC only but a general thing. In your session waits you’ll spot this:

Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
gc freelist                         Cluster              41.37       8.61

This has been logged as a bugs 21352465 (public) and 18228629 (not public). It causes locks are not being closed fast enough, resulting in gc freelist waits. In conjunction the default for _gc_element_percent seemed to be too low at 120 (or 110 in 11.2.0.4).

Actually the issue can affect not only Oracle Database 12.1.0.2 but also Oracle Database 11.2.0.3 and 11.2.0.4.

See MOS Note:2055409.1 (Database Hangs with High “gc freelist” wait ) for further details.

Solution

• Apply the patch for bug 18228629 on top of a PSU or BP where available
  • See the drop-down list to the right labeled “Release” to access the correct patch for your release
  • Unlike the above MOS Note states in Oracle Database 12.1.0.2 it is only available on top of the January 2016 PSU and BP and two other Exadata BPs – and on Linux only!
• Use the workaround and set _gc_element_percent = 200
  • This will require an instance restart as the parameter can’t be changed dynamically:

    alter system set "_gc_element_percent"=200 scope=spfile;

Epilogue

We’ve had a lot of discussions about underscore parameter in the past weeks. And I’m not a big fan of underscores especially when it comes to upgrades as experiences has shown that having underscores set one day may make it hard to remove them the other day – and underscores can significantly impact the upgrade duration in a negative way.

But on the other hand, if an issue is seriously affecting many customers, and there’s no patch available for your platform and environment right now, what else can one do?

–Mike