[20210603]如何跟蹤索引分裂.txt

[20210603]如何跟蹤索引分裂.txt

--//oracle索引分裂有兩種:50-50,90-10.一些測試需要要跟蹤索引的分裂?應該以前也做過類似測試.

1.環境:

SCOTT@book> @ &&r/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

SYS@book> select name from v$sysstat where name like '%split%' order by name;
NAME
--------------------
branch node splits
leaf node 90-10 splits
leaf node splits
queue splits
root node splits

$ oerr ora  10224
10224, 00000, "index block split/delete trace"
// *Cause:
// *Action:
--//很明顯跟蹤索引分裂使用10224事件.

2.測試:
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<=541;
commit;

--//跟蹤內容如下:
*** 2021-06-04 08:39:50.559
*** SESSION ID:(295.5) 2021-06-04 08:39:50.559
*** CLIENT ID:() 2021-06-04 08:39:50.559
*** SERVICE NAME:(SYS$USERS) 2021-06-04 08:39:50.559
*** MODULE NAME:(SQL*Plus) 2021-06-04 08:39:50.559
*** ACTION NAME:() 2021-06-04 08:39:50.559

splitting leaf,dba 0x010002b3,time 08:39:50.558
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x010002b3,time 08:39:50.559
kdisnew_bseg_srch_cbk rejecting block ,dba 0x010002b3,time 08:39:50.559
kdisnew_bseg_srch_cbk using block,dba 0x010002b5,time 08:39:50.559
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x010002b5,time 08:39:50.560
kdisnew_bseg_srch_cbk rejecting block ,dba 0x010002b5,time 08:39:50.560
kdisnew_bseg_srch_cbk using block,dba 0x010002b6,time 08:39:50.560

--//0x010002b3 = set dba 4,691 = alter system dump datafile 4 block 691 = 16777907
--//0x010002b5 = set dba 4,693 = alter system dump datafile 4 block 693 = 16777909
--//0x010002b6 = set dba 4,694 = alter system dump datafile 4 block 694 = 16777910
--//僅僅知道發生了分裂,到底在插入什麼值時發生分裂還是不知道.

SCOTT@book> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='I_T1_ID';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4          690
--//說明dba=4,691是索引的根節點.

SCOTT@book> @ treedump.sql  i_t1_id
 OBJECT_ID
----------
     92867
Session altered.

--//跟蹤檔案內容如下：
----- begin tree dump
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
   leaf: 0x10002b5 16777909 (-1: nrow: 540 rrow: 540)
   leaf: 0x10002b6 16777910 (0: nrow: 1 rrow: 1)
----- end tree dump

--//結合索引塊的儲存,想象一下索引分裂的過程.
--//首先發現dba 0x010002b3塊 -mark full ,reject block ,rejecting block dba 0x010002b3。
--//索引分裂,using block,dba 0x010002b5,發現dba 0x010002b5 塊-mark full,reject block,rejecting block dba 0x010002b5。
--//最後使用dba 0x10002b6。

3.繼續做一個50-50分裂的情況:

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;

insert into t1 values (100,lpad('b',100,'b'));
commit;

--//跟蹤內容如下:
*** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44887_0002.trc ***
splitting leaf,dba 0x010002b3,time 08:47:32.912
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x010002b3,time 08:47:32.913
kdisnew_bseg_srch_cbk rejecting block ,dba 0x010002b3,time 08:47:32.913
kdisnew_bseg_srch_cbk using block,dba 0x010002b5,time 08:47:32.914
kdisnew_bseg_srch_cbk reject block -mark full,dba 0x010002b5,time 08:47:32.914
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kdisnew_bseg_srch_cbk rejecting block ,dba 0x010002b5,time 08:47:32.914
kdisnew_bseg_srch_cbk using block,dba 0x010002b6,time 08:47:32.914
--//有點奇怪的下劃線的內容，不應該提示-mark full,而是鍵值400這個值無法插入這個塊。
--//也許這種情況也是當作mark full處理。


SCOTT@book> @ treedump.sql  i_t1_id
 OBJECT_ID
----------
     92869
Session altered.

*** 2021-06-04 08:48:06.777
----- 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
--//出現50-50分裂。


4.附上執行指令碼:
$ cat  treedump.sql
column object_id new_value m_index_id
select object_id from user_objects where object_name = upper('&&1') and object_type = 'INDEX';
alter session set events 'immediate trace name treedump level &m_index_id';