RAC Specific Wait Event
Difference between CR and current blocks ( explained in Oracle documentation 12.1 )
The Global Cache Block Access Latency chart shows data for two different types of data block requests: Current and Consistent-Read (CR) blocks. When you update data in the database, Oracle Database must locate the most recent version of the data block that contains the data, which is called the current block. If you perform a query, then only data committed before the query began is visible to the query. Data blocks that were changed after the start of the query are reconstructed from data in the undo segments, and the reconstructed data is made available to the query in the form of a consistent-read block. In short - DMLs request the Current Block - SELCET Queries often request a CR block
1) Current - DML
2) CR - SELECT
A) Block-Oriented Waits
-------------------------------
get the block from a different instance
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
---------------------------------
No block was received because it was not cached in any instance.
This events triggers a disk read event like: db file sequential read.
This event is a good indication for your round trip time as no processing occurs at remote side.
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
--------------------------------------
Block is pinned by a session on a different node and could not be shipped immediate
( concurrent DML on index blocks or index splits, ongoing Cache Fusion operation )
2. gc cr block busy
3. gc current buffer busy (local cache contention)
D) Load-Oriented Waits
------------------------------
------------------------------
Block request transfer waits > 1ms for processing due to CPU saturation, LMS process is not able to catch with number of requests
Could be fixed by adding CPU / Load balancing / offloading processing to new cluster notes
2. gc cr block congested
3. gc current grant congested
4. gc cr grant congested
The most common wait events related to the global buffer cache are:
– gc cr request
– gc buffer busy
Block types tracked by STATE column of table X$BH
State Mode Explanation 0 Buffer is free, unused 1 XCUR Buffer current, locked in X mode 2 SCUR Buffer current, locked in S mode 3 CR Consistent read buffer 4 Buffer being read 5 Buffer under media recovery 6 Buffer under instance recovery 7 Write clone buffer 8 PI Past Image buffer
Now let's come to the description 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 Received" 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 that's 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 Received" 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 that's 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 instance 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 immediately.
- Meanwhile requesting instance 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 similar 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.
--------------------------------------------------------------------
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 Received" 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 that's 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 Received" 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 that's 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 instance 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 immediately.
- Meanwhile requesting instance 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 similar 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 instance 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 amount 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 instance 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 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 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 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 instance 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 amount 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 instance 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 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 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 requesting 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"
-----------
- 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 requesting 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"
No comments:
Post a Comment