RAC Specific Wait Event

A) Block-Oriented Waits
-----------------------
1. gc current block 2-way
2. gc current block 3-way
3. gc cr block 2-way
4. gc cr block 3-way

B) Message-Oriented Wais
-------------------------
1. gc current grant 2-way
2. gc current grant 3-way
3. gc cr grant 2-way
4. gc cr grant 3-way (Normally this wait event is not possible, but "_cr_grant_local_role" --> turn 3-way CR grants off, make it automatic, or turn it on)

C) Contention-Oriented Waits
----------------------------
1. gc current block busy (cluster cache contention)
2. gc cr block busy
3. gc current buffer busy (local cache contention)

D) Load-Oriented Waits
----------------------
1. gc current block congested
2. gc cr block congested
3. gc current grant congested
4. gc cr grant congested

Now let's come to the discription part
---------------------------------------
A) Block-Oriented Waits
========================
1. gc current block 2-way (write/write with 2 nodes)
-----------------------------------------------------
Definition
-----------

- requesting instance request any data block for dml(current) from Master.
- If master is the holder of that data block and also has already modified that block.
- Then master will retain PI block for itself.
- Master will also flush respective redo log to log file before sending CURRENT block to requesting instance.
- Meanwhile requesting instance will wait in "GC CURRENT BLOCK 2-WAY"

Reason
---------
As you know before sending the current data block to the requesting instance master instance first flush respective redo log of the log file then it will prepare PI block and then  send CURRENT block to the requesting instance over the interconnect.

- This wait event appears in "TOP-5" timed events section of AWR Report.
- Analyze the contention using AWR REPORT.
- In AWR REPORT, analyze "Current Block Recieved" and identify the top contentious objects.
- You are getting contention at segment level because of bad database design, DB object layout and Space Management.
- LGWR is not efficient to write in redo log file and thats why requesting instance is waiting in "gc current block 2-way".
- Interconnect is having N/W latency.
- Application Segregation is also a reason.

Troubleshooting
----------------
 - Best Solution is to apply application Segregation means try to locate all select query on one node and all DML on another node.
- Tune LGWR
- Tune Interconnect

2. gc current block 3-way (write/write with 3 nodes)
----------------------------------------------------

Definition
----------

- Requesting instance request any data block in CURRENT MODE for dml(current) from Master.

- If master is not holder of that data block and that data block is globally available on another instance.

_ Master will send a message to the current holding instance to relinquish ownership (Downgrade lock).

- The holding instance retain the PI of that data block and then serve to the requesting instance.

- Holding instance will also flush respective redo log to log file before sending CURRENT block to requesting instance.

- Meanwhile requesting instance will wait in "GC CURRENT BLOCK 3-WAY"


Reason
---------

As you know before sending the current data block to the requesting instance master instance first flush respective redo log of the log file then it will prepare PI block and then CR block to send to the requesting instance over the interconnect.

- This wait event appears in "TOP-5" timed events section of AWR Report.

- Analyze the contention using AWR REPORT.

- In AWR REPORT, analyze "Current Block Recieved" and identify the top contentious objects.

- You are getting contention at segment level because of bad database design, DB object layout and Space Management.

- LGWR is not efficient to write in redo log file and thats why requesting instance is waiting in "gc current block 3-way".

- Interconnect is having N/W latency.

- Application Segregation is also a reason.


Troubleshooting
----------------

- Best Solution is to apply application Segregation means try to locate all select query on one node and all DML on another node.

- Tune LGWR

- Tune Interconnect

3. gc cr block 2-way (read/read or write/read with 2 nodes)
-----------------------------------------------------------
Definition
-----------
case-1: WRITE/READ
-------------------

- Requesting instance request any CR data block for select from Master.

- If master is the holder of that data block and also has already modified that block.

- Then master will prepare CR copy of that data block (using undo).

- Finally Master instance serve CR block to the requesting instance.

- Meanwhile requesting instace will wait in "gc cr block 2-way"

Case-2: READ/READ
------------------
- Requesting instance request any CR data block for select from Master.

- If master is the holder of that data block and has not already modified that block.

- Master instance serve CR block to the requesting instance immediatly.

- Meanwhile requesting instace will wait in "gc cr block 2-way"

Reason
---------
- In both the cases you will encounter this wait event in "TOP-5" section of AWR Report.

- Plan of action would be similer like "gc current block 2-way"

4. gc cr block 3-way (read/read or write/read with 3 nodes)
-----------------------------------------------------------
This wait event is exactly same as "gc cr block 2-way", only the difference is that here 3 or more than 3 instances are involved.

#################################################################################

B) Message-Oriented Waits
==========================
1. gc current grant 2-way
--------------------------
Definition
----------
- An instance request any data block in CURRENT MODE for dml(current) from Master.

- If the data block is not cached on any instance even on master too, then master instance will send a message to the requesting instance granting the EXCLUSIVE lock.

- Meanwhile requesting instance will wait in "GC CURRENT BLOCK 2-WAY"

- Requesting instace will read data block from disk and do the physical I/O.

- This wait event does not indicate any contention.

Reason
---------
- This wait event appears in "TOP-5" timed events section of AWR Report.

- This wait event represent that requesting instance is spending a significant amount of time in obtaining the locks.

- Interconnect is having N/W latency (rare).

Troubleshooting
----------------
 - Best Solution is to tune your SQL Application so that it request less amout of data blocks.

- Because if requesting instance ask for more data blocks then master has to locking and holding instance related information in GRD before granting EXCLUSIVE LOCK, which will be high.

- Tune Interconnect (very rare).

2. gc cr grant 2-way
---------------------

Definition
----------
- An instance request any data block in CR MODE for select from Master.

- If the data block is not cached on any instance even on master too, then master instance will send a message to the requesting instance granting the SHARED lock.

- Meanwhile requesting instance will wait in "GC CR BLOCK 2-WAY"

- Requesting instace will read data block from disk and do the physical I/O.

- This wait event does not indicate any contention.


3. gc current grant 3-way
--------------------------
Definition
-----------
- An instacne request for any data block in current mode.

- Mater is not the holder of that data block, then master forward a message to provide the data block to the requesting instance.

- Current holding instance don't have the block because of aging out mechanism.

- Current holding instance grant exclusive lock to the requesting instance for physical IO.

- Meanwhile requesting instance will wait in "gc current grant 3-way".

- there is a hidden parameter to control this "_cr_grant_local_role"

C) Contention-Oriented Waits
=============================
1. gc current block busy
-------------------------
Definition
-----------
- An instance request for any data block in current mode, it send a request to the master.

- If master is the holder of that data block and also has already modified that block.

- Then master will retain PI block for itself.

- Master will also flush respective redo log to log file before sending CURRENT block to requesting instance.

- Now the block transfer delayed on requsting instance.

- Meanwhile requesting instance will wait in "gc current block busy"

Reason
-------

- The block was being used by a session on another instance.

- The block transfer was delayed because the holding instance could not write the corresponding redo record to the online redo log file.

Troubleshooting
----------------
 - Tune LGWR
- Appropriate Application Partitioning
- Tune N/w

2. gc current buffer busy
--------------------------
This wait event appears mostly in SINGLE INSTANCE when more than one sessions are trying to access same data block.
Troubleshooting
---------------
 - Application tuning


3. gc cr block busy
--------------------

- This event is the same as a "gc current block busy"


################################################################################################


D) Load-Oriented Waits (LMS)
=============================

1. gc current block congested
------------------------------

Definition
-----------

- Whenever any instance (HOST-03) request for any data block in any mode, this request will be served by MASTER NODE (HOST-01) of that data block.

- LMS process running on master node will provide data block to the requesting instance LMS process.

- Now LMS process running on both the nodes are highly loaded so there would be wait event ""gc current block congested


Reason
-------

- Highly loaded LMS process or CPU.



Troubleshooting
----------------

- Increase numner of LMS Processes by setting "GCS_SERVER_PROCESS" in 9i RAC onward.

- Optionally you can also set "_LM_LSM" till OPS.

- Tune OS

- Add CPUs



2. gc cr block congested
------------------------------

- This event is the same as a "gc current block congested".



3. gc current grant congested
------------------------------

Definition
-----------

- Whenever any instance (HOST-03) request for any data block in any mode, this request will be served by MASTER NODE of that data block.

- Meanwhile requesting instance (HOST-03) is waiting for approval from master instance to perform physical IO to read data block from DISK.

- This wait event is "gc current grant congested"

-