Tag Archives: _db_block_max_cr_dba

CONSISTENT READS IN ORACLE : PART- II

Database, Uncategorized, , ,

In my earlier post consistent reads in oracle part-I,  I had demonstrated that no. of CR clones of a block are dictated by the undocumented parameter _db_block_max_cr_dba. I had also mentioned that this is a soft limit in the sense that CR clones more than _db_block_max_cr_dba may also be created.

Let us see a practical demonstration of the above concept.

OVERVIEW:

  •  Create a table whose one block has 10 rows.
  •   Update one row in each of 10 sessions
  •  Check that no. of CR clones created > _db_block_max_cr_dba

IMPLEMENTATION:

– Create simple table whose one block has 10 rows

 

HR1> drop table t1 purge;
    create table t1
    (c1 int, c2 char(700));

HR1> var j number;
     begin
       :j := 1;
       for i in 1..10 loop
         insert into t1 values(:j, ‘x’);
         :j := :j+1;
       end loop;
     commit;
     end;
     /

   col c1 for 99
   select c1 from t1;

C1
—
1
2
3
4
5
6
7
8
9
10

– Get the block address and browse X$BH view for that block.

 

HR1> select dbms_rowid.rowid_relative_fno(rowid) as file#,
           dbms_rowid.rowid_block_number(rowid) as block#
    from t1;

FILE#     BLOCK#
———- ———-
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551
4        551

– Check that value of the parameter _db_block_max_cr_dba = 8

 

SYS>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                                                            8                                           F F F Maximum Allowed Number of CR buffers per dba

– Check in x$bh that We have one CURRENT block(state=xcur).
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: 551
old   7:       dbablk = &block_no
new   7:       dbablk = 551

DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4        551          1 xcur                0          0          0          0          0 T1
4        551          1 free                0          0          0          0          0

Now, let’s see how consecutive DML statements generate CR blocks.

— update and monitor cr block

— Start 10 HR sessions
— Update one row in each of 10 sessions

HR1> update t1 set c2 = ‘y’ where c1 = 1;
     /
HR2> update t1 set c2 = ‘y’ where c1 = 2;
     /
HR3> update t1 set c2 = ‘y’ where c1 = 3;
     /
HR4> update t1 set c2 = ‘y’ where c1 = 4;
     /
HR5> update t1 set c2 = ‘y’ where c1 = 5;
     /
HR6> update t1 set c2 = ‘y’ where c1 = 6;
     /
HR7> update t1 set c2 = ‘y’ where c1 = 7;
     /
HR8> update t1 set c2 = ‘y’ where c1 = 8;
     /
HR9> update t1 set c2 = ‘y’ where c1 = 9;
     /
HR10> update t1 set c2 = ‘y’ where c1 = 10;
     /

– Check in x$bh that We have one CURRENT block(state=xcur) and > 8 (_db_block_max_cr_dba) cr clones (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 = 551

DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4        551          1 cr           24257659          0          3        596       1025 T1
4        551          1 cr           24257658          0          3        181       1023 T1
4        551          1 cr           24257657          0          3        181       1023 T1
4        551          1 cr           24257651          0          3        181       1023 T1
4        551          1 cr           24257649          0          3     172517        954 T1
4        551          1 cr           24257648          0          3        181       1023 T1
4        551          1 cr           24257647          0          3        181       1023 T1
4        551          1 cr           24257645          0          3     176654       1063 T1
4        551          1 cr           24257639          0          3        181       1023 T1
4        551          1 cr           24257638          0          3        181       1023 T1
4        551          1 cr           24257635          0          3     279569       1005 T1

4        551          1 cr           24257634          0          3        181       1023 T1
4        551          1 cr           24257633          0          3        181       1023 T1
4        551          1 cr           24257622          0          3     175596        916 T1
4        551          1 cr           24257621          0          3        181       1023 T1
4        551          1 cr           24257620          0          3        181       1023 T1
4        551          1 xcur                0          0          0          0          0 T1

17 rows selected.

– CLEANUP –
HR1> rollback;
HR2>rollback;
HR3> rollback;
HR4> rollback;
HR5> rollback;
HR6> rollback;
HR7> rollback;
HR8> rollback;
HR9> rollback;
HR10> rollback;
     drop table t1 purge;

SYS>alter system set “_db_block_max_cr_dba”= 6 scope=spfile;
    startup force;

——————————————————————————-

Related links:

 

 

 

—————–

 

 

CONSISTENT READS IN ORACLE : PART – I

Database, Uncategorized, , , , ,
 
  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:

 

———————–