[20210604]如何看到遞迴事務.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210604]索引分裂與 itl ktbitflg.txt索引
- 遞迴那點事遞迴
- [20180531]函式呼叫與遞迴.txt函式遞迴
- 遞迴和尾遞迴遞迴
- 快速排序【遞迴】【非遞迴】排序遞迴
- 遞迴遞迴
- 什麼是遞迴?遞迴和迴圈的異同遞迴
- go 遞迴Go遞迴
- JavaScript遞迴JavaScript遞迴
- 分而治之-遞迴遞迴
- 理解遞迴遞迴
- 遍歷二叉樹-------遞迴&非遞迴二叉樹遞迴
- [20200512]oracle的事務隔離級別.txtOracle
- [20211021]關於undo段頭事務表.txt
- 遞迴和遞推總結遞迴
- 演算法小專欄：遞迴與尾遞迴演算法遞迴
- 迭代與遞迴--你被遞迴搞暈過嗎？遞迴
- Python如何遞迴刪除空資料夾Python遞迴
- 遞迴呼叫 VS 迴圈呼叫遞迴
- 谷歌迴應 Google Maps 遭“入侵”一事：樂於看到這種創造性用途谷歌Go
- 遞迴-*快速排序遞迴排序
- 遞迴小記遞迴
- 理解遞迴 Recurtion遞迴
- C#遞迴C#遞迴
- sql server遞迴SQLServer遞迴
- Vue元件遞迴Vue元件遞迴
- SQL 遞迴思想SQL遞迴
- 遞迴函式遞迴函式
- 談談遞迴遞迴
- 遞迴問題遞迴
- 遞迴總結遞迴
- 遞迴加回溯遞迴
- [20181122]bbed人為修改事務提交標誌.txt
- ?30 秒瞭解尾遞迴和尾遞迴優化遞迴優化
- 【C++】翻轉二叉樹(遞迴、非遞迴)C++二叉樹遞迴
- kingbase SQL最佳化案例 ( union遞迴 改 cte遞迴 )SQL遞迴
- 快速排序（遞迴及非遞迴演算法原始碼）排序遞迴演算法原始碼
- 徹底理解遞迴，從遞迴的本質說起！遞迴