oracle 一致读2

在上一篇文章中说的_db_block_max_cr_dba是对一个一个块中的同一行进行更新的时候最多生成的cr块，下面主要说一下对于同一块中的不同行同时更新时会产生多个cr块

SQL>  drop table clm.t1 purge;

Table dropped.

SQL> create table clm.t1    (c1 int, c2 char(700));

Table created.


SQL> l
  1       begin
  2         :j := 1;
  3         for i in 1..10 loop
  4           insert into clm.t1 values(:j, ‘x’);
  5           :j := :j+1;
  6         end loop;
  7       commit;
  8*      end;
SQL> 4
  4*          insert into clm.t1 values(:j, ‘x’);
SQL> c /‘x’/'x'/
  4*          insert into clm.t1 values(:j, 'x');
SQL> l
  1       begin
  2         :j := 1;
  3         for i in 1..10 loop
  4           insert into clm.t1 values(:j, 'x');
  5           :j := :j+1;
  6         end loop;
  7       commit;
  8*      end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select c1 from clm.t1;

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

10 rows selected.

SQL> select dbms_rowid.rowid_relative_fno(rowid) as file#,
  2             dbms_rowid.rowid_block_number(rowid) as block#
  3      from clm.t1;

     FILE#     BLOCK#
---------- ----------
         4       9566
         4       9566
         4       9566
         4       9566
         4       9566
         4       9566
         4       9566
         4       9566
         4       9566
         4       9566

10 rows selected.
多个


SQL> update clm.t1 set c2 ='y' where c1=1;

1 row updated.
.........要有多个session进行更新不同的行
SQL> select b.dbarfil, b.dbablk, b.class,
  2    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,
  3    cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
  4    (select object_name from dba_objects where object_id = b.obj) as object_name
  5  from sys.x$bh b
  6  where dbarfil = 4 and
  7        dbablk = 9566;

   DBARFIL     DBABLK      CLASS STATE   CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJEC
---------- ---------- ---------- ------- ---------- ---------- ---------- ---------- ---------- -----
         4       9566          1 cr         1089657          0          3      78801        184 T1
         4       9566          1 cr         1089656          0          3      78801        184 T1
         4       9566          1 cr         1089651          0          3      78801        184 T1
         4       9566          1 cr         1089650          0          3      78801        184 T1
         4       9566          1 cr         1089644          0          3       1120        194 T1
         4       9566          1 cr         1089643          0          3       1120        194 T1
         4       9566          1 xcur             0          0          0          0          0 T1

7 rows selected.

SQL> show parameter _db_block_max_cr;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_db_block_max_cr_dba                 integer     6