oracle 一致读-1

</pre>这篇文章中主要演示oracle是如何创建一致读的<p></p><p><span style="font-size:14px;">当一个用户查询数据的时候，他想要查询的数据是跟他查询时候的scn有关的</span></p><p><span style="font-size:14px;">如果他要查询的数据不在buffer里</span></p><p><span style="font-size:14px;"><span style="white-space:pre">	</span>数据从磁盘读取，状态是xcur</span></p><p><span style="font-size:14px;">如果读取数据已经在buffer中存在一个或者多个clone</span></p><p><span style="font-size:14px;"><span style="white-space:pre">	</span>则晚于提交查询时间的那个clone块被选中，并应用undo信息来形成一致读，此时状态是cr</span></p><p><span style="font-size:14px;">如果读取的数据在buffer中并且已经被修改没有clone</span></p><p><span style="font-size:14px;"><span style="white-space:pre">	</span>那么clone会发生，并应用undo信息来形成一致读，此时状态是cr</span></p><p><span style="font-size:14px;">DML操作总是在xcur块上进行操作。不管什么时间在一个块上进行update操作</span></p><p><span style="font-size:14px;"><span style="white-space:pre">	</span>如果块中buffer中状态是xcur，那么一个clone块会生成状态是cr，update在xcur块上进行操作</span></p><p><span style="font-size:14px;"><span style="white-space:pre">	</span>除此(需求的块不在buffer中)，block从磁盘中读取状态是xcur，并且生成一个clone块，状态是cr，update在xcur块上执行</span></p><p><span style="font-size:14px;"></span></p><p><span style="font-size:14px;">多个update、query语句更新或者查询相同的块的时候，这个块就会生成多个cr块，因为一个块上所有的cr块都在一个chain上，那么进程查询这个chain的时候就会花费很多时间这样就会产生cbc latch的竞争。因此oracle会限定最大的cr块--6(一个xcur，5个cr)，这是有<span style="color: rgb(20, 20, 18); font-family: 'Times New Roman', serif; line-height: 16px; text-align: left; ">_db_block_max_cr_dba这个参数决定的。当然了如果多个进程同时更新一个块中的不同行，那么不止6个cr块产生了。</span></span></p><p><span style="font-size:14px;"><span style="color: rgb(20, 20, 18); font-family: 'Times New Roman', serif; line-height: 16px; text-align: left; ">验证:</span></span></p><p><span style="font-size:14px;"><span style="color: rgb(20, 20, 18); font-family: 'Times New Roman', serif; line-height: 16px; text-align: left; "></span></span></p><pre name="code" class="html">SQL> create table t1 (c1 int, c2 char(2000), c3 char(2000), c4 char(2000));

Table created.

SQL> insert into t1 values(1,'x','x','x');

1 row created.

SQL> commit;

Commit complete.

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

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


SQL> conn /as sysdba
Connected.
检查v$bh中有关t1的信息

SQL> 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,
  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
---------- ----------
OBJECT_NAME
--------------------------------------------------------------------------------
         4       9566          1 xcur             0          0          0
         0          0
T1

SQL> /

   DBARFIL     DBABLK      CLASS STATE   CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJEC
---------- ---------- ---------- ------- ---------- ---------- ---------- ---------- ---------- -----
         4       9566          1 xcur             0          0          0          0          0 T1

现在有一个xcur块

1、update
SQL> update clm.t1 set c2 = 'y', c3 = 'y', c4 ='y' where c1 = 1;  

1 row updated.

SQL> 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,
  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 xcur             0          0          0          0          0 T1
         4       9566          1 cr         1088625          0          0          0          0 T1

产生一个cr块

2、update
SQL> update clm.t1 set c2 = 'y', c3 = 'y', c4 ='y' where c1 = 1;      

1 row updated.

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 xcur             0          0          0          0          0 T1
         4       9566          1 cr         1088641          0          0          0          0 T1
         4       9566          1 cr         1088625          0          0          0          0 T1

<pre name="code" class="html" style="background-color: rgb(255, 255, 255); color: rgb(20, 20, 18); font-size: 14px; line-height: 16px; text-align: left; ">产生2个cr块

3、update

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

1 row updated.

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 xcur             0          0          0          0          0 T1         4       9566          1 cr         1088647          0          0          0          0 T1         4       9566          1 cr         1088641          0          0          0          0 T1         4       9566          1 cr         1088625          0          0          0          0 T1

<pre name="code" class="html" style="background-color: rgb(255, 255, 255); color: rgb(20, 20, 18); font-size: 14px; line-height: 16px; text-align: left; ">产生3个cr块

4、update

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

1 row updated.

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 xcur             0          0          0          0          0 T1         4       9566          1 cr         1088652          0          0          0          0 T1         4       9566          1 cr         1088647          0          0          0          0 T1         4       9566          1 cr         1088641          0          0          0          0 T1         4       9566          1 cr         1088625          0          0          0          0 T1

<pre name="code" class="html" style="background-color: rgb(255, 255, 255); color: rgb(20, 20, 18); font-size: 14px; line-height: 16px; text-align: left; ">产生4个cr块

5、updateSQL> update clm.t1 set c2 = 'y', c3 = 'y', c4 ='y' where c1 = 1;  

1 row updated.

SQL> 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,  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 xcur             0          0          0          0          0 T1         4       9566          1 cr         1088658          0          0          0          0 T1         4       9566          1 cr         1088652          0          0          0          0 T1         4       9566          1 cr         1088647          0          0          0          0 T1         4       9566          1 cr         1088641          0          0          0          0 T1         4       9566          1 cr         1088625          0          0          0          0 T1<pre name="code" class="html" style="background-color: rgb(255, 255, 255); color: rgb(20, 20, 18); font-size: 14px; line-height: 16px; text-align: left; ">产生5个cr块

6 rows selected.

6、update

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

1 row updated.

SQL> 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,  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 xcur             0          0          0          0          0 T1         4       9566          1 cr         1088667          0          0          0          0 T1         4       9566          1 cr         1088658          0          0          0          0 T1         4       9566          1 cr         1088652          0          0          0          0 T1         4       9566          1 cr         1088647          0          0          0          0 T1         4       9566          1 cr         1088641          0          0          0          0 T16 rows selected.SQL>

没有再生成新的cr块

</pre><p>查看隐藏参数值</p><p>SQL> show parameter _db_block_max_cr;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------_db_block_max_cr_dba                 integer     6</p><p></p><p>从另外一个session查询t1表</p><p>select * from t1;</p><p><pre name="code" class="html">SQL> /

   DBARFIL     DBABLK      CLASS STATE   CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJEC
---------- ---------- ---------- ------- ---------- ---------- ---------- ---------- ---------- -----
         4       9566          1 cr         1089237          0          3       1594        199 T1
         4       9566          1 xcur             0          0          0          0          0 T1
         4       9566          1 cr         1089232          0          0          0          0 T1
         4       9566          1 cr         1089231          0          0          0          0 T1
         4       9566          1 cr         1089229          0          0          0          0 T1
         4       9566          1 cr         1088667          0          0          0          0 T1

6 rows selected.

看到新的cr块生成了，最早的cr块被覆盖了，并且必须读取undo表空间数据来生成这个新的cr块