CONSISTENT READS IN ORACLE : PART – I

 
  In this post I will discuss and demonstrate how oracle creates CR (consistent read) blocks.
 

Read consistency implementation in Oracle

 
    Whenever a user queries data, he is to be displayed the data as of the time/SCN# when he queried it. 
If the desired block is not in the buffer,
    The block is read from the disk (status=xcur) 
If  clone(s) of the block are already there in buffer
      The clone just later than the desired time is selected.
      Undo is applied to the clone so as to make it consistent with the time/SCN# of the query (status=cr).
If the block is in the buffer and is dirty and no clones are there
    A copy(clone) of the current  block is made.
    Undo is applied to the clone so as to make it consistent with the time/SCN# of the query (status=cr)
 
DML is always performed on the current copy of the block (Status=xcur). 
Whenever an update is made to the block, 
  If the current block is in the buffer (status = xcur)
    A clone of the current block is made (status=cr)
    Update is performed on the current block (status=xcur)
Else (the desired block is not in the buffer)
    The block is read from the disk (status=xcur) 
     A clone of the block is also made (status=cr)
    Update is performed on the current block (status=xcur)
 
As multiple updates/queries are issued for the same block, multiple CR blocks of the same block may be created. Since all the CR clones of the same block are on the same chain, the processes searching for the block will take long time to scan the chain and there will be contention for CBC latch. Hence Oracle has limited the maximum no. of CR clones of a block  to 6 (one current (xcur) and 5 CR clones). But it is a soft limit in the sense that if more than 5 processes are simultaneously updating different rows in the same block, more than 6 CR blocks may be created. The maximum no. of CR clones of a block can be modified by an undocumented parameter _db_block_max_cr_dba.
 
Let us see a practical demonstration of the above concept:
 
– Create simple table whose one row occupies one block
HR1> drop table t1 purge;
    create table t1
    (c1 int, c2 char(2000), c3 char(2000), c4 char(2000));
– Insert one row
HR1> insert into t1 values(1, ‘x’, ‘x’, ‘x’);
     commit;
 
– Get the block address and browse X$BH view for that block.
HR> select dbms_rowid.rowid_relative_fno(rowid) as file#,
           dbms_rowid.rowid_block_number(rowid) as block#
    from t1;
File#         Block#
——        ——
    4        550
 
– Check in x$bh that We have one CURRENT block(state=xcur). CURRENT block is always one and only one.
– Enter file#/block# found above when prompted
SYS>select b.dbarfil, b.dbablk, b.class,
  decode(state,0,’free’,1,’xcur’,2,’scur’,3,’cr’, 4,’read’,5,’mrec’,6,’irec’,7,’write’,8,’pi’, 9,’memory’,10,’mwrite’,11,’donated’) as state,
  cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
  (select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = &file_no and
      dbablk = &block_no;
 
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
 
Now, let’s see how consecutive DML statements generate CR blocks.
– update and monitor cr block
– 1st update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Check in x$bh that We have one CURRENT block(state=xcur) and on cr clone (status=cr)
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
– 2nd update
HR> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
 DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T
 
– 3rd update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
  DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
– 4th update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245082          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
– 5th update
HR> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245122          0          0          0          0 T1
         4        550          1 cr           24245082          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
         4        550          1 cr           24244872          0          0          0          0 T1
 
6 rows selected.
 
– 6th update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
– Note that new cr clone has not been created because the limit of the 6 (1 xcur and 5 cr) has been reached.
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24245135          0          0          0          0 T1
         4        550          1 cr           24245122          0          0          0          0 T1
         4        550          1 cr           24245082          0          0          0          0 T1
         4        550          1 cr           24245051          0          0          0          0 T1
         4        550          1 cr           24244981          0          0          0          0 T1
 
6 rows selected.
 
– Check the value of the parameter _db_block_max_cr_dba
 
SELECT 
  a.ksppinm  “Parameter”,
  decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
  c.ksppstvl “Instance”,
  decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
  decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
  decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
  a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
 
Enter value for 1: _db_block_max_cr_dba
old  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new  12:   AND UPPER(a.ksppinm) LIKE UPPER(‘%_db_block_max_cr_dba%’)
 
Parameter                                          Session                      Instance                                    S I D Description
————————————————– —————————- ——————————————————- – – – ——————————————————————————————
_db_block_max_cr_dba                                                            6                                           F F F Maximum Allowed Number of CR buffers per dba
 
– Increase the value of the parameter to 8
 
SYS>alter system set “_db_block_max_cr_dba”= 8 scope=spfile;
    shu immediate;
    startup;
 
– Issue update seven times and check that 2 more clones have been created
 
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
        update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
  update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
  update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
      update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
    update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24246329          0          0          0          0 T1
         4        550          1 cr           24246328          0          0          0          0 T1
         4        550          1 cr           24246327          0          0          0          0 T1
         4        550          1 cr           24246325          0          0          0          0 T1
         4        550          1 cr           24246324          0          0          0          0 T1
         4        550          1 cr           24246323          0          0          0          0 T1
         4        550          1 cr           24246322          0          0          0          0 T1
 
8 rows selected.
 
– Issue a query from another session of HR
 
HR2>select * from t1;
 
– Check that a new clone has been created and an earlier one with 
   scn#=24246322 has been overwritten by scn#=24246435 and undo tablespace has been read to create this one. (uba_file=3,uba_blk=174754)
 
SYS>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
 
 
  DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
         4        550          1 cr           24246435          0          3     174754       1022 T1
         4        550          1 xcur                0          0          0          0          0 T1
         4        550          1 cr           24246329          0          0          0          0 T1
         4        550          1 cr           24246328          0          0          0          0 T1
         4        550          1 cr           24246327          0          0          0          0 T1
         4        550          1 cr           24246325          0          0          0          0 T1
         4        550          1 cr           24246324          0          0          0          0 T1
         4        550          1 cr           24246323          0          0          0          0 T1
 
—————————————————————————–

In my next post , I will demonstrate that _db_block_max_cr_dba is a soft limit and CR clones more than its value can be created.
—————————————————————————————————-
Related links:

 

———————–

5 thoughts on “CONSISTENT READS IN ORACLE : PART – I

  1. “If clone(s) of the block are already there in buffer
    The clone just earlier than the desired time is selected.”

    Hi, what do you mean by “earlier then desired time” is that the clone with new changes than desired time or clone with old changes than the desired time. if its old changes and if you are applying the undo then it will be old to old clone right ? please correct me.
    i suppose it should be redo to be applied but not quite sure …

    1. Sorry for the typo. It should be “later” instead of “earlier”. I have corrected it in the post. Thanx for pointing it out. Your comments and suggestions are always welcome.

      Regards
      Anju Garg

  2. Hi Anju,

    We are using undo to create cr in buffer cache, when a select is made.
    why we cant use cr @ 24244872 to create the new cr @24246435 ?

    Exactly when does the undo information of a block is loaded to undo tablespace?

    1. Hi Uday,

      We can create cr at a SCN from the block at a later SCN only by applying relevant undo. we cant use cr @ 24244872 to create the new cr @24246435 because cr @ 24244872 is earlier than the cr @24246435.

      Hope it answers your question. If you have doubt, please feel free to revert.

      Thanks and regards
      Anju Garg

Leave a Reply to uday Cancel reply