[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';