undo transaction slot被覆盖引起ORA-01555的原理解析

测试环境：Oracle 11gR2 Restart测试目的：模拟undo header事务表槽被覆盖引起ORA-01555的现象及原理解析场景介绍： Session 1：获取scott.tabnow1表中记录对应的relative_fno和block_number；Update一条记录，但不commit，记录下所使用的xid、uba信息Session 2：以非SYS用户连接执行：set transaction read only;Session 1：Commit;Session 3：对scott.t1表连续做update+commit操作Session 2：成功遍历scott.tabnow1表select * from scott.tabnow1;Session 4：再次对scott.t1表连续做update+commit操作，继续覆盖事务表Session 2：再次遍历scott.tabnow1表得到ORA-01555错误测试数据库配置：##为简化测试过程，建立了一个256K的undotbsSQL> show parameter undo NAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOSIG SQL> select d.name,d.bytes from v$datafile d,v$tablespace t where t.ts#=d.ts# and t.name="UNDOSIG"; NAME BYTES---------------------------------------- ----------+STESTDG1/stest2/undosig.dbf 262144 ##undosig里只有一个undo segment onlineSQL> select segment_name,tablespace_name,relative_fno,status from dba_rollback_segs where tablespace_name="UNDOSIG"; SEGMENT_NAME TABLESPACE_NAME RELATIVE_FNO STATUS------------------------------ ------------------------------ ------------ ----------------_SYSSMU2$ UNDOSIG 8 ONLINE ##undo segment里extent的分布情况，除去undo header所占据的block 8，block 9~31都可以被事务用来存放修改前内容：SQL> select segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name="_SYSSMU2$"; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS------------------------------ ---------- ---------- ---------- ----------_SYSSMU2$ 0 8 8 8_SYSSMU2$ 1 8 16 8_SYSSMU2$ 2 8 24 8 ##测试用到的两张表初始内容：SQL> select * from scott.tabnow1; USERNAME USER_ID CREATED------------------------------ ---------- ------------XS$NULLLL 2147483638 21-OCT-11NEWUSER 84 12-MAR-14SCOTTTTT 83 21-OCT-11OWBSYS_AUDIT 82 21-OCT-11OWBSYS 78 21-OCT-11APEX 77 21-OCT-11APEX_PUBLIC 75 21-OCT-11FLOWS_FILE 74 21-OCT-11MGMT_VIEW 73 21-OCT-11DDD 34 28-MAY-14 SQL> select * from scott.t1; ID---------- 34 34 开始测试过程：///////////////session 1://///////////##确定scott.tabnow1表中的记录所在的块，便于之后对data block作dump；所有的行都在同一个block中select dbms_rowid.rowid_relative_fno（rowid） rfno,dbms_rowid.rowid_block_number（rowid） blkno from scott.tabnow1; RFNO BLKNO---------- ---------- 4 1779 4 1779 4 1779 4 1779 4 1779 4 1779 4 1779 4 1779 4 1779 4 1779##记录一下undo header的初始状态，slot 0x1e将成为下一个要用到的slot TRN CTL:: seq: 0x187e chd: 0x001e ctl: 0x0000 inc: 0x00000000 nfb: 0x0001 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 （0x7ffffffe） uba: 0x02000010.187e.18 scn: 0x0000.00ae9095 TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x11d2 0xffff 0x0000.00ae9241 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531961 0x01 9 0x00 0x11cf 0x001a 0x0000.00ae90f1 0x0200000b 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x02 9 0x00 0x11d2 0x0016 0x0000.00ae9199 0x0200001c 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x03 9 0x00 0x11d4 0x0007 0x0000.00ae91f4 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531756 0x04 9 0x00 0x11cd 0x0006 0x0000.00ae90af 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1402531591 0x05 9 0x00 0x11d0 0x000e 0x0000.00ae915a 0x02000017 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x06 9 0x00 0x11d2 0x0019 0x0000.00ae90b2 0x0200001f 0x0000.000.00000000 0x00000001 0x00000000 1402531591 0x07 9 0x00 0x11d3 0x000d 0x0000.00ae91f6 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531756 0x08 9 0x00 0x11d3 0x0014 0x0000.00ae911b 0x0200001b 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x09 9 0x00 0x11d2 0x000a 0x0000.00ae916f 0x0200000c 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x0a 9 0x00 0x11d3 0x0012 0x0000.00ae9179 0x0200000e 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x0b 9 0x00 0x11d0 0x0017 0x0000.00ae90d2 0x02000015 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x0c 9 0x00 0x11ce 0x001b 0x0000.00ae9145 0x02000013 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x0d 9 0x00 0x11d2 0x0000 0x0000.00ae91f8 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 1402531756 0x0e 9 0x00 0x11d1 0x0009 0x0000.00ae9164 0x0200000a 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x0f 9 0x00 0x11d0 0x0003 0x0000.00ae91ae 0x02000010 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x10 9 0x00 0x11d3 0x000b 0x0000.00ae90c7 0x02000013 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x11 9 0x00 0x11d4 0x0008 0x0000.00ae9111 0x02000019 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x12 9 0x00 0x11d0 0x001f 0x0000.00ae9184 0x02000018 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x13 9 0x00 0x11cd 0x001c 0x0000.00ae909d 0x0200001d 0x0000.000.00000000 0x00000001 0x00000000 1402531591 0x14 9 0x00 0x11d1 0x0015 0x0000.00ae9126 0x0200001d 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x15 9 0x00 0x11d1 0x0020 0x0000.00ae9130 0x0200001f 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x16 9 0x00 0x11d1 0x000f 0x0000.00ae91a3 0x0200001e 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x17 9 0x00 0x11d1 0x0021 0x0000.00ae90dc 0x02000016 0x0000.000.00000000 0x00000002 0x00000000 1402531591 0x18 9 0x00 0x11d0 0x0011 0x0000.00ae9106 0x0200000f 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x19 9 0x00 0x11d2 0x0010 0x0000.00ae90bd 0x02000011 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x1a 9 0x00 0x11d1 0x0018 0x0000.00ae90fc 0x0200000d 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x1b 9 0x00 0x11d3 0x0005 0x0000.00ae914f 0x02000015 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x1c 9 0x00 0x11d0 0x001d 0x0000.00ae90a3 0x0200001d 0x0000.000.00000000 0x00000001 0x00000000 1402531591 0x1d 9 0x00 0x11d1 0x0004 0x0000.00ae90ad 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1402531591 0x1e 9 0x00 0x11d1 0x0013 0x0000.00ae909b 0x0200001c 0x0000.000.00000000 0x00000001 0x00000000 1402531591 0x1f 9 0x00 0x11d0 0x0002 0x0000.00ae918e 0x0200001a 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x20 9 0x00 0x11d2 0x000c 0x0000.00ae913b 0x02000011 0x0000.000.00000000 0x00000003 0x00000000 1402531591 0x21 9 0x00 0x11d0 0x0001 0x0000.00ae90e7 0x02000009 0x0000.000.00000000 0x00000002 0x00000000 1402531591 update scott.tabnow1 set username="XS$NULL" where user_id=2147483638; select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN,to_char（START_SCNB,"xxxxxxxx"） start_scn from v$transaction; XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN START_SCN---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- 2 30 4562 8 16 25 6270 ae9241 alter system flush buffer_cache; alter system dump datafile 8 block 16; ---data block dump的结果中摘录了Rec#0x19内容：*-----------------------------* Rec #0x19 slt: 0x1e objn: 90344（0x000160e8） objd: 90344 tblspc: 8（0x00000008）* Layer: 11 （Row） opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095 prv tx scn: 0x0000.00ae909btxn start scn: scn: 0x0000.00ae90b2 logon user: 0 prev brb: 33554460 prev bcl: 0KDO undo record:KTB Redoop: 0x04 ver: 0x01compat bit: 4 （post-11） padding: 1op: L itl: xid: 0x0002.004.0000111f uba: 0x0200001a.177b.09 flg: C--- lkc: 0 scn: 0x0000.00ad9579KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x010006f3 hdba: 0x010006f2itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0（0x0） flag: 0x2c lock: 0 ckix: 0ncol: 3 nnew: 1 size: 2col 0: [ 9] 58 53 24 4e 55 4c 4c 4c 4c --将58 53 24 4e 55 4c 4c 4c 4c转换为varchar2，得到修改前的拷贝select utl_raw.cast_to_varchar2（replace（"58 53 24 4e 55 4c 4c 4c 4c"," "）） from dual;UTL_RAW.CAST_TO_VARCHAR2（REPLACE（"5853244E554C4C4C4C",""））--------------------------------------------------------------------------------XS$NULLLL alter system dump undo header "_SYSSMU2$"; --下面是undo header中有关事务控制和事务表的dump内容，slot 0x1e处于活动状态，0x02000010即8/16 TRN CTL:: seq: 0x187e chd: 0x0009 ctl: 0x000e inc: 0x00000000 nfb: 0x0001 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 （0x7ffffffe） uba: 0x02000014.187e.13 scn: 0x0000.00ae92e6index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x11d4 0x0013 0x0000.00ae92f6 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x01 9 0x00 0x11d2 0x001a 0x0000.00ae9303 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x02 9 0x00 0x11d4 0x0016 0x0000.00ae92ed 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x03 9 0x00 0x11d6 0x0007 0x0000.00ae92f2 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x04 9 0x00 0x11d0 0x0006 0x0000.00ae92fa 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x05 9 0x00 0x11d3 0x000e 0x0000.00ae9310 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x06 9 0x00 0x11d5 0x0019 0x0000.00ae92fc 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x07 9 0x00 0x11d5 0x000d 0x0000.00ae92f4 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x08 9 0x00 0x11d6 0x0014 0x0000.00ae9308 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x09 9 0x00 0x11d4 0x000a 0x0000.00ae92e7 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x0a 9 0x00 0x11d5 0x0012 0x0000.00ae92e9 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x0b 9 0x00 0x11d3 0x0017 0x0000.00ae9300 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x0c 9 0x00 0x11d1 0x001b 0x0000.00ae930e 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x0d 9 0x00 0x11d4 0x0000 0x0000.00ae92f5 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x0e 9 0x00 0x11d4 0xffff 0x0000.00ae9311 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x0f 9 0x00 0x11d2 0x0003 0x0000.00ae92f0 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x10 9 0x00 0x11d6 0x000b 0x0000.00ae92ff 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x11 9 0x00 0x11d7 0x0008 0x0000.00ae9307 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x12 9 0x00 0x11d2 0x001f 0x0000.00ae92eb 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x13 9 0x00 0x11d0 0x001c 0x0000.00ae92f7 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x14 9 0x00 0x11d4 0x0015 0x0000.00ae930a 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x15 9 0x00 0x11d4 0x0020 0x0000.00ae930b 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x16 9 0x00 0x11d3 0x000f 0x0000.00ae92ee 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x17 9 0x00 0x11d4 0x0021 0x0000.00ae9301 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x18 9 0x00 0x11d3 0x0011 0x0000.00ae9306 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x19 9 0x00 0x11d5 0x0010 0x0000.00ae92fe 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x1a 9 0x00 0x11d4 0x0018 0x0000.00ae9304 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x1b 9 0x00 0x11d6 0x0005 0x0000.00ae930f 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x1c 9 0x00 0x11d3 0x001d 0x0000.00ae92f8 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x1d 9 0x00 0x11d4 0x0004 0x0000.00ae92f9 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x1e 10 0x80 0x11d2 0x0002 0x0000.00ae9241 0x02000010 0x0000.000.00000000 0x00000001 0x00000000 0 0x1f 9 0x00 0x11d2 0x0002 0x0000.00ae92ec 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x20 9 0x00 0x11d5 0x000c 0x0000.00ae930c 0x02000014 0x0000.000.00000000 0x00000001 0x00000000 1402532191 0x21 9 0x00 0x11d3 0x0001 0x0000.00ae9302 0x02000013 0x0000.000.00000000 0x00000001 0x00000000 1402532191 ///////////////session 2://///////////##用scott用户登陆，注意这里不能用SYS用户登录，否则set transaction read only不起作用，记录下snapshot SCN的近似值sqlplus scott/sdfg0987 select "scn before read only: "||to_char（timestamp_to_scn（sysdate）,"xxxxxxxx"） from dual;"SCNBEFOREREADONLY:"||TO_CHAR（T-------------------------------scn before read only: ae931d set transaction read only; ##记录当前会话的统计信息，system.clean_stat里存放了与本次测试有关的统计select st.name,my.value from system.clean_stat st,v$mystat my where my.statistic#=st.statistic#; NAME VALUE---------------------------------------------------------------- ----------commit cleanouts 0commit cleanouts successfully completed 0redo KB read for transport 0file io wait time 27395gc cr blocks served 0transaction tables consistent reads - undo records applied 0transaction tables consistent read rollbacks 0cleanouts only - consistent read gets 0cleanouts and rollbacks - consistent read gets 0immediate （CR） block cleanout applications 0deferred （CURRENT） block cleanout applications 0 ///////////////session 1://///////////commit; alter system flush buffer_cache; alter system dump datafile 4 block 1779;--摘录data block dump结果中Itl有关的部分，事务使用0x02槽位，xid、uba与undo header 0x1e事务槽中的值匹配，这里很明显这里发生了delayed block clean out Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0002.000.000011d2 0x02000010.187e.18 C--- 0 scn 0x0000.00ae92410x02 0x0002.01e.000011d2 0x02000010.187e.19 ---- 1 fsc 0x0002.000000000x03 0x0002.017.00001139 0x02000011.1788.1f C--- 0 scn 0x0000.00adb1b6 ///////////////session 3://///////////##db_block_size=8k的情况下，undo segment中有34个slot，执行下面的存储过程完成340次的update，平均每个slot将被覆盖10次@proc1.sql ##执行后输出历次所使用的xid，uba信息--第1轮覆盖XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,31,4563,8,20,27,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,2,4565,8,20,28,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,22,4564,8,20,29,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,15,4563,8,20,30,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,3,4567,8,20,31,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,7,4566,8,20,32,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,13,4565,8,20,33,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,0,4565,8,20,34,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,19,4561,8,20,35,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,28,4564,8,20,36,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,29,4565,8,20,37,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,4,4561,8,20,38,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,6,4566,8,20,39,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,25,4566,8,20,40,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,16,4567,8,20,41,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,11,4564,8,20,42,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,23,4565,8,20,43,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,33,4564,8,20,44,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,1,4563,8,20,45,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,26,4565,8,20,46,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,24,4564,8,20,47,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,17,4568,8,16,26,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,8,4567,8,16,27,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,20,4565,8,16,28,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,21,4565,8,16,29,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,32,4566,8,16,30,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,12,4562,8,16,31,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,27,4567,8,16,32,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,5,4564,8,16,33,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,14,4565,8,16,34,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,30,4563,8,16,35,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,9,4566,8,16,36,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,10,4567,8,21,1,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,18,4564,8,21,2,6270。。。。省略了部分--第10轮覆盖XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :307 2,31,4572,8,14,3,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :308 2,2,4574,8,14,4,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :309 2,22,4573,8,14,5,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :310 2,15,4572,8,14,6,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :311 2,3,4576,8,14,7,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :312 2,7,4575,8,14,8,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :313 2,13,4574,8,14,9,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :314 2,0,4574,8,14,10,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :315 2,19,4570,8,14,11,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :316 2,28,4573,8,14,12,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :317 2,29,4574,8,14,13,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :318 2,4,4570,8,14,14,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :319 2,6,4575,8,14,15,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :320 2,25,4575,8,14,16,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :321 2,16,4576,8,14,17,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :322 2,11,4573,8,14,18,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :323 2,23,4574,8,14,19,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :324 2,33,4573,8,14,20,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :325 2,1,4572,8,14,21,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :326 2,26,4574,8,14,22,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :327 2,24,4573,8,14,23,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :328 2,17,4577,8,14,24,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :329 2,8,4576,8,14,25,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :330 2,20,4574,8,14,26,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :331 2,21,4574,8,14,27,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :332 2,32,4575,8,14,28,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :333 2,12,4571,8,14,29,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :334 2,27,4576,8,14,30,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :335 2,5,4573,8,14,31,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :336 2,14,4574,8,14,32,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :337 2,30,4572,8,14,33,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :338 2,9,4575,8,14,34,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :339 2,10,4576,8,15,1,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :340 2,18,4573,8,15,2,6271 alter system flush buffer_cache; alter system dump undo header "_SYSSMU2$";--undo header dump结果，发现slot：0x1e 的wrap#从0x11d2->0x11dc，的确覆盖了10次，control scn：00ae96dc远大于 Session 2查询开始时的scn：ae931d，也预示着Session 2的查询将触发undo事务表回滚 TRN CTL:: seq: 0x187f chd: 0x001f ctl: 0x0012 inc: 0x00000000 nfb: 0x0001 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 （0x7ffffffe） uba: 0x0200000f.187f.02 scn: 0x0000.00ae96dc TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x11de 0x0013 0x0000.00ae96f4 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x01 9 0x00 0x11dc 0x001a 0x0000.00ae9715 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x02 9 0x00 0x11de 0x0016 0x0000.00ae96e2 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x03 9 0x00 0x11e0 0x0007 0x0000.00ae96eb 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x04 9 0x00 0x11da 0x0006 0x0000.00ae9700 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x05 9 0x00 0x11dd 0x000e 0x0000.00ae9733 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x06 9 0x00 0x11df 0x0019 0x0000.00ae9703 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x07 9 0x00 0x11df 0x000d 0x0000.00ae96ee 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x08 9 0x00 0x11e0 0x0014 0x0000.00ae9721 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x09 9 0x00 0x11df 0x000a 0x0000.00ae973c 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x0a 9 0x00 0x11e0 0x0012 0x0000.00ae973f 0x0200000f 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x0b 9 0x00 0x11dd 0x0017 0x0000.00ae970c 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x0c 9 0x00 0x11db 0x001b 0x0000.00ae972d 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x0d 9 0x00 0x11de 0x0000 0x0000.00ae96f1 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x0e 9 0x00 0x11de 0x001e 0x0000.00ae9736 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x0f 9 0x00 0x11dc 0x0003 0x0000.00ae96e8 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x10 9 0x00 0x11e0 0x000b 0x0000.00ae9709 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x11 9 0x00 0x11e1 0x0008 0x0000.00ae971e 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x12 9 0x00 0x11dd 0xffff 0x0000.00ae9742 0x0200000f 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x13 9 0x00 0x11da 0x001c 0x0000.00ae96f7 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x14 9 0x00 0x11de 0x0015 0x0000.00ae9724 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x15 9 0x00 0x11de 0x0020 0x0000.00ae9727 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x16 9 0x00 0x11dd 0x000f 0x0000.00ae96e5 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x17 9 0x00 0x11de 0x0021 0x0000.00ae970f 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x18 9 0x00 0x11dd 0x0011 0x0000.00ae971b 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x19 9 0x00 0x11df 0x0010 0x0000.00ae9706 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x1a 9 0x00 0x11de 0x0018 0x0000.00ae9718 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x1b 9 0x00 0x11e0 0x0005 0x0000.00ae9730 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x1c 9 0x00 0x11dd 0x001d 0x0000.00ae96fa 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x1d 9 0x00 0x11de 0x0004 0x0000.00ae96fd 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x1e 9 0x00 0x11dc 0x0009 0x0000.00ae9739 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x1f 9 0x00 0x11dc 0x0002 0x0000.00ae96df 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x20 9 0x00 0x11df 0x000c 0x0000.00ae972a 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 0x21 9 0x00 0x11dd 0x0001 0x0000.00ae9712 0x0200000e 0x0000.000.00000000 0x00000001 0x00000000 1402532364 ##dump 所有的undo block，以便后面作分析alter system dump datafile 8 block min 9 block max 31; ///////////////session 2://///////////##遍历scott.tabnow1表，查到了session 1修改前的拷贝select * from scott.tabnow1;USERNAME USER_ID CREATED------------------------------ ---------- ------------XS$NULLLL 2147483638 21-OCT-11NEWUSER 84 12-MAR-14SCOTTTTT 83 21-OCT-11OWBSYS_AUDIT 82 21-OCT-11OWBSYS 78 21-OCT-11APEX 77 21-OCT-11APEX_PUBLIC 75 21-OCT-11FLOWS_FILE 74 21-OCT-11MGMT_VIEW 73 21-OCT-11DDD 34 28-MAY-14 ##会话中事务表一致性读相关的统计值增加select st.name,my.value from system.clean_stat st,v$mystat my where my.statistic#=st.statistic#;NAME VALUE---------------------------------------------------------------- ----------commit cleanouts 0commit cleanouts successfully completed 0redo KB read for transport 0file io wait time 40974gc cr blocks served 0transaction tables consistent reads - undo records applied 310transaction tables consistent read rollbacks 1cleanouts only - consistent read gets 0cleanouts and rollbacks - consistent read gets 1immediate （CR） block cleanout applications 1deferred （CURRENT） block cleanout applications 0 针对上述测试结果的分析：Undo header 事务表中的每个slot被覆盖多达10次，session 2还能正常返回scott.tabnow1表修改前的记录，就是利用了undo事务表的回滚机制，为了实现这个回滚：当新的事务开始使用某个undo事务表槽之前会将事务表TRN CTL部分的scn与uba两个值作为ctl max scn、uba字段保存到新事务所拥有的undo block里，也会将这个事务表槽中的scn与dba两个值作为prv tx scn、prev brb字段保存到新事务所拥有的undo block里，例如session 1 中事务使用的uba为8/16 Rec #0x19：*-----------------------------* Rec #0x19 slt: 0x1e objn: 90344（0x000160e8） objd: 90344 tblspc: 8（0x00000008）* Layer: 11 （Row） opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095 prv tx scn: 0x0000.00ae909btxn start scn: scn: 0x0000.00ae90b2 logon user: 0 prev brb: 33554460 prev bcl: 0KDO undo record:KTB Redoop: 0x04 ver: 0x01compat bit: 4 （post-11） padding: 1op: L itl: xid: 0x0002.004.0000111f uba: 0x0200001a.177b.09 flg: C--- lkc: 0 scn: 0x0000.00ad9579KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x010006f3 hdba: 0x010006f2itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0（0x0） flag: 0x2c lock: 0 ckix: 0ncol: 3 nnew: 1 size: 2col 0: [ 9] 58 53 24 4e 55 4c 4c 4c 4c 上述dump中uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095正是来自于事务开始前undo header里TRN CTL部分所包含的uba: 0x02000010.187e.18 scn: 0x0000.00ae9095，而prv tx scn：0x0000.00ae909b 和prev brb：33554460 则分别等于事务开始前slot 0x1e 所对应的scn：00ae909b和dba：0x0200001c（转换成10进制就是33554460）。只有事务的首条undo记录才会保留上一版本的undo信息。将相关的undo记录串起来就可以完整的回放事务表所发生过的变更，下面较为直观的方式说明一下undo 记录间的指向关系，还是以proc1.sql里前三个事务为例，它们所使用的xid，uba如下：XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN : 2,31,4563,8,20,27,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN : 2,2,4565,8,20,28,6270XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN : 2,22,4564,8,20,29,6270 Undo record的记录是后修改的指向先修改的，所以依次dump block 8/20中 Rec #0x1d、Rec #0x1c、 Rec #0x1b的记录，仅将相关的内容摘录下来：*-----------------------------* Rec #0x1d slt: 0x16 objn: 89731（0x00015e83） objd: 89731 tblspc: 8（0x00000008）* Layer: 11 （Row） opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x02000014.187e.1c ctl max scn: 0x0000.00ae92ed prv tx scn: 0x0000.00ae92eetxn start scn: scn: 0x0000.00ae9357 logon user: 0 prev brb: 33554451 prev bcl: 0 *-----------------------------* Rec #0x1c slt: 0x02 objn: 89731（0x00015e83） objd: 89731 tblspc: 8（0x00000008）* Layer: 11 （Row） opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x02000014.187e.1b ctl max scn: 0x0000.00ae92ec prv tx scn: 0x0000.00ae92edtxn start scn: scn: 0x0000.00ae9354 logon user: 0 prev brb: 33554451 prev bcl: 0 *-----------------------------* Rec #0x1b slt: 0x1f objn: 89731（0x00015e83） objd: 89731 tblspc: 8（0x00000008）* Layer: 11 （Row） opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x02000014.187e.18 ctl max scn: 0x0000.00ae92eb prv tx scn: 0x0000.00ae92ectxn start scn: scn: 0x0000.00000000 logon user: 0 prev brb: 33554451 prev bcl: 0 通过这三个undo Record能够映射得到前一时刻的undo事务表，假设T3>T2>T1>T0，以下图示能够直观的展现这一映射关系：
上面的图能够帮助我们理解oracle是如何实现事务表的回滚，只要undo block不被覆盖，就能回滚得到足够旧的事务表。具备了上述知识点后，我们分析一下session 2是如何成功遍历scott.tabnow1表的：（1） Session 2的select语句执行时的snapshot scn近似等于ae931d（2）访问rdba：4/1779 时发现Itl列表里slot 0x02还处于活动状态，且没有commit scn，于是就到xid所指向的undo 事务表进一步查询事务是否提交，slot 0x1e的state=9表示事务已经提交，但提交时的wrap#值为0x11dc远大于0x11d2，且undo 事务表头的control scn：ae96dc远大于snapshot scn: ae931d（3） Oracle判断事务表槽已slot 0x1e已经被覆盖，需要回滚事务表，回滚的起点是8/14 Rec #0x21，这个Record是proc1.sql中第337个transaction所使用的undo record，因为正是这个transaction完成了对undo slot 0x1e的最后一次覆盖（4）接下来将如上图所示，oracle一步一步的对事务表进行回滚，每重构出一个事务表的版本后，将事务表头部的control scn和snapshot scn进行比较，如果snapshot scn<control scn则继续进行回滚，直到snapshot scn>=control scn而且wrap#的值等于0x11d2，事务表才回滚完毕，从undo record ：8/16 Rec #0x19里读出修改前的值：select utl_raw.cast_to_varchar2（replace（"58 53 24 4e 55 4c 4c 4c 4c"," "）） from dual;UTL_RAW.CAST_TO_VARCHAR2（REPLACE（"5853244E554C4C4C4C",""））--------------------------------------------------------------------------------XS$NULLLL Session 2实现一致性读的两个统计信息：NAME VALUE---------------------------------------------------------------- ----------transaction tables consistent reads - undo records applied 310transaction tables consistent read rollbacks 1 transaction tables consistent reads - undo records applied 310表示为了回滚事务表应用了多少undo records，这里为何是310，暂无法证实，我本来认为应该是338的transaction tables consistent read rollbacks 1表示事务表发生了一次回滚的动作之前我们曾经在session 3里通过alter system dump datafile 8 block min 9 block max 31将所有的undo block dump出来，现在回过头来看一下dump的结果在undo Record 8/17 Rec #0x1里找到了uba: 0x02000010.187e.19，即存放修改前拷贝的undo record：8/16 Rec #0x19，说明这个undo record还在undo chain里*-----------------------------* Rec #0x1 slt: 0x13 objn: 270（0x0000010e） objd: 268 tblspc: 1（0x00000001）* Layer: 11 （Row） opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x02000010.187e.19 ctl max scn: 0x0000.00ae909b prv tx scn: 0x0000.00ae909dtxn start scn: scn: 0x0000.00ae9294 logon user: 0 prev brb: 33554461 prev bcl: 0KDO undo record:KTB Redoop: 0x04 ver: 0x01compat bit: 4 （post-11） padding: 1op: L itl: xid: 0x0002.006.000011d2 uba: 0x0200001f.1877.01 flg: C--- lkc: 0 scn: 0x0000.00ae90b2KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00810de8 hdba: 0x00800192itli: 2 ispac: 0 maxfr: 4858tabn: 1 slot: 5（0x5） ##进一步dump 8/16 Rec #0x19的内容，可以看到修改前的内容还在，所以session 2能完成表的遍历*-----------------------------* Rec #0x19 slt: 0x1e objn: 90344（0x000160e8） objd: 90344 tblspc: 8（0x00000008）* Layer: 11 （Row） opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x02000010.187e.18 ctl max scn: 0x0000.00ae9095 prv tx scn: 0x0000.00ae909btxn start scn: scn: 0x0000.00ae90b2 logon user: 0 prev brb: 33554460 prev bcl: 0KDO undo record:KTB Redoop: 0x04 ver: 0x01compat bit: 4 （post-11） padding: 1op: L itl: xid: 0x0002.004.0000111f uba: 0x0200001a.177b.09 flg: C--- lkc: 0 scn: 0x0000.00ad9579KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x010006f3 hdba: 0x010006f2itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0（0x0） flag: 0x2c lock: 0 ckix: 0ncol: 3 nnew: 1 size: 2col 0: [ 9] 58 53 24 4e 55 4c 4c 4c 4c ///////////////session 4://///////////##对proc1.sql脚本的循环次数作略微调整后，分别在循环34次、循环68次、循环102次的情况下，继续对事务槽作覆盖，平均每个事务槽再被覆盖6次，执行调整后的proc1.sql，输出如下：--proc1.sql调整为循环34次时的输出：XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,2,4575,8,15,17,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,22,4574,8,15,18,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,15,4573,8,15,19,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,3,4577,8,15,20,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,7,4576,8,15,21,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,13,4575,8,15,22,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,0,4575,8,15,23,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,19,4571,8,15,24,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,28,4574,8,15,25,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,29,4575,8,15,26,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,4,4571,8,15,27,6271XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,6,4576,8,24,1,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,25,4576,8,24,2,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,16,4577,8,24,3,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,11,4574,8,24,4,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,23,4575,8,24,5,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,33,4574,8,24,6,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,1,4573,8,24,7,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,26,4575,8,24,8,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,24,4574,8,24,9,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,17,4578,8,24,10,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,8,4577,8,24,11,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,20,4575,8,24,12,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,21,4575,8,24,13,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,32,4576,8,24,14,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,12,4572,8,24,15,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,27,4577,8,24,16,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,5,4574,8,24,17,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,14,4575,8,24,18,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,30,4573,8,24,19,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,9,4576,8,24,20,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,10,4577,8,24,21,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,18,4574,8,24,22,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,31,4574,8,24,23,6272 --proc1.sql调整为循环68次时的输出：XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,21,4576,8,26,1,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,32,4577,8,26,2,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,12,4573,8,26,3,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,27,4578,8,26,4,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,5,4575,8,26,5,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,14,4576,8,26,6,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,30,4574,8,26,7,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,9,4577,8,26,8,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,10,4578,8,26,9,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,18,4575,8,26,10,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,31,4575,8,26,11,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,2,4577,8,26,12,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,22,4576,8,26,13,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,15,4575,8,26,14,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,3,4579,8,26,15,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,7,4578,8,26,16,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,13,4577,8,26,17,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,0,4577,8,26,18,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,19,4573,8,26,19,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,28,4576,8,26,20,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,29,4577,8,26,21,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,4,4573,8,26,22,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,6,4578,8,26,23,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,25,4578,8,26,24,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,16,4579,8,26,25,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,11,4576,8,26,26,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :27 2,23,4577,8,26,27,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :28 2,33,4576,8,26,28,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :29 2,1,4575,8,26,29,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :30 2,26,4577,8,26,30,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :31 2,24,4576,8,26,31,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :32 2,17,4580,8,26,32,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :33 2,8,4579,8,26,33,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :34 2,20,4577,8,26,34,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :35 2,21,4577,8,27,1,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :36 2,32,4578,8,27,2,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :37 2,12,4574,8,27,3,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :38 2,27,4579,8,27,4,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :39 2,5,4576,8,27,5,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :40 2,14,4577,8,27,6,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :41 2,30,4575,8,27,7,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :42 2,9,4578,8,27,8,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :43 2,10,4579,8,27,9,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :44 2,18,4576,8,27,10,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :45 2,31,4576,8,27,11,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :46 2,2,4578,8,27,12,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :47 2,22,4577,8,27,13,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :48 2,15,4576,8,27,14,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :49 2,3,4580,8,27,15,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :50 2,7,4579,8,27,16,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :51 2,13,4578,8,27,17,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :52 2,0,4578,8,27,18,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :53 2,19,4574,8,27,19,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :54 2,28,4577,8,27,20,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :55 2,29,4578,8,27,21,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :56 2,4,4574,8,27,22,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :57 2,6,4579,8,27,23,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :58 2,25,4579,8,27,24,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :59 2,16,4580,8,27,25,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :60 2,11,4577,8,27,26,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :61 2,23,4578,8,27,27,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :62 2,33,4577,8,27,28,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :63 2,1,4576,8,27,29,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :64 2,26,4578,8,27,30,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :65 2,24,4577,8,27,31,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :66 2,17,4581,8,27,32,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :67 2,8,4580,8,27,33,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :68 2,20,4578,8,27,34,6272 --proc1.sql调整为循环102次时的输出：XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :1 2,21,4578,8,28,1,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :2 2,32,4579,8,28,2,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :3 2,12,4575,8,28,3,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :4 2,27,4580,8,28,4,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :5 2,5,4577,8,28,5,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :6 2,14,4578,8,28,6,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :7 2,30,4576,8,28,7,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :8 2,9,4579,8,28,8,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :9 2,10,4580,8,28,9,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :10 2,18,4577,8,28,10,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :11 2,31,4577,8,28,11,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :12 2,2,4579,8,28,12,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :13 2,22,4578,8,28,13,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :14 2,15,4577,8,28,14,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :15 2,3,4581,8,28,15,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :16 2,7,4580,8,28,16,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :17 2,13,4579,8,28,17,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :18 2,0,4579,8,28,18,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :19 2,19,4575,8,28,19,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :20 2,28,4578,8,28,20,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :21 2,29,4579,8,28,21,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :22 2,4,4575,8,28,22,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :23 2,6,4580,8,28,23,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :24 2,25,4580,8,28,24,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :25 2,16,4581,8,28,25,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN :26 2,11,4578,8,28,26,6272XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,UBASQN&