gc current block 3-way wait event in RAC Oracle Performance Tuning

What is gc current block 3-way wait & gc current block 2-way wait?

So many times oracle rac database wait event gc current block 3-way wait logs in statspack reports and awr reports. What is the gc current block 3-way wait? This is the main query in every oracle rac dba expert's mind. We found this wait event so many times in our remote database services. Here you can find out description of gc current block 3-way wait event analysis.

When gc current block 3-way wait event occurred:

If node-1 has request for oracle block which has not right to access. Node-2 is master or actual directory holder of node-1 requested block. Due to this reason node-1 send request to get privilege of requested block to node-2. Now node-2 is master and directory holder, due to this reason it has details that node-3 is having right of said oracle block and has also real ownership of block. Then node-2 informs to node-3 for changing permission or right of oracle block and sends to node-1. After getting request from node-2, the node-3 is changing privilege of block and sends to node-1 and reporting to node-2 that job done. Finally node-1 gets require oracle block. We can say that if 3 nodes available in oracle RAC then gc current 3-way wait can occur.

General misinterpretation of gc current block 3-way wait in RAC Oracle Database:

Non experienced oracle rac dba doesn't know that gc current block 3-way wait only logs when you have 3 nodes Oracle cluster RAC database. This gc current block 3-way wait never occurs in 2 nodes oracle cluster RAC database. This gc current block n-way wait depends on n- number of nodes. Means if you have 4 nodes oracle cluster then you will get gc current block 4-way wait.

How to trace gc current block wait event in RAC Oracle Database:

We can get this wait event from statspack report of Oracle AWR reports. Affected segment list can be see from Segments By Current Blocks Received part of AWR reports. We can collect information from v$segment_statistics for gc current block wait event.

Tuning of gc current block 3-way wait event of Oracle RAC Database:

gc current block wait event occurs if all nodes of cluster database are fully loaded and huge traffic is generating on all nodes. Transferring of blocks from one node to another node is depending on cluster Interconnect. You should need to check bandwidth of cluster interconnect and switch configuration. If lot of reference key contains tables are involving in transactions then also gc current block wait occurs. Because if heavy DML are being occurred on foreign key contain tables from all connected nodes then shared lock on primary key contain tables causing problem. This situation is also affecting gc current block 3-way wait event. Sometime gc current block 3-way wait event is appearing in TOP-5 wait event analysis in AWR reports because of missing of index on foreign key columns.

Dbametrix is world wide leader in remote dba support. Expert remote DBA team of Dbametrix is offering high quality professional Oracle DBA support with strong response time to fulfill your SLA. Contact our sales department for more information with your requirements for remote dba services.