Why Uncommitted Updates in Oracle RAC Trigger Massive GC Waits and How to Fix Them

Background

In a two‑node RAC (AIX 6.1 + Oracle 11.2.0.3.3) AWR reports revealed that the wait events gc buffer busy acquire and gc cr block busy dominate the Top‑5 timed foreground events, especially for the SQL

select t.* from SD.SYS_PARAMETER t where t.PARAM_CLASS in (:1 , :2 )

. Although the application showed no errors, these waits indicate serious contention.

Analysis of AWR Data

gc buffer busy acquire ranks in the Top‑5 timed foreground events.

gc cr block busy follows DB CPU after gc buffer busy acquire.

The problematic SQL spends 85% of its execution time waiting for cluster‑related events.

Capture % shows 89.46% of gc buffer busy acquire waits are caused by this SQL.

CR Blocks Received accounts for 43% of the waits, indicating heavy cross‑node block transfers.

Understanding gc buffer busy acquire and gc cr block busy

When a block is not in the local buffer cache but exists in the remote node’s cache, the local LMS fetches it via the private network (cache‑fusion). If the remote node has modified the block and not committed the change, the local node must construct a CR copy, generating gc cr block busy waits. Concurrent sessions reading the same block then wait on gc buffer busy acquire .

Root‑Cause Investigation

Using DBMS_FGA to audit updates on SD.SYS_PARAMETER revealed that the table is updated every 30 seconds on node 1, but the commit is delayed until the next update. This delayed commit leaves the block uncommitted on the remote node, causing repeated gc cr block busy and gc buffer busy acquire events on node 2.

Test Environment

A reproducible test was built on a similar RAC setup with three sessions:

Session 1 on testnode 1: continuous SELECT on a test table.

Session 2 on testnode 1: identical SELECT started simultaneously.

Session 1 on testnode 2: UPDATE the same table every 30 seconds (first without commit, later with delayed commit).

Session 3 on testnode 1: capture session statistics (v$active_session_history, v$session_wait, etc.).

Key scripts (simplified):

-- select test table
select * from poweruser.t0505_1;

-- update test table (no commit)
update poweruser.t0505_1 set col = col where rownum = 1;
-- later commit after 30 seconds
commit;

-- capture stats
@sess_1st.sql
@sess_2nd.sql

Test Results

Scenario 1 – Remote UPDATE committed promptly

Select latency ~18 seconds, gc cr block busy waits only a few occurrences, CPU impact negligible.

Scenario 2 – Remote UPDATE not committed

Select latency jumps to ~485 seconds, gc cr block busy waits appear on every select, LMS and LGWR on the remote node consume ~2 % of a 32‑core CPU each.

Scenario 3 – Two different tables, same uncommitted updates

Similar latency (~480 seconds) and wait patterns, confirming that the contention is caused by the uncommitted block rather than the specific table.

Performance Impact

Uncommitted updates force the remote node to repeatedly construct CR copies, apply undo records, generate redo, and transfer the block, dramatically increasing CPU and I/O on both LMS and LGWR processes.

Mitigation Measures

Short‑term: commit updates immediately; keep update and read operations on the same node; move frequently updated configuration rows to a separate table.

Long‑term: cache SD.SYS_PARAMETER in the application layer to avoid frequent selects.

Takeaway

Cache‑fusion reduces physical reads but can become a performance liability when uncommitted changes are accessed across nodes. Proper commit discipline and thoughtful data‑access design are essential in RAC environments.