Problem with 12.1.0.2 database (3 node RAC cluster) running on Exadata:
We currently have a large number of sessions (41) in one instance of a three instance RAC database waiting with "gc buffer busy acquire". The event parameters indicate that the are all waiting on the same block in the same table (DR$FT_IDCTEXT2$I) - and some have been waiting several days now. From my research - I take this to mean that these sessions are waiting on another session in the same instance to obtain a current copy of a block that has been requested from the global cache. From examples I've found like this one:
https://orainternals.wordpress.com/2012/04/19/gc-buffer-busy-acquire-vs-release/
- it would appear that in most cases there is an "acquiring" session that is waiting for the block from the global cache (wait event gc current request or similar) and sessions waiting with gc buffer busy acquire are waiting to essentially get the block once the local acquiring session has finished with it. This is well and good - but I cannot find any current session in the instance that is reporting this event - its as if that session "forgot" to report its completion and has left the rest hanging.
The waiting sessions are all running the same SQL - a PLSQL package call that I suspect is trying to update or insert in the "table of interest" - DR$FT_IDCTEXT2$I. I can issue an update to this table and roll it back in nodes 2 and 3 (which don't have the waiters) - but if I try the same on node 1 - my sqlplus session shows up in the list of sessions waiting with gc buffer busy acquire.
Any suggestions (short of restarting the instance or database) to better understand and/or resolve this situation? This is, of course, a production database - so my freedom to experiment is very limited. Note that we are planning to try a restart this weekend.
The alert log for this instance shows nothing out of the ordinary occurring - but an ORA-700 event (in another instance) was logged around the time the earliest of the waiting sessions started to wait. I suspect this is the genesis of this situation...
Thanks
Nels