RAC :
Wait Events
current read block - locally instance read block
cr block - consistent read block
current read block - locally instance read block
cr block - consistent read block
Block Oriented Wait
gc current block 2-way
gc current block 3-way
gc cr block 2-way
gc cr block 3-way
Message Oriented Wait
gc current grant 2-way
gc current grant 3-way
gc cr grant 2-way
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)
Contention Oriented Wait
gc current block busy (cluster cache
contention)
gc cr block busy
gc current buffer busy (local cache
contention)
gc cr buffer busy (Remote cache
contention)
Load Oriented Wait
gc current block congested
gc cr block congested
gc current grant congested
gc cr grant congested
OTHERS:
gc
current block lost
: Lost blocks due to Interconnect or CPU. Indicates interconnect issues and
contention.
gc cr
block lost
: Lost blocks due to Interconnect or CPU. Indicates interconnect issues and
contention.
gc
current multi block request : Full table or index scans.
gc cr
multi block request
: Full table or index scans.
gc cr
request
: The time it takes to retrieve the data from the remote cache. Oracle may not
pick
private interconnect
and instead route traffic over slower public network.
RAC event similar to
buffer busy waits, tune SQL to request less data, tune network
latency between RAC
nodes, localize data access.
gc
current/cr failure/retry : A block is requested and a failure status received or
some other
exceptional event has occured.
Block Oriented
Wait : gc current block 2-way
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
- Make Sure the system has enough CPU power.
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
- Make Sure the system has enough CPU power.
Block Oriented Wait : gc current block 3-way
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
Block Oriented Wait : gc cr block 2-way
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"
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"
Block Oriented Wait : gc cr block 3-way
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.
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.
Message Oriented Wait : gc current grant 2-way
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).
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).
Message Oriented Wait : gc current grant 3-way
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"
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"
Message Oriented Wait : gc cr grant 2-way
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.
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.
Message Oriented Wait : gc cr grant 2-way
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.
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.
Contention Oriented Wait : gc current block busy
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.
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.
Contention Oriented Wait : gc current buffer busy
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
This wait event appears mostly in SINGLE INSTANCE when more than one sessions are trying to access same data block.
Troubleshooting
- Application tuning
Contention Oriented Wait : gc cr block busy
Contention Oriented Wait : gc cr buffer busy (Remote cache contention)
Load Oriented Wait : gc current block congested
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 number 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
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 number 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
Load Oriented Wait : gc cr block congested
gc cr block congested
- This event is the same as a "gc current block congested".
- This event is the same as a "gc current block congested".
Load Oriented Wait : gc current grant congested
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"
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"
Load Oriented Wait : gc current grant congested
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"
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"
