Introduction
Oracle RAC is designed to provide high availability and scalability, but it also introduces Cache Fusion, where database blocks are shared across multiple RAC instances. While this architecture enables seamless access to shared data, it can also lead to cluster contention when multiple instances compete for the same data block.
One of the most common RAC wait events encountered by Oracle DBAs is:
gc buffer busy acquire
This wait event is often misunderstood. It is not the root cause—it is a symptom of underlying block contention.
In this article, we’ll walk through a real-world troubleshooting approach to identify the root cause, analyze the affected SQL and objects, and implement effective solutions.
What is gc buffer busy acquire
?
gc buffer busy acquire occurs when an Oracle RAC instance requests a data block from another instance, but the block is temporarily unavailable because it is currently busy.
Instead of immediately receiving the block through Cache Fusion, the requesting session waits until the block becomes available.
This usually indicates:
- High block contention
- Hot blocks
- Excessive concurrent DML
- Frequent block transfers between RAC nodes
Example Environment
|
Component |
Value |
|
Oracle Version |
Oracle Database 19c |
|
Cluster |
2-Node Oracle RAC |
|
Application |
Retail Order Processing |
|
Database |
ORDERDB |
|
Concurrent Users |
12,000+ |
|
Workload |
Heavy OLTP |
Typical Symptoms
Users may report:
- Slow application response
- High transaction latency
- Sessions waiting in Oracle RAC
- Increased Cluster Wait Time
- CPU utilization appears normal
- Storage and Network are healthy
AWR Reports often show:
Top Timed Events
gc buffer busy acquire
Step 1 – Verify OS Resources
Before investigating Oracle, confirm that the operating system is healthy.
Check:
top
vmstat
iostat -x
sar
netstat
Look for:
- CPU bottlenecks
- Memory pressure
- Disk latency
- Network congestion
If the OS is healthy, continue with Oracle diagnostics.
Step 2 – Identify Waiting Sessions
Determine which wait events dominate the workload.
SELECT event,
COUNT(*)
FROM gv$session
WHERE state='WAITING'
GROUP BY event
ORDER BY COUNT(*) DESC;
Example:
|
Event |
Sessions |
|
gc buffer busy acquire |
286 |
|
db file sequential read |
54 |
|
SQL*Net message from client |
31 |
This confirms RAC block contention.
Step 3 – Find the SQL Causing Contention
Identify the SQL executed by waiting sessions.
SELECT sql_id,
COUNT(*)
FROM gv$session
WHERE event='gc buffer busy acquire'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
Then retrieve the SQL text.
SELECT sql_text
FROM gv$sql
WHERE sql_id='&SQL_ID';
Example:
INSERT INTO order_txn
(order_id,
customer_id,
amount,
created_date)
VALUES
(order_seq.NEXTVAL,
:1,
:2,
SYSDATE);
Step 4 – Identify the Hot Block
Determine which block all sessions are waiting for.
SELECT inst_id,
sid,
sql_id,
p1 file#,
p2 block#
FROM gv$session
WHERE event='gc buffer busy acquire';
If many sessions reference the same FILE# and BLOCK#, you have identified a hot block.
Step 5 – Identify the Hot Object
Map the block to its database object.
SELECT owner,
segment_name,
segment_type
FROM dba_extents
WHERE file_id=&FILE#
AND &BLOCK#
BETWEEN block_id
AND block_id+blocks-1;
Example:
|
Owner |
Segment |
|
RETAIL_APP |
ORDER_TXN |
Step 6 – Analyze AWR and ASH
Review:
- Top Wait Events
- Top SQL
- Top Segments
- Cluster Waits
- Hot Blocks
Useful ASH Query
SELECT current_file#,
current_block#,
COUNT(*)
FROM gv$active_session_history
GROUP BY current_file#,
current_block#
ORDER BY COUNT(*) DESC;
Common Root Causes
1. Hot Blocks
Thousands of concurrent INSERT operations target the same block.
Solution
- Reverse Key Index
- Partition the table
- Increase INITRANS
2. Sequence-Based Index Contention
Sequential primary keys create right-hand index hotspots.
Solution
CREATE INDEX idx_order
ON order_txn(order_id)
REVERSE;
3. Low INITRANS
Too few ITL slots force sessions to wait.
Increase INITRANS.
ALTER TABLE order_txn
INITRANS 8;
ALTER INDEX idx_order
INITRANS 8;
4. Sequence Configuration
Avoid ORDER sequences in RAC.
Recommended:
CREATE SEQUENCE order_seq
CACHE 1000
NOORDER;
5. Poor Partitioning
All inserts target a single partition.
Consider:
- Range Partitioning
- Hash Partitioning
- Composite Partitioning
6. Heavy Concurrent DML
Thousands of concurrent INSERT or UPDATE operations against the same object increase contention.
Possible solutions:
- Batch processing
- Queue-based architecture
- Reduce unnecessary updates
Best Practices
✔ Monitor AWR regularly.
✔ Use ASH to identify hot blocks.
✔ Create Reverse Key Indexes where appropriate.
✔ Increase INITRANS on high-concurrency objects.
✔ Configure NOORDER CACHE sequences.
✔ Partition large OLTP tables.
✔ Route write-intensive services to a preferred RAC instance.
✔ Monitor Cluster Wait Events proactively.
Useful Diagnostic Queries
Current Waiting Sessions
SELECT inst_id,
sid,
event,
sql_id
FROM gv$session
WHERE event='gc buffer busy acquire';
Top Cluster Waits
SELECT event,
COUNT(*)
FROM gv$session
WHERE state='WAITING'
GROUP BY event;
Hot Blocks
SELECT current_file#,
current_block#,
COUNT(*)
FROM gv$active_session_history
GROUP BY current_file#,
current_block#
ORDER BY COUNT(*) DESC;
Real-World Resolution
In a production Oracle RAC environment, the issue was resolved by:
- Creating a Reverse Key Index on the primary key.
- Increasing INITRANS for the table and index.
- Configuring the sequence with CACHE 1000 NOORDER.
- Routing write-heavy services to the preferred RAC instance.
- Monitoring post-change performance using AWR and ASH.
After implementing these changes:
- gc buffer busy acquire waits dropped significantly.
- Cluster wait time decreased.
- Transaction throughput improved.
- Overall application response time became stable.
Conclusion
The gc buffer busy acquire wait event should never be treated as the problem itself. It is an indicator that multiple RAC instances are competing for the same data blocks.
Effective troubleshooting requires a structured approach:
- Verify OS health.
- Identify waiting sessions.
- Find the SQL causing contention.
- Locate the hot object and hot block.
- Analyze AWR and ASH.
- Eliminate the underlying contention through indexing, partitioning, sequence tuning, or workload redesign.
Understanding why the contention occurs—and addressing that root cause—is what separates reactive troubleshooting from effective Oracle RAC performance tuning.
Coming Next—>
- Oracle RAC Cache Fusion Internals
- gc cr request vs gc current request
- Oracle RAC Performance Tuning Best Practices
- Oracle AWR & ASH Deep Dive
- Oracle Wait Events Explained for DBA
Comments
Post a Comment