Message-Oriented Oracle RAC wait event
The message-oriented wait event statistics indicate that no block was received because it was not cached in any instance. Instead, a global grant was given, enabling the requesting instance to read the block from disk or modify it.
If the local instance is the resource master, the grant happens immediately.If not, the grant is always 2-way, regardless of the number of instances in the cluster.
In the first article on wait events, we discussed on Basic on Oracle RAC wait events.:- Click here to read.
1. gc current grant 2-way
If the time consumed by gc current grant 2-way is high that indicates that workload inserts a lot of data and needs to find and format new block frequently.
Example :
- Instance A request a new data block in CURRENT mode from resource master of that block.
- If the data block is not cached on any instance even on master too, then the master instance will send a message to Instance A [requesting instance] granting exclusive lock.
- Requesting Instance will read data block from disk and do the physical I/O
2. gc current grant 3-way
- An instance 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 doesn’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“
Basic Solution to resolve above two wait events
- Best Solution is to tune your SQL Application so that it requests less amount of data blocks.
- Because if requesting instance ask for more data blocks then master has to record locking and holding instance related information in GRD before granting EXCLUSIVE LOCK, which will be high.
- Tune Interconnect.
3. gc cr grant 2-way
If the time consumed by gc cr grant 2-way event is , then it may be assumed that the frequently used SQL causes a lot of disk I/O
- Instance A is requesting 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 message to Instance A [requesting instance] granting SHARED lock.
- Requesting instance will read data block from disk and do the physical I/O.
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.
In last article, we discussed on Block-Related Oracle Rac Wait:- Click here to read.
Stay Tuned for more detail on Oracle RAC wait events.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp