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