I already mentioned about this event in the same blog few months back. But this example is more lively in terms of finding the root cause with the help of AWR.
The gc current block busy and gc cr block busy wait events indicate that the local instance that is making the request did not immediately receive a current or consistent read block. The term "busy" in these events' names indicates that the sending of the block was delayed on a remote instance. For example, a block cannot be shipped immediately if Oracle Database has not yet written the redo for the block's changes to a log file.
In comparison to "block busy" wait events, a gc buffer busy event indicates that Oracle Database cannot immediately grant access to data that is stored in the local buffer cache. This is because a global operation on the buffer is pending and the operation has not yet completed. In other words, the buffer is busy and all other processes that are attempting to access the local buffer must wait to complete.
This AWR belongs to 3 Node RAC Architecture, we see high gc buffer busy in one of the busy nodes. You need to drill down further down in the AWR to the SQL Ordered by Cluster Wait Time section ( Fig 2)and see what query is on the top waiting for the resources. One you pick the query look for the object which is being referred in the query.
In this case we have seen a table (referred in the top sql of Cluster Wait) which is being frequently accessed by all the 3 nodes is constantly being updated by another process.
Further drill down in the AWR to 'Segments by Global Cache Buffer Busy ' to find out the object on which most of the buffer waits are being found. If the table identified in the query matches with that of the Global Cache Buffer Busy section then identify all the DML operations on that table to figure out the solution.
In our case the application is using the same table for updates and selects which is causing the issue. The other quick fix is to see that update application and select application are being routed to the same node to avoid the gc wait event. But it is always better to identify the hot block and try to fix it from the design level for the optimal solution.
The gc current block busy and gc cr block busy wait events indicate that the local instance that is making the request did not immediately receive a current or consistent read block. The term "busy" in these events' names indicates that the sending of the block was delayed on a remote instance. For example, a block cannot be shipped immediately if Oracle Database has not yet written the redo for the block's changes to a log file.
In comparison to "block busy" wait events, a gc buffer busy event indicates that Oracle Database cannot immediately grant access to data that is stored in the local buffer cache. This is because a global operation on the buffer is pending and the operation has not yet completed. In other words, the buffer is busy and all other processes that are attempting to access the local buffer must wait to complete.
This AWR belongs to 3 Node RAC Architecture, we see high gc buffer busy in one of the busy nodes. You need to drill down further down in the AWR to the SQL Ordered by Cluster Wait Time section ( Fig 2)and see what query is on the top waiting for the resources. One you pick the query look for the object which is being referred in the query.
In this case we have seen a table (referred in the top sql of Cluster Wait) which is being frequently accessed by all the 3 nodes is constantly being updated by another process.
Further drill down in the AWR to 'Segments by Global Cache Buffer Busy ' to find out the object on which most of the buffer waits are being found. If the table identified in the query matches with that of the Global Cache Buffer Busy section then identify all the DML operations on that table to figure out the solution.
In our case the application is using the same table for updates and selects which is causing the issue. The other quick fix is to see that update application and select application are being routed to the same node to avoid the gc wait event. But it is always better to identify the hot block and try to fix it from the design level for the optimal solution.
No comments:
Post a Comment