We all know that each instance of a RAC database has it’s own buffer cache in its SGA and all these caches put together forms the cache fusion. The blocks within the RAC instances are transferred using a high speed private interconnect. In order to make sure that no two or more instances are updating the same block at the same time and also to track what each instance is performing with the block, cache coherency and consistency needs to be maintained. Oracle RAC maintains this cache coherency and consistency using two main services GCS (Global Cache Service)and GES (Global Enqueue Service).
GCS with the help of LMS processes, co-ordinates within the RAC instances and records the status of the cached data blocks in the GRD (Global Resource Directory). The GRD is distributed across all the active instances and contains the information of the cached block such as the block number, which instance is owning the current version of the block, mode of the block, role of the block.
GES maintains the coherency of enqueues on the dictionary and library cache. It keeps track of all global enqueues of the resources in the RAC environment.
Based on what a resource holder is requesting to perform, a data block can be of any of the 3 modes.
1. NULL (N): A null mode indicates just a resource holder and has no access rights.
2. Exclusive (X): An exclusive mode signifies exclusive access of the block. This means that the resource holder needs to perform a write operation on the block and no other resource can write over it. However, other resources can perform read operation on the block.
3. Shared (S): A shared mode indicates that the resource holder has a shared lock on the block and is performing a read operation. As the mode name, since the lock is shared, any other resource can also read the block.
In addition to the modes, GCS also has roles for the resources.
1. Local role: When a data block is first read from the disk into the cache, it’s role is said to be LOCAL. This also means that no other modified/dirty copy of the block exists in the cache.
2. Global Role: When a data block is acquired from a remote instance and if this data block is already modified in the remote instance, then the role of the block is said to be GLOBAL. If multiple copies of modified/dirtied data block exists across multiple instances, then the role of this data block is considered to be GLOBAL.
Another important concept that we need to know of is the “Past Image” (PI). As the name says, an image copy of the modified/updated data block is saved by the modifying instance before passing on this data block to the other requesting instance that would like to perform either read or write operation. A PI block is considered to be the current version of the block and in case of a node failure, GCS would start the recovery from this PI block thereby reducing the recovery time. Once the latest version of the data block is written to the disk (checkpoint), GCS informs all the instances having PI to discard those images.
All the above details speak about the data blocks, but when a block is read into the memory, it would be stored in the buffer. The state of a buffer is determined based on the 3 characters.
1. Lock mode – N (NULL), S (Shared), X (Exclusive)
2. Role – L(Local), G(Global)
3. Past Image(PI): Number indicating the number of past images
GV$BH view can be used to check the status of the block against an instance of the database. The values represent:
1. “cr“: This represents a NULL lock mode on the block.
2. “SCUR“: This represents that a SHARED lock is held on the block by that particular instance.
3. “XCUR“: This represents an EXCLUSIVE lock is held on the block by that particular instance.
Lets consider a 3 node RAC as an example to see how the block transfer works with user A connected to instance 1, user B connected to instance 2 and user C connected to instance 3.
Assuming that the the table EMP under schema BTTEST is created freshly and no connections from any of the 3 instances have accessed this table, the following scenarios have been defined. But before moving on to the scenarios, let’s capture the block relevant information for a row of this EMP table. This can be done with the help of dbms_rowid.
1 2 3 4 5 6 7 8 | SQL> select * from bttest.emp;CODE NAME---------- --------------------------------------------------100 JAMES200 SCOTT300 SMITH400 JOHN |
1 2 3 4 5 | SQL>select owner,object_name,data_object_id from dba_objects where object_name='EMP';OWNER OBJECT_NAME DATA_OBJECT_ID--------- ------------ ----------------BTTEST EMP 91791 |
1 2 3 4 5 | SQL>select dbms_rowid.rowid_relative_fno(rowid) FILE#,dbms_rowid.rowid_block_number(rowid) BLOCK# from bttest.emp where code=300;FILE# BLOCK#---------- ----------11 135 |
Considering the block 135 which holds a row of the table EMP whose value for column CODE is 300, we are explaining the following scenarios.
Scenario 1:
User B on instance 2 performs a SELECT operation on this table which access the contents of the block 135.
SQL statement run: select * from bttest.emp where code=300;
1. Since no other connections from other instances have accessed this table previously, the data block 135 needs to be read from the disk and written on to the buffer.
2. User B will now hold a Shared (S) lock on this block as it’s performing only a READ and not WRITE operation. Since the block is being read for the first time from the disk and that there exists no dirty copy of this block in the buffer cache, the role of the block is LOCAL (L). The third consideration is that has been no modification done to this block by USER B and hence there exists no PAST IMAGES (0). With this, the mode of the block on instance 2 would be SL0 and nothing with respect to instance 1 and 3.
Querying the GV$BH view to capture the details of the block, we see that the status of the block for instance 2 is SCUR which indicates that the block is held in SHARED lock by instance 2. There is no information for instance 1 and 3 as they haven’t yet accessed this block.
1 2 3 4 5 | SQL> select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 scur N 91791 2 |
===========================================================================
Scenario 2:
Now user C on instance 3 performs an UPDATE operation on the row in the block 135. The SQL statement run is:
update bttest.emp set name=’UPDATE3′ where code=300;
Since the block is already available in the cache of instance B, there would be no requirement to read the block from the disk and thereby avoiding physical read.
1. Instance 3 would send the request to GCS which knows who currently owns the block (using the information from GRD).
2. GCS forwards the request to instance B who is currently holding the SHARED lock.
3. Instance 2 would downgrade it’s lock on the block from SHARED to NULL and since there is no modification done on this block by instance 2, the role of the block remains LOCAL and Past Image (PI) count is 0. Thereby, the mode of the block on instance 2 would be NL0.
4. Instance 2 would then send the requested block to instance 3 and updates this to the GCS.
5. Instance 3 will now go for an EXCLUSIVE lock (X) on the block and the role of the block still remains LOCAL as the instance previously holding this block (instance 2) hasn’t made any modification to this block and so would the PI count be 0. The mode of the block on instance 3 would be XL0.
6. Instance 1 has not come into picture yet with respect to this block.
1 2 3 4 5 6 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 cr N 91791 211 135 xcur Y 91791 3 |
From the above result, we can see that the buffer status on instance 2 is CR (NULL) and no modifications being done to the block. While on instance 3, the buffer status is XCUR (EXCLUSIVE) and instance 3 has dirtied (modified) (column DIRTY – Value “Y”) the block.
===========================================================================
Scenario 3:
User A on instance 1 runs a “select” statement to access the row in block 135.
1. Instance 1 requests GCS for the data block. GCS knows that instance 3 owns the block and has an EXCLUSIVE lock on it.
2. GCS forwards the request to instance 3.
3. Instance 3 would now bring down it’s lock from Exclusive to Shared on the requested block. The lock would have been lowered to NULL if instance 1 would have requested the block to be modified (WRITE operation), instead, it has requested for a READ operation. Since instance 3 has modified the block in the previous scenario, the role of the block would be GLOBAL and GCS be informed that the requesting instance needs to have this block in the GLBOAL role.
4. Since instance 3 has modified the block in scenario 2, it retains a copy (PI) of the modified block and sends the requested block. Instance 3 will now have SG1 mode on the block.
5. Instance 1 receives the block and will hold a Shared (S) lock with Global Role (as informed previously) with 0 Past Images (there exists no past images for this block on instance 1). So, instance 1 will now have SG0 mode on the block.
6. The mode of the block on instance 2 will be NG0. The role of the block will be changed to GLOBAL as instance 3 has modified the block in the previous scenario.
1 2 3 4 5 6 7 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 cr N 91791 211 135 scur N 91791 111 135 scur Y 91791 3 |
1 2 3 4 5 6 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135 and status <> 'cr';FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 scur N 91791 111 135 scur Y 91791 3 |
When I query the GV$BH view, I could see that both instance 1 and 3 have SHARED lock on the block 135 whereas instance 2 with just CR mode (NULL). Since instance 3 has dirtied this block previously, the value for column DIRTY against instance 3 remains as Y (YES).
===========================================================================
Scenario 4:
Now, I run an “Update” statement on the same block through User B from instance 2.
1. Instance 2 requests GCS for the data block. The block was last accessed by instance 1 and GCS would forward the request to instance 1.
2. Instance 1 would send the block to the requesting instance 2 through GCS. But, before passing on the block, instance 1 would downgrade it’s lock on the block from Shared to NULL with the role of the block being “GLOBAL” and not holding any Past Images. The mode of the block on instance 1 would now be NG0.
3. Since instance 2 has requested for WRITE operation, instance 3 will also downgrade it’s lock on the block from SHARED to NULL. The role of the block remains GLOBAL and since it holds 1 PAST IMAGE (as per Scenario 3), the mode of the block on instance 3 will be NG1.
4. Instance 2 will acquire an EXCLUSIVE (X) lock on the block with the role of the block retained as GLOBAL and the PI count remains 0. The PI count remains 0 for instance 2 is because this instance has never performed any modifications to this block and thereby not retaining any copies of the modifications. The mode of the block on instance 2 would be XG0.
1 2 3 4 5 6 7 8 9 10 11 12 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 xcur Y 91791 211 135 cr N 91791 211 135 cr N 91791 211 135 pi Y 91791 311 135 cr N 91791 3 |
1 2 3 4 5 6 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135 and status <> 'cr';FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 pi Y 91791 311 135 xcur Y 91791 2 |
Querying GV$BH, one can see that the status of the buffer on instance 2 is XCUR with the value for the column DIRTY being “Y”. Also, it shows a clear image of the status of the buffer being shown as “PI” with the value for the column DIRTY being “Y” against instance 3. This is related to the scenario 2 where instance 3 performed an UPDATE operation on the block 135. The values “CR” for the column “STATUS” against instance 2 or instance 3 represent the status of the block in the previous scenarios and not to the current one.
===========================================================================
Scenario 5:
User C on instance 3 runs a “SELECT” statement to access row in block 135.
1. Instance 3 requests GCS for the block 135. GCS then forwards the request to instance 2 which is holding the block with an EXCLUSIVE lock.
2. Instance 2 downgrades its lock from “EXCLUSIVE” to “SHARED”, adds a flag that it modified the block as stated in scenario 4 and thereby declaring that it has 1 PAST IMAGE. However the role of the block still remains GLBOAL and the mode of this block would be SG1 on instance 2.
3. The block is then received by instance 3 and since it requested for a READ operation, it would acquire a SHARED lock on the block. The role of the block would be GLBOAL (as multiple changes have occurred to this block by remoted instances too). The PAST IMAGE count for this bock on instance 3 would be 1 (Reason – there has been only 1 change made to this block by instance 3 (as explained in scenario 2)). Finally, the mode of the block on instance 3 will be SG1.
4. Since instance 1 hasn’t come into picture in this scenario, it would retain it’s mode as it had in the previous scenario i.e, NG0.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 scur Y 91791 211 135 cr N 91791 211 135 cr N 91791 211 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 pi Y 91791 311 135 scur N 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 311 rows selected. |
1 2 3 4 5 6 7 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135 and status <> 'cr';FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 pi Y 91791 311 135 scur N 91791 311 135 scur Y 91791 2 |
From the above result, we can see that the status of the block against instance 2 and instance 3 is SCUR (SHARED lock) and that NULL (cr) for instance 1.
===========================================================================
Scenario 6:
User A on instance 1 performs an UPDATE operation on the same block 135.
SQL statement run: “update bttest.emp set name=’UPDATE2′ where code=300;”
1. Instance 1 requests for the block with GCS. GCS knows that the block was last modified by instance 2 and is the latest one. Hence, it would request instance 2 to transfer the block to instance 1.
2. Since instance 1 wants to perform a WRITE operation on the block, it would acquire an EXCLUSIVE lock. This in turn means that all other instances should downgrade their locks on this block to NULL. The role of the block would be GLBOAL and the PAST IMAGE count of this block on instance 1 would be 0 as this is the first time that this instance has requested for an UPDATE operation on this block. So no PAST IMAGES exist. As a result, the mode of the block on instance 1 would be XG0.
3. As explained in previous step, instance 2 would have NULL lock on the block with GLBOAL role. The PAST IMAGE count would still be 1 because of the update operation it did as per scenario 4. The mode of the block 135 with respect to instance 2 would be NG1.
4. Similarly the mode of the block on instance 3 would be NG1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 pi Y 91791 211 135 cr N 91791 211 135 cr N 91791 211 135 pi Y 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 xcur Y 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 113 rows selected. |
1 2 3 4 5 6 7 | SYS@srprim1>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135 and status <> 'cr';FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 pi Y 91791 211 135 pi Y 91791 311 135 xcur Y 91791 1 |
We now have the status as “PI” which is in dirty state for instance 2 (scenario 4) and instance 3 (scenario 2) each and that for instance 1, the block status is XCUR (EXCLUSIVE).
===========================================================================
Scenario 7:
User A on instance 1 performs another UPDATE operation on the same block.
SQL statement run: “update bttest.emp set name=’UPDATE11′ where code=300;”
1. GCS knows that the last update operation performed on this block was instance 1 itself. Now, again instance 1 has requested for an EXCLUSIVE lock.
2. Mode of the block on instance 1 would be XG1 (The Past image count is 1 for instance 1 as it previously performed as UPDATE operation as per scenario 6).
3. The mode of the block with respect to instance 2 and 3 would remain as in the previous stage (NG1).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 pi Y 91791 211 135 cr N 91791 211 135 cr N 91791 211 135 xcur Y 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 pi Y 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 314 rows selected. |
1 2 3 4 5 6 7 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135 and status <> 'cr';FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 pi Y 91791 211 135 xcur Y 91791 111 135 pi Y 91791 3 |
From the above result, instance 1 holds an XCUR status on the block 135 which indicates an EXCLUSIVE lock is held. While the other 2 instances (2 and 3) hold NULL lock which is why the value is “cr”.
===========================================================================
Scenario 8:
User B on instance 2 performs a CHECKPOINT. (This signifies that the dirty blocks in the buffer needs to be written to the disk.)
1. Instance 2 requests GCS for a checkpoint.
2. GCS forwards the request to instance 1 who held the block in EXCLUSIVE mode in scenario 7, to write the block to disk.
3. Instance 1 does the write operation of the block to disk and informs GCS of the completion of the operation, but would still retain it’s lock as EXCLUSIVE on the block.
4. GCS then informs all the instances holding PIs to discard or flush those PIs and requests the instances to change the role of the block from GLOBAL to LOCAL.
5. Thus, instance 1 will now have the block with mode XL0, instance 2 and instance 3 with NULL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135;FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 cr N 91791 211 135 cr N 91791 211 135 cr N 91791 211 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 cr N 91791 311 135 xcur N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 111 135 cr N 91791 114 rows selected. |
1 2 3 4 5 | SQL>select file#,block#,status,dirty,objd,inst_id from gv$bh where objd=91791 and block#=135 and status <> 'cr';FILE# BLOCK# STATUS D OBJD INST_ID---------- ---------- ---------- - ---------- ----------11 135 xcur N 91791 1 |
Here, one can see that the block is currently held by instance 1 in EXCLUSIVE mode while all other instances have NULL lock on this block. All the past images that instance 2 and 3 had have been flushed.
No comments:
Post a Comment