This was first published on https://blog.dbi-services.com/rac-buffer-states-xcur-scur-pi-ci (2015-06-25)
Republishing here for new followers. The content is related to the the versions available at the publication date
In RAC, blocks are copied across instances by the Global Cache Service. In single instance, we have only two status: CR for consistent read clones where undo is applied, and CUR for the current version that can be modified (then being a dirty block). I’ts a bit more complex in RAC. Here is a brief example to show the buffer status in Global Cache.
I connect to one instance (I have a few singleton services. service ONE is on instance 3 and service TWO is on instance 1)
SQL> connect demo/demo@//192.168.78.252/ONE.racattack Connected.
and I query a row by ROWID in order to read only one block
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1'; old 1: select rowid,DEMO.* from DEMO where rowid='&rowid1' new 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' ROWID ID N ------------------ ---------- ---------- AAAXqxAALAAACUkAAD 10 10
Here is the status of the buffer in the buffer cache:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id; INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N ---------- ---------- ---------- ---------------- - - - - - - 3 1 scur 00000000B9FEA060 N N N N N N
The block has been read from disk by my instance. Without modification it is in SCUR status: it’s the current version of the block and can be shared.
Now connecting to another instance
SQL> connect demo/demo@//192.168.78.252/TWO.racattack Connected.
and reading the same block
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1'; old 1: select rowid,DEMO.* from DEMO where rowid='&rowid1' new 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' ROWID ID N ------------------ ---------- ---------- AAAXqxAALAAACUkAAD 10 10
let’s see what I have in my Global Cache:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr; INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N ---------- ---------- ---------- ---------------- - - - - - - 1 1 scur 00000000B0FAADC0 N N N N N N 3 1 scur 00000000B9FEA060 N N N N N N
non modified blocks can be shared: I have a copy on each instance.
I’ll start a new case, I flush the buffer cache
connecting to the first instance
SQL> connect demo/demo@//192.168.78.252/ONE.racattack Connected.
I’m now doing a modification with a select for update (which writes the lock in the block, so it’s a modification)
SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1' for update; old 1: select rowid,DEMO.* from DEMO where rowid='&rowid1' for update new 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' for update ROWID ID N ------------------ ---------- ---------- AAAXqxAALAAACUkAAD 10 10 now the status in buffer cache is different: SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr; INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N ---------- ---------- ---------- ---------------- - - - - - - 3 1 cr 00 N N N N N N 3 1 xcur 00000000B9FEA060 Y N N N N N
So I have two buffers for the same block. The buffer that has been read and will not be current anymore because it has the rows before the modifications. It stays in consistent read (CR) status. The modified one is then the current one but cannot be shared: its the XCUR buffer where modifications will be done.
Now I’ll read it from the second instance
SQL> connect demo/demo@//192.168.78.252/TWO.racattack Connected. SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1'; old 1: select rowid,DEMO.* from DEMO where rowid='&rowid1' new 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' ROWID ID N ------------------ ---------- ---------- AAAXqxAALAAACUkAAD 10 10
the block is read and I’ve another CR buffer:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr; INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N ---------- ---------- ---------- ---------------- - - - - - - 1 1 cr 00 N N N N N N 3 1 cr 00 N N N N N N 3 1 xcur 00000000B9FEA060 Y N N N N N
the CR buffer is at another SCN. A block can have several CR blocks (by default up to 6 per instance)
Let’s do a modification from the other instance
SQL> connect demo/demo@//192.168.78.252/TWO.racattack Connected. SQL> select rowid,DEMO.* from DEMO where rowid='&rowid1' for update; old 1: select rowid,DEMO.* from DEMO where rowid='&rowid1' for update new 1: select rowid,DEMO.* from DEMO where rowid='AAAXqxAALAAACUkAAD' for update ROWID ID N ------------------ ---------- ---------- AAAXqxAALAAACUkAAD 10 10
My modification must be done on the current version, which must be shipped to my instance
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr; INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N ---------- ---------- ---------- ---------------- - - - - - - 1 1 cr 00 N N N N N N 1 1 cr 00 N N N N N N 1 1 xcur 00000000B0FAADC0 Y N N N N N 3 1 cr 00 N N N N N N 3 1 pi 00000000B9FEA060 Y N N N N N
and the previous current version remains as a PI – past image. It cannot be used for consistent reads but it is kept for recovery: if current block is lost, redo can be applied to the past image to recover it. See Jonathan Lewis explanation.
As the past images are there in case of recovery, they are not needed once an instance has checkpointed the current block.
SQL> connect sys/oracle@//192.168.78.252/ONE.racattack as sysdba Connected. SQL> alter system checkpoint; System altered.
afer the checkpoint on the instance that has the XCUR, there is no dirty buffer in any instance:
SQL> select inst_id,class#,status,lock_element_addr,dirty,temp,ping,stale,direct,new from gv$bh where objd=(select data_object_id from dba_objects where owner='DEMO' and object_name='DEMO') and status!='free' order by inst_id,lock_element_addr; INST_ID CLASS# STATUS LOCK_ELEMENT_ADD D T P S D N ---------- ---------- ---------- ---------------- - - - - - - 1 1 cr 00 N N N N N N 1 1 cr 00 N N N N N N 1 1 xcur 00000000B0FAADC0 N N N N N N 3 1 cr 00 N N N N N N 3 1 cr 00 N N N N N N
the PI became a consistent read.
Here are the states we have seen here:
XCUR: current version of the block – holding an exclusive lock for it
SCUR: current version of the block that can be share because no modification were done
CR: only valid for consistent read, after applying the necessary undo to get it back to requried SCN
PI: past image of a modified current block, kept until the latest version is checkpointed
and the other possible states:
FREE: The buffer is not currently in use.
READ: when the block is being read from disk
MREC: when the block is being recovered for media recovery
IREC: when the block is being recovered for crash recovery
This is a great explanation of the status values. Better than the docs (http://docs.oracle.com/database/121/REFRN/GUID-A8230335-47C4-4707-A866-678DD8D322A8.htm#REFRN30029).
It might be beneficial to others to take note of the Consistent Read section. Note that the Buffer Cache has two ‘cr’ blocks, one in inst 1 and the other in inst 3. This is because the block was modified on inst 3. So a cr image of the block is reconstructed in inst 3. The block is the sent, via a ‘gc cr block %’ transfer to inst 1. Hence the two CR copies of that same block in GV$BH.
Good stuff!
Thanks, Brian
Hi Brian, Thanks for your feedback and remark. Regardsm Franck.