This article follows closely chapter 12 of following book:
Oracle Database 11g Oracle Real Application Cluster Handabook Second Edition written by K. Gopalakrishan
Table of Contents
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 ---------- ---------- ---------------- ---------- ---------- ---------- ---------- 2 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 ---------- ---------- ---------------- ---------- ---------- ---------- ---------- 2 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 ---------- ---------- ---------------- ---------- ---------- ---------- ---------- 3 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 1 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