Block-Related Oracle Rac Wait Events – Part 2

An event can be anything that Oracle has to perform on behalf of a set of instructions sent by the user interface. The term “wait” is used because every time a user connects to your application, a resource is allocated to perform tasks on its behalf. The waiting comes when a session is waiting for an action, sometimes from a user and at other times from the database

3. gc cr block 2-way :

CR block: A consistent read (CR) block represents a consistent snapshot of the data from a previous point in time. Applying undo/rollback segment information produces consistent read versions.

For example :

We have Instance A and Instance B.
Some foreground process running in Instance A,is trying to access a block using a SELECT statement and that block is not in the local cache of Instance A.To maintain read consistency , foreground process will require the block consistent with the query SCN.
In this case ,operation will performed in following sequence :
1. Foreground session will determine master node of the block,in our case Instance B;and requests a LMS process running in the master node to access the block.
2.And if the master node is the same node having consistent state of block[Means block version is lower or equal to query SCN, then LMS process of Instance B can send the block to the foreground process immediately.

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”

4. gc cr block 3-way :

For example :

We have Instance A,Instance B and Instance C:
1.One foreground process running in Instance A,is trying to access a block using SELECT statement and that block is not in the local cache of Instance A.
2.Foreground session will determine master node of the block , Instance B. when Instance B is resource master for that block  is not having consistent image of the block compared to query SCN.
3.Then master Instance[Instance B], forwards the request to holding Instance – Instance C , making it more understandable , lets say requested block has an uncommitted changes.
4.Since the block has uncommitted changes,LMS process can not send block immediately.LMS process creates a CR version of the block by cloning the buffer and applying undo records to the buffer rolling back the block to the SCN consistent with the requested query SCN.
5.Then CR [consistent read] block is sent to the foreground process.

Basic Solution to resolve above two wait events

• 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

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