A closer look on Cache Fusion II

This article  follows closely chapter 12 of following book:

 Oracle Database 11g Oracle Real Application Cluster Handabook Second Edition written by K. Gopalakrishan

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
- Queries often request a CR block

RAC/Global Cache releated waits

Block-Oriented Waits: 
    get the block from a different instance
    Wait events:
    gc current block 2-way
    gc current block 3-way
    gc cr block 2-way
    gc cr block 3-way

Message-Oriented Waits: 
    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
    Wait events:
    gc current grant 2-way
    gc current grant 3-way
    gc cr grant 2-way
    gc cr grant 3-way

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 )
    Wait events
    gc current block busy
    gc cr block busy
    gc current buffer busy

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 
    Wait events
    gc current block congested
    gc cr block congested
    gc current grant congested
    gc cr  grant congested

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

Example 1: Read the block from disk

Example 1: Read the block from disk
Operation:   Instance 1: Master  Instance 2: -  Instance 3: read block from disk

grac43: $ sql3 @get_emp
OWNER                   DATA_OBJECT_ID OBJECT_NAM
------------------------------ -------------- ----------
SCOTT                        87452 EMP
grac41 - Action : Force grac41 to become master of EMP object
SQL> oradebug setmypid
SQL> oradebug lkdebug -m pkey 87452

Status grac41: ( Resource Master )
@get_res_stat 
RESOURCE_NAME                  STATE     MASTER    REMOTE_NODE     CNVTQ      GRNTQ   REQUEST_L GRANT_LEV
------------------------------ -------- ---------- ----------- ---------- ---------- --------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED           0           2          0          1  KJUSERNL  KJUSERPR
Summary:
  There is no physical or logical read on instance grac41 as get_segement_stats query shows no rows
  This particular block is not cached on the master node 
  No resource is allocated for this block 
  Query get_res_stat show that resource is granted on a protected read level
  Lock is granted to grac43 ( REMOTE_NODE 2 )

Status grac43:
@get_seg_stat
STAT                                    VAL
--------------------------------------- ----------
physical reads                                  1
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
                  0 0000000066FD9908          4        148          0          0
@get_res_name
HEXNAME                         RESOURCE_NAME  KJBLGRANT   KJBLROLE KJBLREQUE  M_INST     O_INST
------------------------------ --------------- --------- ---------- ---------  ---------- --------- 
[0x94][0x4],[BL][ext 0x0,0x0]  148,4,BL         KJUSERPR          0 KJUSERNL            0         2          

Summary:
  Instance 3 holds this block in SCUR mode ( STATE=2)
  the block was read from disk   
  Master node is instance 1 ( grac41 )
  A protected shared  read has been granted on this resource ( KJUSERPR ) 

Dump this data block as we need the bdba later
SQL> alter system dump datafile 4 block 148;

Example 2: Read the Block from Cache

Operation:   Instance 1: Master -  Instance 2: read block from cache  Instance 3: -
grac42: $ sql2 @get_emp
ROWID               EMPNO      ENAME       SAL       BLOCK_NO      FILE_NO
------------------  ---------- ---------- ---------- ---------- ----------
AAAVWcAAEAAAACUAAH        7788 SCOTT            3000        148        4

Status :grac41: ( Resource master )
RESOURCE_NAME               STATE              MAST REMOTE_NODE        CNVTQ      GRNTQ REQUEST_L GRANT_LEV
------------------------------ ---------------- ---------- ----------- ---------- ---------- --------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0         2        0       1 KJUSERNL  KJUSERPR
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0         1        0       1 KJUSERNL  KJUSERPR
- Block was granted with Protected Read ( KJUSERPR ) to instance grac43 and grac42
- Instance grac41 still has no refrence to that block in its buffer cache  

Status grac42:
@get_seg_stat
STAT                                    VAL
---------------------------------------------------------------- ----------
gc current blocks received                          1
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
           0 0000000066F9FF88        4     148          0      0
@get_res_name
HEXNAME                RESOURCE_NAME              KJBLGRANT   KJBLROLE KJBLREQUE       M_INST     O_INST
------------------------------ ------------------------------ --------- ---------- --------- ---------- ----------
[0x94][0x4],[BL][ext 0x0,0x0]  148,4,BL               KJUSERPR         0 KJUSERNL           0          2      
- block was read from network ( gc current blocks received )
- Instance 2 holds this block in SCUR mode ( STATE=2) - same mode as  for Instance grac43
- Master Instance has granted a Protected Read ( KJUSERPR ) to Instance 2 - same grant as for for Instance grac43

Status grac43:
no changes ( for details check Example1

Example 3: Update Block in Instance grac41

Operation:   Instance 1: Master  - Update block   Instance 2: -  Instance 3: -
grac1: 
SQL> update emp set sal=sal+1000 where empno = 7788;

Status grac41:
@get_seg_stat
STAT                                    VAL
---------------------------------------------------------------- ----------
logical reads                                 16
db block changes                             16
gc current blocks received                          1

@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
     1              0 0000000066F9A088        4     148          0      0
@get_res_name
HEXNAME                        RESOURCE_NAME                  KJBLGRANT   KJBLROLE KJBLREQUE
------------------------------ ------------------------------ --------- ---------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  148,4,BL                       KJUSEREX           0 KJUSERNL
@get_res_stat
RESOURCE_NAME               STATE              MAST REMOTE_NODE        CNVTQ      GRNTQ REQUEST_L GRANT_LEV
------------------------------ ---------------- ---------- ----------- ---------- ---------- --------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0         0        0       1 KJUSERNL  KJUSEREX
- block was read from the wire/Global cache ( gc current blocks received )
- Master ( grac41)  has granted the block to Instance grac41 in exclusiv mode ( KJUSEREX )
- X$bh ( STATE=1) shows tha block is in XCUR mode - this means grac41 has the most recenct block in its buffer cache
- Master replaces Both KJUSERPR locks ( for grac42 and grac43 ) by an explusive lock (  KJUSEREX  ) for grac41  

Status grac42/grac43:
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
         3        0               00            4       148     2849892      0
- buffer are changed to CR  on grac42 and grac43( STATE=3 ) 
- KJUSERPR locks were downgraded to NULL  on grac42 and grac43 
- Instance grac42 and grac43 no longer have any any locks for that resource

Example 4: Update Block in Instance grac43

Operation:   Instance 1: Master  -  Instance 2: Update block/COMMIT   Instance 3: -
Node to get the PI blocks you need to all scripts very quickly :
  - Update from Example 3
  - Update form this sample + COMMIT
  - Scritps to query statistics
In grac42
SQL> update emp set sal=sal+2000 where empno = 7369;
SQL> commit;

Status grac41 ( Resource Master )
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
                  0 00                      4      148     2865782      0
     8              0 0000000064F87C68        4     148            0      0
@get_res_name
HEXNAME                RESOURCE_NAME              KJBLGRANT   KJBLROLE KJBLREQUE
------------------------------ ------------------------------ --------- ---------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  148,4,BL               KJUSERNL           192 KJUSERNL
@get_res_stat
RESOURCE_NAME                   STATE             MAST     REMOTE_NODE CNVTQ      GRNTQ       REQUEST_L GRANT_LEV
------------------------------ ---------------- ---------- ----------- ---------- ---------- --------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED                  0         0            0          1   KJUSERNL  KJUSERNL
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED                  0                   0          1   KJUSERNL  KJUSEREX
- Master has granted instance grac42 exclusiv mode for that block (  KJUSEREX )
- Instance grac41 downconverts XCUR lock to state PI ( STATE=8 )
- A NULL mode lock indicated by KJUSERNL protects the PI block 
- The block in the buffer cache is changed to CR ( State=3) for instance grac41
- The former exclusive lock for that resource was removed 
- All the instances will discard th PI copies once the current block is written to disk

Status grac42:
@get_seg_stat
STAT                                    VAL
---------------------------------------------------------------- ----------
gc current blocks received                          2
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
     1             0  0000000066F9FF88        4     148          0      0
     3             0  00                      4     148    2849892      0
@get_res_name
HEXNAME                        RESOURCE_NAME          KJBLGRANT KJBLROLE KJBLREQUE
------------------------------ ---------------------- -------- --------- ---------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  148,4,BL               KJUSEREX         0 KJUSERNL
- Instance grac42 gets a block from cache fusion ( gc current blocks received )
- Instance grac42 holds the block in exclusive mode ( STATE=1 ) 
Summary:
  - Even the block holds an active transaction ( see Example 3 ) it can be successfully update from a 
    different instance if updated row is different

 

 Example 5: Commiting the long running operation issued in Example 3

Operation:   Instance 1 (Master ) :  COMMIT  Instance 2: -   Instance 3: -

Status grac41:
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
     8              0 0000000063FE7258        4     148          0      0
@get_res_name
HEXNAME                RESOURCE_NAME              KJBLGRANT  KJBLROLE KJBLREQUE
------------------------------ -----------------  --------- --------- ----------
[0x94][0x4],[BL][ext 0x0,0x0]  148,4,BL           KJUSERNL        192 KJUSERNL
@get_res_stat
RESOURCE_NAME               STATE              MAST REMOTE_NODE        CNVTQ      GRNTQ REQUEST_L GRANT_LEV
------------------------------ ---------------- ---------- ----------- ---------- ---------- --------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0         0        0       1 KJUSERNL  KJUSERNL
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0         1        0       1 KJUSERNL  KJUSEREX

Status grac42:
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
     1             0 0000000065FB2278        4     148          0      0
@get_res_name
HEXNAME                RESOURCE_NAME              KJBLGRANT   KJBLROLE KJBLREQUE
------------------------------ ------------------------------ --------- ---------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  148,4,BL               KJUSEREX        64 KJUSERNL
Summary:
 - Commit doesn't make any major changes on the global cache locks

 

Example 6: Disk writes due to checkpoint

SQL> alter system checkpoint global

Status grac41 : before checkpoint 
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
     8              0 0000000063FD1C58        4     148          0      0

RESOURCE_NAME               STATE              MAST REMOTE_NODE        CNVTQ      GRNTQ REQUEST_L GRANT_LEV
------------------------------ ---------------- ---------- ----------- ---------- ---------- --------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0         0        0       1 KJUSERNL  KJUSERNL
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0         1        0       1 KJUSERNL  KJUSEREX

Status grac41 : after checkpoint  
@get_buffer_stat
     STATE  MODE_HELD LE_ADDR            FILE#     BLOCK# CR_SCN_BAS CR_SCN_WRP
---------- ---------- ---------------- ---------- ---------- ---------- ----------
     3        0 00            4     148    2897491      0
@get_res_name
no rows selected
@get_res_stat
RESOURCE_NAME                  STATE                  MAST REMOTE_NODE     CNVTQ      GRNTQ REQUEST_L GRANT_LEV
------------------------------ ---------------- ---------- ----------- ---------- ---------- --------- ---------
[0x94][0x4],[BL][ext 0x0,0x0]  GRANTED             0                 1        0            1 KJUSERNL  KJUSEREX
Summary
- the checkpoint command from grac43 does not change any lock status in current mode ( KJUSEREX )
- The PI lock is discarded and the buffer is changed from PI mode  to CR mode 
- As we don't have any PI lock ( KJUSERNL )  protecting this resource was deleted

SQL scripts used in above samples

get_seg_stat.sql
select statistic_name stat, value val from v$segment_statistics
  where value >  0 and owner='SCOTT' and object_name='EMP';

get_buffer_stat.sql
set linesize 132
select state, mode_held, le_addr, dbarfil file#, dbablk block#, cr_scn_bas, cr_scn_wrp from x$bh
  where obj=(select data_object_id from dba_objects where  owner='SCOTT' and object_name='EMP'  and object_type='TABLE')   
  and dbablk=(select  dbms_rowid.rowid_block_number (rowid) Block_No from scott.emp where  empno = 7788 )
  and class=1;

get_res_name.sql
set linesize 132
select b.kjblname hexname, b.kjblname2 resource_name, b.kjblgrant, b.kjblrole, b.kjblrequest, b.KJBLMASTER M_Inst, b.KJBLOWNER O_inst
  from X$LE a , X$KJBL b where
 a.le_kjbl=b.kjbllockp  and a.le_addr =
  ( select le_addr from x$bh 
 where dbablk=(select  dbms_rowid.rowid_block_number (rowid) Block_No from scott.emp where  empno = 7788 )
 and and state !=3 and
   obj=(select data_object_id from dba_objects where  owner='SCOTT' and object_name='EMP' and object_type='TABLE'));

get_res_stat.sql
set linesize 132
col state format A16
select a.resource_name, b.state, a.master_node mast, b.owner_node remote_node, a.on_convert_q cnvtq,
  a.on_grant_q grntq, b.request_level, b.grant_level
from v$dlm_ress a , v$ges_enqueue b where
  upper(a.resource_name) = upper(b.resource_name1)
  and a.resource_name like '%[0x94][0x4],[BL]%';

Reference:

  • Oracle Database 11g Oracle Real Application Cluster Handabook Second Edition written by K. Gopalakrishan
  • Cache Fusion II Details

Leave a Reply

Your email address will not be published. Required fields are marked *