[20210604]如何看到遞迴事務.txt

lfree發表於2021-06-04

[20210604]如何看到遞迴事務.txt

--//上午測試索引分裂以及itl ktbitflg的標識,第一次注意到B標識,關於索引分裂的遞迴事務的。
--//連結:http://blog.itpub.net/267265/viewspace-2775396/=>[20210604]索引分裂與 itl ktbitflg.txt

--//因為遞迴事務的特殊性,查詢v$tranaction無法看到遞迴事務。有什麼方法能看到這個遞迴事務呢?

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
--//session 1:
drop table t1 purge;
create table t1 ( id number ,name varchar2(100)) ;
create index i_t1_id on t1(id);
--//alter session set events '10224 trace name context forever,level 1';
insert into t1 select rownum,lpad('a',100,rownum) from dual connect by level<=540;
commit;
--//注:插入540正好索引根節點滿了,繼續插入索引會發生分裂.

SCOTT@book> @ scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
             13286182066 2021-06-04 09:49:34


SCOTT@book> insert into t1 values (400,lpad('b',100,'b'));
1 row created.

SCOTT@book> @ scn
GET_SYSTEM_CHANGE_NUMBER SYSDATE
------------------------ -------------------
             13286182097 2021-06-04 09:50:00
            
--//我開始以為使用 as of scn 模式可以看到,發現不行,它僅僅訪問底層的 X$KTCXB ,不支援這種功能,放棄!!

3.換一個思路,設定斷點呢?
--//多次嘗試,發現設定在ktucmt表示ktucmt - kernel transaction undo commit transaction ,可以查詢連結:
--// orafun.info ,發現一個小小的缺點不能根據描述反查。比如我查詢
--//重複上面建表過程略:
--//session 1:
SCOTT@book> @ spid
         SID      SERIAL# PROCESS                  SERVER    SPID       PID    P_SERIAL# C50
------------ ------------ ------------------------ --------- ------ ------- ------------ --------------------------------------------------
         295            5 44886                    DEDICATED 44887       21            3 alter system kill session '295,5' immediate;

--//session 2:
$ gdb -p 44887
...
(gdb) b ktucmt
Breakpoint 1 at 0xf1c6aa

--//session 1:
SCOTT@book> insert into t1 values (100,lpad('b',100,'b'));

--//掛起。

--//session 3:
SYS@book> select * from v$transaction ;
no rows selected

--//session 2:
--//不斷的ni 20
(gdb) ni 20
0x000000000186466e in opitsk ()
(gdb) ni 20
0x00000000018646b5 in opitsk ()
(gdb) ni 20

Breakpoint 1, 0x0000000000f1c6aa in ktucmt ()

--//再次出現ktucmt.

--//session 3:
SYS@book> select * from v$transaction
  2  @ prxx
==============================
ADDR                          : 0000000085AB9D20
XIDUSN                        : 9
XIDSLOT                       : 10
XIDSQN                        : 4145
UBAFIL                        : 3
UBABLK                        : 5905
UBASQN                        : 1938
UBAREC                        : 1
STATUS                        : ACTIVE
START_TIME                    : 06/04/21 10:04:02
START_SCNB                    : 401281729
START_SCNW                    : 3
START_UEXT                    : 33
START_UBAFIL                  : 3
START_UBABLK                  : 5903
START_UBASQN                  : 1938
START_UBAREC                  : 28
SES_ADDR                      : 0000000087584188
FLAG                          : 67116579
SPACE                         : NO
RECURSIVE                     : YES
NOUNDO                        : NO
PTX                           : NO
NAME                          :
PRV_XIDUSN                    : 10
PRV_XIDSLT                    : 27
PRV_XIDSQN                    : 36881
PTX_XIDUSN                    : 0
PTX_XIDSLT                    : 0
PTX_XIDSQN                    : 0
DSCN-B                        : 0
DSCN-W                        : 0
USED_UBLK                     : 2
USED_UREC                     : 2
LOG_IO                        : 23
PHY_IO                        : 0
CR_GET                        : 4
CR_CHANGE                     : 0
START_DATE                    : 2021-06-04 10:04:02
DSCN_BASE                     : 0
DSCN_WRAP                     : 0
START_SCN                     : 13286183617
DEPENDENT_SCN                 : 0
XID                           : 09000A0031100000
PRV_XID                       : 0A001B0011900000
PTX_XID                       : 0000000000000000
==============================
ADDR                          : 0000000085369CA0
XIDUSN                        : 9
XIDSLOT                       : 17
XIDSQN                        : 4142
UBAFIL                        : 0
UBABLK                        : 0
UBASQN                        : 0
UBAREC                        : 0
STATUS                        : ACTIVE
START_TIME                    : 06/04/21 10:04:02
START_SCNB                    : 401281729
START_SCNW                    : 3
START_UEXT                    : 33
START_UBAFIL                  : 3
START_UBABLK                  : 5904
START_UBASQN                  : 1938
START_UBAREC                  : 3
SES_ADDR                      : 0000000087584188
FLAG                          : 67110435
SPACE                         : NO
RECURSIVE                     : YES
NOUNDO                        : NO
PTX                           : NO
NAME                          :
PRV_XIDUSN                    : 9
PRV_XIDSLT                    : 10
PRV_XIDSQN                    : 4145
PTX_XIDUSN                    : 0
PTX_XIDSLT                    : 0
PTX_XIDSQN                    : 0
DSCN-B                        : 0
DSCN-W                        : 0
USED_UBLK                     : 1
USED_UREC                     : 1
LOG_IO                        : 9
PHY_IO                        : 0
CR_GET                        : 0
CR_CHANGE                     : 0
START_DATE                    : 2021-06-04 10:04:02
DSCN_BASE                     : 0
DSCN_WRAP                     : 0
START_SCN                     : 13286183617
DEPENDENT_SCN                 : 0
XID                           : 090011002E100000
PRV_XID                       : 09000A0031100000
PTX_XID                       : 0000000000000000
==============================
ADDR                          : 0000000084CFEC50
XIDUSN                        : 10
XIDSLOT                       : 27
XIDSQN                        : 36881
UBAFIL                        : 3
UBABLK                        : 896
UBASQN                        : 7568
UBAREC                        : 4
STATUS                        : ACTIVE
START_TIME                    : 06/04/21 10:04:02
START_SCNB                    : 401280496
START_SCNW                    : 3
START_UEXT                    : 3
START_UBAFIL                  : 3
START_UBABLK                  : 896
START_UBASQN                  : 7568
START_UBAREC                  : 4
SES_ADDR                      : 0000000087584188
FLAG                          : 3587
SPACE                         : NO
RECURSIVE                     : NO
NOUNDO                        : NO
PTX                           : NO
NAME                          :
PRV_XIDUSN                    : 0
PRV_XIDSLT                    : 0
PRV_XIDSQN                    : 0
PTX_XIDUSN                    : 0
PTX_XIDSLT                    : 0
PTX_XIDSQN                    : 0
DSCN-B                        : 0
DSCN-W                        : 0
USED_UBLK                     : 1
USED_UREC                     : 1
LOG_IO                        : 31
PHY_IO                        : 0
CR_GET                        : 5
CR_CHANGE                     : 0
START_DATE                    : 2021-06-04 10:04:02
DSCN_BASE                     : 0
DSCN_WRAP                     : 0
START_SCN                     : 13286182384
DEPENDENT_SCN                 : 0
XID                           : 0A001B0011900000
PRV_XID                       : 0000000000000000
PTX_XID                       : 0000000000000000
PL/SQL procedure successfully completed.

--//看到3條記錄。注意看RECURSIVE=yes就是遞迴事務。

SYS@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------


C70                                                                          XIDUSN      XIDSLOT       XIDSQN       UBAFIL       UBABLK       UBASQN       UBAREC STATUS              USED_UBLK    USED_UREC XID              ADDR             START_DATE                  FLAG
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 10 4145;                9           10         4145            3         5905         1938            1 ACTIVE                      2            2 09000A0031100000 0000000085AB9D20 2021-06-04 10:04:02     67116579
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 5905;

ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 17 4142;                9           17         4142            0            0            0            0 ACTIVE                      1            1 090011002E100000 0000000085369CA0 2021-06-04 10:04:02     67110435
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$';
ALTER SYSTEM DUMP DATAFILE 0 BLOCK 0;

ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881;            10           27        36881            3          896         7568            4 ACTIVE                      1            1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02         3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896;

--//繼續ni 40,直到出現Breakpoint 1, 0x0000000000f1c6aa in ktucmt 。
SYS@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------


C70                                                                          XIDUSN      XIDSLOT       XIDSQN       UBAFIL       UBABLK       UBASQN       UBAREC STATUS              USED_UBLK    USED_UREC XID              ADDR             START_DATE                  FLAG
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 10 4145;                9           10         4145            3         5906         1938            1 ACTIVE                      3            3 09000A0031100000 0000000085AB9D20 2021-06-04 10:04:02     67116579
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 5906;

ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881;            10           27        36881            3          896         7568            4 ACTIVE                      1            1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02         3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896;

--//變成2個,最後:
SYS@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------


C70                                                                          XIDUSN      XIDSLOT       XIDSQN       UBAFIL       UBABLK       UBASQN       UBAREC STATUS              USED_UBLK    USED_UREC XID              ADDR             START_DATE                  FLAG
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881;            10           27        36881            3          896         7568            4 ACTIVE                      1            1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02         3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896;

--//實際上這次操作存在2次遞迴事務。
SYS@book> alter system checkpoint ;
System altered.

SYS@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_45262_0001.trc

SYS@book> alter system dump datafile 4 block 694;
System altered.

Block header dump:  0x010002b6
 Object id on Block? Y
 seg/obj: 0x16ad1  csc: 0x03.17eb1529  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.00a.00001031  0x00c01712.0792.02  -B--    1  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

--//Xid=0x0009.00a.00001031,0x1031 = 4145 能完全對上。
--//Uba=0x00c01712.0792.02
--//0x00c01712 = set dba 3,5906 = alter system dump datafile 3 block 5906 = 12588818
--//0x0792 = 1938

SYS@book> alter system dump datafile 4 block 694;
System altered.

Block header dump:  0x010002b3
 Object id on Block? Y
 seg/obj: 0x16ad1  csc: 0x03.17eb12c1  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.00a.00001031  0x00c01711.0792.01  -BU-    1  fsc 0x0000.17eb15a9

--//0x00c01711 = set dba 3,5905 = alter system dump datafile 3 block 5905 = 12588817
--//0x0792 = 1938
--//能與遞迴事務對上。
--//如果繼續看可以看到:

SYS@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------


C70                                                                          XIDUSN      XIDSLOT       XIDSQN       UBAFIL       UBABLK       UBASQN       UBAREC STATUS              USED_UBLK    USED_UREC XID              ADDR             START_DATE                  FLAG
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 31 4144;                9           31         4144            3         5906         1938           54 ACTIVE                      1            2 09001F0030100000 0000000085AB9D20 2021-06-04 10:23:58     67116579
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 5906;

ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881;            10           27        36881            3          896         7568            4 ACTIVE                      1            1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02         3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896;

SYS@book> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 31 4144;
System altered.

*** 2021-06-04 10:29:14.043

********************************************************************************
Undo Segment:  _SYSSMU9_1650507775$ (9)
xid: 0x0009.01f.00001030
Low Blk   :   (0, 0)
High Blk  :   (35, 7)
Object Id :   ALL
Layer     :   ALL
Opcode    :   ALL
Level     :   2

+++++++++++ XID mismatch. Some records may not belong to specified transaction.
+ WARNING + Block xid: 0x0009.021.0000102e   dba (file#, block#): 3,0x00001712
+++++++++++ Input xid: 0x0009.01f.00001030

********************************************************************************
UNDO BLK:  Extent: 33   Block: 18   dba (file#, block#): 3,0x00001712
xid: 0x0009.021.0000102e  seq: 0x792 cnt: 0x37  irb: 0x37  icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70     0x02 0x1ef8     0x03 0x1e48     0x04 0x1dc8     0x05 0x1d1c
0x06 0x1c6c     0x07 0x1bbc     0x08 0x1b50     0x09 0x1ae4     0x0a 0x1a78
0x0b 0x19c8     0x0c 0x195c     0x0d 0x18f0     0x0e 0x1884     0x0f 0x17d4
0x10 0x1768     0x11 0x16fc     0x12 0x164c     0x13 0x15e0     0x14 0x1574
0x15 0x14c4     0x16 0x1454     0x17 0x13a4     0x18 0x12f4     0x19 0x1288
0x1a 0x11d8     0x1b 0x116c     0x1c 0x1100     0x1d 0x1050     0x1e 0x0fe4
0x1f 0x0f78     0x20 0x0f08     0x21 0x0e58     0x22 0x0dec     0x23 0x0d80
0x24 0x0d14     0x25 0x0c64     0x26 0x0bb4     0x27 0x0b48     0x28 0x0a98
0x29 0x09e4     0x2a 0x0934     0x2b 0x08c8     0x2c 0x085c     0x2d 0x07b0
0x2e 0x06fc     0x2f 0x064c     0x30 0x05d0     0x31 0x0564     0x32 0x04b8
0x33 0x0408     0x34 0x0334     0x35 0x02ac     0x36 0x0258     0x37 0x01bc

*-----------------------------
* Rec #0x35  slt: 0x1f  objn: 92881(0x00016ad1)  objd: 92881  tblspc: 4(0x00000004)
*       Layer:  10 (Index)   opc: 21   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c01712.0792.34 ctl max scn: 0x0003.17eb1789 prv tx scn: 0x0003.17eb178b
txn start scn: scn: 0x0003.17eb17f2 logon user: 83
 prev brb: 12588816 prev bcl: 0
index general undo (branch) operations
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0009.00a.00001031 uba: 0x00c01712.0792.01
                      flg: CBU-    lkc:  0     scn: 0x0003.17eb1789
Dump kdige : block dba :0x010002b5, seghdr dba: 0x010002b2
unlock block
(1):  01


+++++++++++ Next block not in extent map - rollback segment has been shrunk.
+ WARNING + Block dba (file#, block#): 0,0x00000000
+++++++++++

*-----------------------------
* Rec #0x36  slt: 0x1f  objn: 92881(0x00016ad1)  objd: 92881  tblspc: 4(0x00000004)
*       Layer:  10 (Index)   opc: 22   rci 0x35
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c01712.0792.35
Dump kdilk : itl=1, kdxlkflg=0x1 sdc=1 indexid=0x10002b2 block=0x010002b5
(kdxlin): insert leaf row
key :(10):  02 c2 02 06 01 00 02 b8 00 0c
--//視乎是插入索引鍵值的。c2 02 編碼對應100,後面的應該是rowid。

*************************************
Total undo blocks scanned  = 1
Total undo records scanned = 55
Total undo blocks dumped   = 1
Total undo records dumped  = 2

##Total warnings issued = 2
*************************************

4.總結:
測試有點亂。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2775406/,如需轉載,請註明出處,否則將追究法律責任。

相關文章