一致性读内部原理分析

1. 环境准备

大家都知道Oracle一致读的基本概念,就是当一个会话要读取的块被其它会话修改了,那就会通过UNDO产生CR块,进行一致性读。本文只讨论以下这种情况,Let’s go

T1       Session 1   修改块
T2       Sessino 2   读取块
T3       Session 1   Commit

1.1 创建相关表

SQL> create table t1 as select * from all_objects;
Table created.

SQL> create index idx_t1 on t1(object_id);
Index created.

SQL> exec dbms_stats.gather_table_stats('SYS', 'T1', cascade=>true, estimate_percent=>100);
PL/SQL procedure successfully completed.

SQL> select * from dba_extents e where e.owner = 'SYS' and e.segment_name = 'T1';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID FILE_ID   BLOCK_ID      BYTES BLOCKS RELATIVE_FNO
----- ------------ -------------- ------------ --------------- ---------- ------- ---------- ---------- ------ ------------
SYS   T1                          TABLE        SYSTEM                   0       1      90232      65536      8            1
SYS   T1                          TABLE        SYSTEM                   1       1      90240      65536      8            1
SYS   T1                          TABLE        SYSTEM                   2       1      90248      65536      8            1
SYS   T1                          TABLE        SYSTEM                   3       1      90256      65536      8            1
SYS   T1                          TABLE        SYSTEM                   4       1      90264      65536      8            1
SYS   T1                          TABLE        SYSTEM                   5       1      90272      65536      8            1
SYS   T1                          TABLE        SYSTEM                   6       1      90280      65536      8            1
SYS   T1                          TABLE        SYSTEM                   7       1      90288      65536      8            1
SYS   T1                          TABLE        SYSTEM                   8       1      90296      65536      8            1
SYS   T1                          TABLE        SYSTEM                   9       1      90304      65536      8            1
SYS   T1                          TABLE        SYSTEM                  10       1      90312      65536      8            1
SYS   T1                          TABLE        SYSTEM                  11       1      90320      65536      8            1
SYS   T1                          TABLE        SYSTEM                  12       1      92800      65536      8            1
SYS   T1                          TABLE        SYSTEM                  13       1      92808      65536      8            1
SYS   T1                          TABLE        SYSTEM                  14       1      92816      65536      8            1
SYS   T1                          TABLE        SYSTEM                  15       1      92824      65536      8            1
SYS   T1                          TABLE        SYSTEM                  16       1      92928    1048576    128            1
SYS   T1                          TABLE        SYSTEM                  17       1      93056    1048576    128            1
SYS   T1                          TABLE        SYSTEM                  18       1      93184    1048576    128            1
SYS   T1                          TABLE        SYSTEM                  19       1      93312    1048576    128            1
SYS   T1                          TABLE        SYSTEM                  20       1      93440    1048576    128            1
SYS   T1                          TABLE        SYSTEM                  21       1      93568    1048576    128            1
SYS   T1                          TABLE        SYSTEM                  22       1      93696    1048576    128            1
SYS   T1                          TABLE        SYSTEM                  23       1      93824    1048576    128            1
 24 rows selected

select owner, object_name,
       dbms_rowid.rowid_relative_fno(rowid) as fno,
       dbms_rowid.rowid_block_number(rowid) as bno,
       dbms_rowid.rowid_row_number(rowid) as rno,
       to_char(dbms_utility.make_data_block_address(dbms_rowid.rowid_relative_fno(rowid),
               dbms_rowid.rowid_block_number(rowid)), 'xxxxxxxxxx') as dba
 from t1 where object_id = 7840;
OWNER   OBJECT_NAME      FNO   BNO  RNO DBA
------- --------------- ---- ----- ---- -----------
SYS     DBFS_SFS$_FSTO     1 92803   19      416a83

DBA字段是构建了这个数据块的16进制地址,这行记录处于t1表分配的extents中间位置,这是后面想测试查询的记录

1.2 构建大的事务

更新全表,但不提交,构建一个大的事务

SQL> update t1 set OBJECT_NAME = 'bbbbbb';
 73087 rows updated.

set linesize 160
set pagesize 999
column usn format 99999
column slot format 99999
column sqn format 99999999
column segment_name format a23 heading 'Segment|Name'
column tablespace_name format a15 heading 'TableSpace|Name'
column start_ubafil format 9999 heading 'Start|UBA FILE'
column start_ubablk format 99999999 heading 'Start|UBA Block'
column start_ubarec format 99999999 heading 'Start|UBA Record'
column ubafil format 9999 heading 'End|UBA FILE'
column ubablk format 99999999 heading 'End|UBA Block'
column ubarec format 99999999 heading 'End|UBA Record'
column used_ublk format 99999999 heading 'Used|Block'
column SID format 9999999 heading 'Session|Id'
column status format 9999999 heading 'Session|Status'

select /*+rule*/ r.USN, t.XIDSLOT slot, XIDSQN sqn,
       t.start_ubafil, t.start_ubablk, t.start_ubarec,
       t.ubafil, t.ubablk, t.ubarec,
       t.used_ublk, rs.segment_name, rs.tablespace_name, s.SID,
       s.status
   from v$rollstat    r,
       v$transaction t,
       v$session     s,
       dba_rollback_segs rs
 where r.USN = t.XIDUSN
   and s.SADDR = t.SES_ADDR
   and r.USN   = rs.segment_id
  order by t.used_ublk desc;

                           Start     Start      Start      End       End        End      Used Segment                 TableSpace       Session Session
   USN   SLOT       SQN UBA FILE UBA Block UBA Record UBA FILE UBA Block UBA Record     Block Name                    Name                  Id Status
------ ------ --------- -------- --------- ---------- -------- --------- ---------- --------- ----------------------- --------------- -------- --------
     7      7       847        3      1211         11        3      7289         58      1669 _SYSSMU7_1924883037$    UNDOTBS1              68 INACTIVE

1.3 转储事务的回滚信息

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_5642.trc
SQL> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU7_1924883037$' XID 7 7 847;
System altered.

另外转储一下事务的回滚段头,配合分析

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_5881.trc
SQL> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU7_1924883037$';
System altered.

1.4 Event跟踪查询记录前景象

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_12261.trc
SQL> ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';
Session altered.
SQL> oradebug event 10046 trace name context forever, level 255
Statement processed.
SQL> set linesize 180 pagesize 999
SQL> set autotrace on stat
SQL> select owner, object_name from t1 where object_id = 7840;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            DBFS_SFS$_FSTO

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        159  consistent gets
          0  physical reads
        108  redo size
        500  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

1.5 转储查询的块

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_6211.trc
SQL> alter system dump datafile 1 block 92803;
System altered.

2. 分析回滚段头

回滚段头的跟踪文件:PROD1_ora_5881.trc, 回滚段头保存了事务表的信息

********************************************************************************
Undo Segment:  _SYSSMU7_1924883037$ (7)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 29     #blocks: 1791
                  last map  0x00000000  #maps: 0      offset: 4080
      Highwater::  0x00c01c79  ext#: 16     blk#: 121    ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 16
                   Unlocked
     Map Header:: next  0x00000000  #extents: 29   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub          stmt_num    cmt
  -------------------------------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x034e  0x0005  0x0000.0026249a  0x00c004ba  0x0000.000.00000000  0x00000002   0x00000000  1479169240
   0x01    9    0x00  0x034f  0x0018  0x0000.002623bd  0x00c004b9  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x02    9    0x00  0x034f  0x0020  0x0000.002674c1  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479170777
   0x03    9    0x00  0x034f  0x000a  0x0000.00266025  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x04    9    0x00  0x034f  0x000b  0x0000.00265fb2  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x05    9    0x00  0x034f  0x0009  0x0000.0026250d  0x00c004bb  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x06    9    0x00  0x034f  0x0021  0x0000.00266063  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x07   10    0x80  0x034f  0x0019  0x0000.00267544  0x00c01c79  0x0000.000.00000000  0x00000685   0x00000000  0           --> 事务使用的回滚段头上的槽位
   0x08    9    0x00  0x034f  0x0012  0x0000.00265f84  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x09    9    0x00  0x034e  0x0017  0x0000.0026250e  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x0a    9    0x00  0x034f  0x000e  0x0000.00266027  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x0b    9    0x00  0x034f  0x0015  0x0000.00265fb4  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x0c    9    0x00  0x034f  0x0002  0x0000.00266d73  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479170222
   0x0d    9    0x00  0x034e  0x000f  0x0000.00262515  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x0e    9    0x00  0x034f  0x0006  0x0000.00266035  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x0f    9    0x00  0x034f  0x001b  0x0000.00262516  0x00c004bb  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x10    9    0x00  0x034d  0x001a  0x0000.00262496  0x00c004b8  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x11    9    0x00  0x034e  0x0010  0x0000.0026245e  0x00c004b9  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x12    9    0x00  0x034f  0x0014  0x0000.00265f95  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x13    9    0x00  0x034e  0x0019  0x0000.00262520  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x14    9    0x00  0x034e  0x0004  0x0000.00265f9b  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x15    9    0x00  0x034e  0x0003  0x0000.00265fc9  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
   0x16    9    0x00  0x034f  0xffff  0x0000.00267528  0x00c004bb  0x0000.000.00000000  0x00000001   0x00000000  1479170804
   0x17    9    0x00  0x034f  0x000d  0x0000.00262511  0x00c004bb  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x18    9    0x00  0x034e  0x0011  0x0000.002623ed  0x00c004b9  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x19    9    0x00  0x034e  0x001c  0x0000.00262654  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169241
   0x1a    9    0x00  0x034e  0x0000  0x0000.00262498  0x00c004b8  0x0000.000.00000000  0x00000001   0x00000000  1479169240
   0x1b    9    0x00  0x034e  0x001d  0x0000.00262518  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1479169240
   0x1c    9    0x00  0x034e  0x0008  0x0000.0026280b  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169540
   0x1d    9    0x00  0x034e  0x0013  0x0000.0026251e  0x00c004bc  0x0000.000.00000000  0x00000002   0x00000000  1479169240
   0x1e    9    0x00  0x034f  0x0016  0x0000.0026751e  0x00c004bb  0x0000.000.00000000  0x00000001   0x00000000  1479170804
   0x1f    9    0x00  0x034e  0x0001  0x0000.0026236b  0x00c004b8  0x0000.000.00000000  0x00000002   0x00000000  1479169240
   0x20    9    0x00  0x034f  0x001e  0x0000.00267500  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479170804
   0x21    9    0x00  0x034e  0x000c  0x0000.00266066  0x00c004ba  0x0000.000.00000000  0x00000001   0x00000000  1479169921
SQL> select s.tablespace_name, s.header_file, s.header_block
  2  from dba_segments s where s.segment_name = '_SYSSMU7_1924883037$';

TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
UNDOTBS1                                 3          224

‘_SYSSMU7_1924883037$’这个回滚段的段头位置是file 3, block 224
而update事务所使用的槽位是回滚段段头的7号槽位(ITL相关信息和事务表里state=10),
该槽位指向的最后一条回滚信息的UNDO块位置是:0x00c01c79
也就是说update事务,最后指向的UNDO块的位置是:0x00c01c79(前面UNDO链表,最后一个UNDO块是0x00c01c79)

select  dbms_utility.data_block_address_file(to_number('00c01c79', 'xxxxxxxxxxxxxxx')) as fno,
        dbms_utility.data_block_address_block(to_number('00c01c79', 'xxxxxxxxxxxxxxx')) as bno
from dual;
       FNO        BNO
---------- ----------
         3       7289

这在前面v$transaction视图的ubafil,ubablock字段有体现。但是这个UNDO块只是这个事务的回滚信息结束位置。
而我们前面查询的t1.object_id=7840,肯定是在这个UNDO链表的中间位置。
如何找到这个信息(也就是说Oracle如何去快速找到记录的前景象),就要靠数据块的ITL槽位信息了,
否则Oracle就需要扫描整个undo链,代价太大。

3. Oracle如何查询记录的前景象

3.1 分析查询的数据块信息

前面select owner, object_name from t1 where object_id = 7840;这个SQL语句查询的t1表的数据块是:
file#=1 block#=92803,前面转储该块的文件为:PROD1_ora_6211.trc

查看块头信息和对应的数据行信息:

Block header dump:  0x00416a83
 Object id on Block? Y
 seg/obj: 0x1309a  csc: 0x00.269d86  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00267438
0x02   0x0007.007.0000034f  0x00c018d2.01e1.1a  ----   77  fsc 0x036e.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x00416a83
data_block_dump,data header at 0x7c6674
===============
tsiz: 0x1f88
hsiz: 0xac
pbl: 0x007c6674
     76543210
......
tab 0, row 19, @0x194a
tl: 80 fb: --H-FL-- lb: 0x2  cc: 14
col  0: [ 3]  53 59 53
col  1: [ 6]  62 62 62 62 62 62
col  2: *NULL*
col  3: [ 3]  c2 4f 29                      -->  转成数值为:7840,与前面select一致
col  4: [ 3]  c2 4f 29
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 6f 09 12 12 24 09
col  7: [ 7]  78 6f 09 12 12 24 09
col  8: [19]  32 30 31 31 2d 30 39 2d 31 38 3a 31 37 3a 33 35 3a 30 38
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 02
......

数据行信息里:
lb: 0x2 表明使用ITL槽位是2号,从上面的ITL槽位信息得知:
Xid = 0x0007.007.0000034f 转成十进制就是 7.7.847,与v$transaction视图中查出USN SLOT SQN的信息一致
Uba = 0x00c018d2.01e1.1a
将这个UBA分解:0x00c018d2是undo record所处undo块的地址,seq:01e1是顺序号,1a是UNDO记录位置。

SQL> select  dbms_utility.data_block_address_file(to_number('00c018d2', 'xxxxxxxxxxxxxxx')) as fno,
  2          dbms_utility.data_block_address_block(to_number('00c018d2', 'xxxxxxxxxxxxxxx')) as bno
  3  from dual;
       FNO        BNO
---------- ----------
         3       6354

注意:这个UBA地址(0x00c018d2.01e1.1a)是数据块上ITL槽位指到的对应UNDO的最后一条信息,而不是整个事务的最后一条undo信息,也就是指明了某个数据块所涉及的UNDO信息的最后位置,这样就给一致性读带来了极大的方便,直接定位到这个数据块涉及的最后的UNDO信息,延着UNDO链稍微往上检查,基本就可以检查到数据行的相关UNDO信息。

3.2 转储分析ITL指向的UNDO块

把这个ITL槽位指向的UNDO块转储:

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_7776.trc
SQL> alter system dump datafile 3 block 6354;
System altered.

********************************************************************************
UNDO BLK:
xid: 0x0007.007.0000034f  seq: 0x1e1 cnt: 0x4c  irb: 0x4c  icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f7c     0x02 0x1f0c     0x03 0x1ea8     0x04 0x1e44     0x05 0x1de0
0x06 0x1d7c     0x07 0x1d0c     0x08 0x1c9c     0x09 0x1c2c     0x0a 0x1bbc
0x0b 0x1b50     0x0c 0x1ae4     0x0d 0x1a74     0x0e 0x1a04     0x0f 0x1990
0x10 0x191c     0x11 0x18a4     0x12 0x182c     0x13 0x17c8     0x14 0x1764
0x15 0x1700     0x16 0x169c     0x17 0x163c     0x18 0x15dc     0x19 0x157c
0x1a 0x151c     0x1b 0x14b8     0x1c 0x1454     0x1d 0x13f4     0x1e 0x1394
0x1f 0x1330     0x20 0x12cc     0x21 0x126c     0x22 0x120c     0x23 0x11ac
0x24 0x114c     0x25 0x10e8     0x26 0x1078     0x27 0x1014     0x28 0x0fa8
0x29 0x0f38     0x2a 0x0ecc     0x2b 0x0e5c     0x2c 0x0dec     0x2d 0x0d88
0x2e 0x0d18     0x2f 0x0ca8     0x30 0x0c3c     0x31 0x0bd0     0x32 0x0b64
0x33 0x0af8     0x34 0x0a8c     0x35 0x0a20     0x36 0x09b4     0x37 0x0954
0x38 0x08e8     0x39 0x087c     0x3a 0x0810     0x3b 0x07b0     0x3c 0x0748
0x3d 0x06e0     0x3e 0x067c     0x3f 0x0618     0x40 0x05b0     0x41 0x0548
0x42 0x04e4     0x43 0x0480     0x44 0x041c     0x45 0x03b8     0x46 0x0344
0x47 0x02d0     0x48 0x0268     0x49 0x01f8     0x4a 0x018c     0x4b 0x0120
0x4c 0x00b4
..........

从前面数据块的ITL槽位信息可以知道,指向的UNDO记录位置是:0x1a,在这个UNDO块的转储信息中找到 Rec #0x1a记录。
这个记录只是ITL槽位指向的最后一条UNDO记录,而不是object_id=7840这条记录对应的UNDO记录。

先分析一下#0x1a这条UNDO记录的结构:

*-----------------------------
* Rec #0x1a  slt: 0x07  objn: 77978(0x0001309a)  objd: 77978  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x19
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c018d2.01e1.19
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00416a83  hdba: 0x00416078
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 76(0x4c) flag: 0x2c lock: 2 ckix: 191
ncol: 14 nnew: 1 size: 1
col  1: [ 7]  55 54 4c 5f 55 52 4c

这条undo记录对应的数据块位置是:bdba: 0x00416a83,这与PROD1_ora_6211.trc文件(t1.object_id = 7840)里数据块位置一致,
与1.1节中查询记录构建的数据块地址一致。

Rec #0x1a:回滚记录号,这是由数据块中的ITL槽位指到,该记录号是ITL槽位指到的该数据块对应的最后一条回滚记录
slt: 0x07:槽号
objn: 77978(0x0001309a):dba_objects.object_id列,此处是T1的对象号
objd: 77978:dba_objects.data_object_id列,T1的数据对象号
tblspc: 0(0x00000000):T1所在表空间编号
Layer: 11 (Row)
rci:该参数代表的就是UNDO chain(同一事务中的多次修改,根据chain链接关联)的前一回滚记录的编号,此处为0x19。
如果为0x00,表示是最后一个修改记录。
bdba:该参数是指原数据块,所处的DBA
itli: 该参数是指对应数据块的ITL槽(待验证)
slot: 76 对应的数据块中的数据行
rdba:

这个回滚记录,只是数据块ITL槽位指到的UNDO记录,而实际的第19行记录所对应的UNDO记录,还需要通过这条UNDO记录所在的UNDO链来查找,
手工根据这个UNDO链来查询比较麻烦,这里先通过dump,并且到事务转储(PROD1_ora_5642.trc)文件里先直接找到相应的UNDO记录

SQL> select dump('DBFS_SFS$_FSTO', 16) from dual;
DUMP('DBFS_SFS$_FSTO',16)
--------------------------------------------------------
Typ=96 Len=14: 44,42,46,53,5f,53,46,53,24,5f,46,53,54,4f
*-----------------------------
* Rec #0x2b  slt: 0x07  objn: 77978(0x0001309a)  objd: 77978  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x2a
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c018d1.01e1.2a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00416a83  hdba: 0x00416078
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 19(0x13) flag: 0x2c lock: 2 ckix: 191
ncol: 14 nnew: 1 size: 8
col  1: [14]  44 42 46 53 5f 53 46 53 24 5f 46 53 54 4f

从这里分析:ITL槽位指到的UBA是:0x00c018d2.01e1.1a
而实际的第19行记录对应的UNDO记录的UBA是:0x00c018d1.01e1.2a
两个不是同一个UNDO块
如何匹配UNDO记录和对应的数据记录,是根据UNDO记录中的bdba + slot
再从ITL槽位指到的UNDO记录查询UNDO串,往上分析,
如:Rec #0x1a记录的rci 0x19,指到Rec #0x19,分析bdba + slot是否与数据块中的一致
Rec #0x19记录的rci 0x18,指到Rec #0x18,分析bdba + slot是否与数据块中的一致
…..
以此类推,推到Rec #0x1第一条记录

*-----------------------------
* Rec #0x1  slt: 0x07  objn: 77978(0x0001309a)  objd: 77978  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00c018d1
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c018d1.01e1.4a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00416a83  hdba: 0x00416078
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 51(0x33) flag: 0x2c lock: 2 ckix: 191
ncol: 14 nnew: 1 size: 14
col  1: [20]  44 42 46 53 5f 48 53 24 5f 50 4f 4c 49 43 59 49 44 53 45 51

这条记录rci 0x00,表明是这个UNDO块第一条UNDO记录了,但是注意RDBA信息,
其它记录都无RDBA信息,而只有第一条记录有RDBA信息。
那就表明,当前UNDO块的前一个UNDO块是:0x00c018d1

继续转储:0x00c018d1这个UNDO块

SQL> select  dbms_utility.data_block_address_file(to_number('00c018d1', 'xxxxxxxxxxxxxxx')) as fno,
  2          dbms_utility.data_block_address_block(to_number('00c018d1', 'xxxxxxxxxxxxxxx')) as bno
  3  from dual;
       FNO        BNO
---------- ----------
         3       6353
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_10996.trc
SQL> alter system dump datafile 3 block 6353;
System altered.
********************************************************************************
UNDO BLK:
xid: 0x0007.007.0000034f  seq: 0x1e1 cnt: 0x4a  irb: 0x4a  icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f78     0x02 0x1f04     0x03 0x1e90     0x04 0x1e24     0x05 0x1db8
0x06 0x1d44     0x07 0x1cd0     0x08 0x1c5c     0x09 0x1be4     0x0a 0x1b80
0x0b 0x1b0c     0x0c 0x1aa8     0x0d 0x1a44     0x0e 0x19e0     0x0f 0x197c
0x10 0x190c     0x11 0x18a8     0x12 0x1844     0x13 0x17dc     0x14 0x1778
0x15 0x1714     0x16 0x16ac     0x17 0x1644     0x18 0x15d4     0x19 0x1570
0x1a 0x150c     0x1b 0x14a8     0x1c 0x1440     0x1d 0x13dc     0x1e 0x1374
0x1f 0x1310     0x20 0x12ac     0x21 0x1248     0x22 0x11e4     0x23 0x117c
0x24 0x1114     0x25 0x10b0     0x26 0x103c     0x27 0x0fd8     0x28 0x0f74
0x29 0x0f0c     0x2a 0x0ea8     0x2b 0x0e40     0x2c 0x0dd4     0x2d 0x0d64
0x2e 0x0d00     0x2f 0x0c8c     0x30 0x0c28     0x31 0x0bc4     0x32 0x0b60
0x33 0x0afc     0x34 0x0a98     0x35 0x0a30     0x36 0x09c4     0x37 0x0954
0x38 0x08e0     0x39 0x086c     0x3a 0x07fc     0x3b 0x0788     0x3c 0x0714
0x3d 0x06a4     0x3e 0x0640     0x3f 0x05d0     0x40 0x055c     0x41 0x04e8
0x42 0x0470     0x43 0x03f8     0x44 0x0388     0x45 0x0314     0x46 0x02a4
0x47 0x0240     0x48 0x01d4     0x49 0x0164     0x4a 0x00f8

这个UNDO块的最后一条记录是:irb: 0x4a
继续类似上一个UNDO串的查找方式,终于在这个文件中找到19行记录对应的UNDO记录

*-----------------------------
* Rec #0x2b  slt: 0x07  objn: 77978(0x0001309a)  objd: 77978  tblspc: 0(0x00000000)
*       Layer:  11 (Row)   opc: 1   rci 0x2a
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c018d1.01e1.2a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00416a83  hdba: 0x00416078
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 19(0x13) flag: 0x2c lock: 2 ckix: 191
ncol: 14 nnew: 1 size: 8
col  1: [14]  44 42 46 53 5f 53 46 53 24 5f 46 53 54 4f

数据块19行记录对应的UNDO记录(bdba: 0x00416a83, slot: 19(0x13))

4. 分析Event跟踪

从1.4节的跟踪文件里可以看出:查询到数据块,首先跟踪到
这里是转储了ITL槽位指到的UNDO链的所有UNDO信息

Applying CR undo to block 0 : 416a83 itl entry 02:                      --> 416a83是数据块地址, itl entry是ITL槽位
          xid:  0x0007.007.0000034f uba: 0x00c018d2.01e1.1a
          flg: ----    lkc: 77     fsc: 0x036e.00000000
......

0x00c018d2.01e1.1a,下接着是0x00c018d2.01e1.19
Applying CR undo to block 0 : 416a83 itl entry 02:
xid: 0x0007.007.0000034f uba: 0x00c018d2.01e1.19
flg: —- lkc: 77 fsc: 0x036d.00000000

直到找到:
Applying CR undo to block 0 : 416a83 itl entry 02:
xid: 0x0007.007.0000034f uba: 0x00c018d1.01e1.2b
flg: —- lkc: 77 fsc: 0x0090.00000000

所以Oracle一致读的大致读取路径和分析的应该一致

关于紫砂壶

感悟技术人生
此条目发表在Internal分类目录,贴了标签。将固定链接加入收藏夹。

2 则回应给 一致性读内部原理分析

  1. killsql说:

    快照1的表名 应该是 tmp1_dtm?

  2. 紫砂壶说:

    笔误,感谢提出

评论已关闭。