[20210604]索引分裂與 itl ktbitflg.txt
[20210604]索引分裂與 itl ktbitflg.txt
--//前幾天在library cache 轉儲的測試中,重新複習Oracle Core Essential Internals for DBAs and Developers的中文版,無意中發
--//現如下:
--//轉抄 英文版PDF文件內容 P41:
Table 3-2. Columns in the Interested Transaction List
-----------------------------------------------------------------------------------------------------
Column Description
-----------------------------------------------------------------------------------------------------
...
Flag Bit flag identifying the apparent state of this transaction:
----: active (or "never existed" if every field in the Xid is zero).
--U-: Upper bound commit (also set during "fast commit").
C---: Committed and cleaned out (all associated lock bytes have been reset to zero).
-B--: May be relevant to the recursive transactions for index block splits. I have seen
comments that this flag means the UBA will point to a record holding the previous
content of the ITL entry, but I have not managed to confirm this.
---T: I have seen comments that this means the transaction was active during block
cleanout, but I have not managed to confirm this.
-----------------------------------------------------------------------------------------------------
--//裡面提到-B--標識與索引分裂有關.裡面提到了recursive transactions,既然是遞規事務表示不會回滾的,應該檢視索引分裂時可以
--//看到這個標識.自己測試看看,另外後面的內容實在不好理解:
--//I have seen comments that this flag means the UBA will point to a record holding the previous content of the ITL
--//entry, but I have not managed to confirm this.
--//我仔細看了中文的翻譯,感覺不好理解.感覺應該翻譯成:
--//我曾看過評論這個標記意味UBA會指向持有先前內容的ITL項.但我無法確認.
--//總之既然相關索引分裂,應該可以看到這個-B--標識.
--//金山詞霸的翻譯:
--//可能與索引塊分割的遞迴事務相關。我看到評論，這個標誌意味著UBA將指向儲存ITL條目的之前內容的記錄，但我沒有確認這一點。
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正好索引根節點滿了,繼續插入索引會發生分裂.
--//session 2:
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> @ treedump.sql i_t1_id
OBJECT_ID
----------
92871
Session altered.
--//跟蹤內容如下：
*** 2021-06-04 08:54:57.989
leaf: 0x10002b3 16777907 (0: nrow: 540 rrow: 540)
----- end tree dump
0x10002b3 = set dba 4,691 = alter system dump datafile 4 block 691 = 16777907
SCOTT@book> alter system dump datafile 4 block 691;
System altered.
--//跟蹤內容如下：
Block header dump: 0x010002b3
Object id on Block? Y
seg/obj: 0x16ac7 csc: 0x03.17eafa32 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 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x000a.006.00009010 0x00c00425.1d8f.0a --U- 540 fsc 0x0000.17eafa42
--//session 1:
SCOTT@book> insert into t1 values (400,lpad('b',100,'b'));
1 row created.
--//注意不要提交!!
--//session 2:
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0001.trc
SCOTT@book> @ treedump.sql i_t1_id
OBJECT_ID
----------
92871
Session altered.
--//跟蹤內容如下：
----- begin tree dump
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
leaf: 0x10002b5 16777909 (-1: nrow: 279 rrow: 279)
leaf: 0x10002b6 16777910 (0: nrow: 262 rrow: 262)
----- end tree dump
--//可以確定索引已經發生了分裂.
3.轉儲塊分析：
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system checkpoint ;
System altered.
--//0x10002b3 = set dba 4,691 = alter system dump datafile 4 block 691 = 16777907
--//0x10002b5 = set dba 4,693 = alter system dump datafile 4 block 693 = 16777909
--//0x10002b6 = set dba 4,694 = alter system dump datafile 4 block 694 = 16777910
--//索引鍵值400應該插入dba=0x10002b6.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0002.trc
SCOTT@book> alter system dump datafile 4 block 691;
System altered.
--//跟蹤內容如下：
Block header dump: 0x010002b3
Object id on Block? Y
seg/obj: 0x16ac7 csc: 0x03.17eafb00 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.016.00001020 0x00c012d7.0776.01 -BU- 1 fsc 0x0000.17eafb02
--//確實看到了B標識，注意還有一個U標識，表示已經提交。
--//奇怪一點原來2個ITL槽變成1個ITL槽了。
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0003.trc
SCOTT@book> alter system dump datafile 4 block 694;
System altered.
--//跟蹤內容如下：
Block header dump: 0x010002b6
Object id on Block? Y
seg/obj: 0x16ac7 csc: 0x03.17eafb01 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.016.00001020 0x00c012d8.0777.02 -B-- 1 fsc 0x0000.00000000
0x02 0x000a.000.0000900c 0x00c00424.1d8f.1c ---- 1 fsc 0x0000.00000000
--//注意看ITL第1個槽用於分裂事務的，注意看XID=0x0009.016.00001020,與塊0x010002b3一致。也就是同一個事務。
--//再看UBA＝0x00c012d8.0777.02，而前面的塊0x010002b3，記錄的UBA=0x00c012d7.0776.01.
--//按照順序號0x776，根節點事務在前，葉子節點順序號0x777在後。使用的undo塊不一樣。
--//0x00c012d7 = set dba 3,4823 = alter system dump datafile 3 block 4823 = 12587735
--//0x00c012d8 = set dba 3,4824 = alter system dump datafile 3 block 4824 = 12587736
--//session 1:
SCOTT@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.0.36876
--//36876 = 0x900c,可以發現ITL第2個槽記錄的事務XID一致。XID=0x000a.000.0000900c。
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 0 36876; 10 0 36876 3 1060 7567 28 ACTIVE 1 2 0A0000000C900000 00000000857D0FA8 2021-06-04 08:57:15 3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1060;
4.回滾測試看看：
SCOTT@book> rollback ;
Rollback complete.
SCOTT@book> @ tix
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0004.trc
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system dump datafile 4 block 691;
System altered.
--//跟蹤內容如下：
Block header dump: 0x010002b3
Object id on Block? Y
seg/obj: 0x16ac7 csc: 0x03.17eafb00 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.016.00001020 0x00c012d7.0776.01 -BU- 1 fsc 0x0000.17eafb02
SCOTT@book> alter system dump datafile 4 block 694;
System altered.
--//跟蹤內容如下：
Block header dump: 0x010002b6
Object id on Block? Y
seg/obj: 0x16ac7 csc: 0x03.17eafb01 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.016.00001020 0x00c012d8.0777.02 CBU- 0 scn 0x0003.17eb0156
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--//事務取消，最後標識是提交C。第2個ITL槽全部變為0.塊分裂已經發生並提交，這個遞迴事務不會rollback。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2775396/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210603]如何跟蹤索引分裂.txt索引
- 資料庫索引分裂 問題分析資料庫索引
- [20211105]索引分裂塊清除日誌增加(唯一索引).txt索引
- Oracle索引塊分裂split資訊彙總Oracle索引
- [20211105]索引分裂 塊清除 日誌增加.txt索引
- [20211108]索引分裂塊清除日誌增加(唯一索引)2.txt索引
- ORACLE 12c索引分裂引起的會話夯Oracle索引會話
- OceanBase子表分裂與合併
- PG 12-2 B-Tree 索引 分析 分裂 level = 1索引
- 【ASK_ORACLE】關於Oracle索引分裂你需要知道的Oracle索引
- [20180327]行遷移與ITL浪費.txt
- [20210604]如何看到遞迴事務.txt遞迴
- 遭遇ITL死鎖
- 【ASK_ORACLE】由於索引分裂而產生的效能問題的解決方案Oracle索引
- [20180402]行連結行遷移與ITL槽6.txt
- 索引與null（二）：組合索引索引Null
- 索引與null（一）：單列索引索引Null
- in、exists與索引索引
- order by與索引索引
- [20220223]Index ITL Limit.txtIndexMIT
- 記一次索引分裂造成rac業務短暫業務不可用的情況索引
- [20231027]Index ITL Limit 2.txtIndexMIT
- [20231027]Index ITL Limit 3.txtIndexMIT
- [20231031]Index ITL Limit 4.txtIndexMIT
- [20210223]bbed itl ktbitflg 2.txt
- IPv4與IPv6：網際網路面臨分裂嗎？
- 淺析B-樹分裂
- 淺談聚簇索引與非聚簇索引索引
- enq: TX - allocate ITL entry等待事件分析ENQ事件
- 匿名類 與 索引重建索引
- 淺談MySQL的B樹索引與索引優化MySql索引優化
- MySQL 頁分裂中的鎖3MySql
- 關於enq: TX - allocate ITL entry等待事件ENQ事件
- 《PostgreSQL》 索引與最佳化SQL索引
- InnoDB索引與底層原理索引
- MongoDB慢查詢與索引MongoDB索引
- ES 文件與索引介紹索引
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index