Beware of uncommitted updates during heavy selects in Oracle RAC

One of the often overlooked aspect of Oracle RAC is the impact of large changes on heavily accessed tables on overall RAC responsiveness. Since an Oracle RAC has the added responsibility of synchronizing with other nodes in a cluster, this is also its Achilles heel.

Consider a scenario where you have a massive update going on one of the nodes on a single table that you don't commit for a long time. Now, consider issuing tons of select statements against this table from all the nodes in the RAC cluster. What should happen? In effect, this should cause all the select statements to go and look in the UNDO tablespace for the before image of blocks for this table that was changed by the updates. This is because select starting after the update cannot see any uncommitted data. So why should this be an issue? Isn't this DB 101? Yes... it is but in the case of RAC this takes on a different dimension.

What you will start seeing depending on the volume of selects is that your AWR reports will show a ton of the following wait events:     1. gc cr block busy     2. gc cr block 2-way     3. gc cr block congested     4. gc buffer busy acquire

And a whole bunch of other "gc stuff" . So all of this points to the Global Cache. So what is going on? What is going on is that since selects all have to come and look for UNDO blocks for the table from the node that is executing the massive update, they start pinging that particular node where the update is running across the private interconnect and jam the interconnect. The traffic into that node spikes, average active sessions, CPU usage all increase a ton. "Cluster" wait events take up a majority of the overall waits in the database. If this were to be a non-RAC system then the extra work of pinging across the interconnect etc would be moot and you probably won't notice the degradation as much as in Oracle RAC.

So the moral here is two fold, if you are stuck with Oracle RAC then for heavens sake make sure your updates to large, often queried tables are optimized, secondly update in small batches and commit, commit and commit.