一、单进程的B-Tree索引的分裂

1. 创建测试表

SQL> create table alex_tab (id number, name varchar2(100));

表已创建。

SQL> alter table alex_tab add constraint pk_alex_tab primary key (id) using index;

表已更改。

SQL> create sequence seq_alex_tab
  2  minvalue 1
  3  maxvalue 99999999
  4  start with 1
  5  increment by 1
  6  cache 200;

序列已创建。

2. 单进程插入1万条数据

SQL> declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into alex_tab values (i, 'alex');
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

3. 分析一下索引结构

SQL> col ratio for a20
SQL> col segment_name for a30
SQL> analyze index PK_ALEX_TAB validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'PK_ALEX_TAB';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 0%                           93

SQL> select segment_name,bytes from user_segments where segment_name='PK_ALEX_TAB';

SEGMENT_NAME                        BYTES
------------------------------ ----------
PK_ALEX_TAB                        196608

SQL> select pct_free from user_indexes where index_name='PK_ALEX_TAB';

  PCT_FREE
----------
        10

4. DUMP出主键索引的索引树结构

SQL> select data_object_id,object_id from dba_objects where owner=user and object_name='PK_ALEX_TAB';

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         50709      50709

SQL> alter session set events 'immediate trace name treedump level 50709';

会话已更改。

SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name
  2           from (select p.spid
  3                from v$mystat m, v$session s, v$process p
  4               where m.statistic# = 1
  5                 and s.sid = m.sid
  6                 and p.addr = s.paddr) p,
  7             (select t.instance
  8                from v$thread t, v$parameter v
  9              where v.name = 'thread'
 10                and (v.value = 0 or t.thread# = to_number(v.value))) i,
 11            (select value from v$parameter where name = 'user_dump_dest') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/paic/app/oracle/rdbms/otzj10g/admin/tthigh/udump/tthigh_ora_14193.trc

5. DUMP日志分析

----- begin tree dump
branch: 0x148333c 21508924 (0: nrow: 18, level: 1)
   leaf: 0x1483340 21508928 (-1: nrow: 578 rrow: 578)
   leaf: 0x148333d 21508925 (0: nrow: 571 rrow: 571)
   leaf: 0x148333e 21508926 (1: nrow: 571 rrow: 571)
   leaf: 0x148333f 21508927 (2: nrow: 571 rrow: 571)
   leaf: 0x1483348 21508936 (3: nrow: 571 rrow: 571)
   leaf: 0x1483341 21508929 (4: nrow: 571 rrow: 571)
   leaf: 0x1483342 21508930 (5: nrow: 571 rrow: 571)
   leaf: 0x1483343 21508931 (6: nrow: 571 rrow: 571)
   leaf: 0x1483344 21508932 (7: nrow: 571 rrow: 571)
   leaf: 0x1483345 21508933 (8: nrow: 571 rrow: 571)
   leaf: 0x1483346 21508934 (9: nrow: 571 rrow: 571)
   leaf: 0x1483347 21508935 (10: nrow: 571 rrow: 571)
   leaf: 0x148335b 21508955 (11: nrow: 571 rrow: 571)
   leaf: 0x148335c 21508956 (12: nrow: 571 rrow: 571)
   leaf: 0x148335d 21508957 (13: nrow: 571 rrow: 571)
   leaf: 0x148335e 21508958 (14: nrow: 571 rrow: 571)
   leaf: 0x148335f 21508959 (15: nrow: 571 rrow: 571)
   leaf: 0x1483360 21508960 (16: nrow: 286 rrow: 286)
----- end tree dump

6. 小结
(1)可以看到主键索引只在以下叶块上进行分裂,即最右侧的叶块上进行分裂:

   leaf: 0x1483360 21508960 (16: nrow: 286 rrow: 286)

(2)此时的分裂就索引空间的使用率来说,是比较高效的,使用率大约在93%左右。简单地做个换算,93%充盈的叶块可以存储571行的记录,那一个100%充盈的块即可以存储571/0.93=613行记录。
(3)我们看到主键索引的PCT_FREE=10%,当索引的叶块的利用率达到90%,最右侧的叶块是按9:1的比例进行分裂出新叶块的。也就是我们所说的索引9:1的分裂方式。

二、高并发的B-Tree索引的分裂

1. 创建测试表

SQL> create table alex_tab (id number, name varchar2(100));

表已创建。

SQL> alter table alex_tab add constraint pk_alex_tab primary key (id) using index;

表已更改。

SQL> create sequence seq_alex_tab
  2  minvalue 1
  3  maxvalue 99999999
  4  start with 1
  5  increment by 1
  6  cache 200;

序列已创建。

2. 100个进程迭代100次插入1万条数据

Load        Loops       Proto       TPS         Response(ms)
100         100         thin        108180.0    0.0092

3. 分析一下索引结构

SQL> analyze index PK_ALEX_TAB validate structure;

索引已分析

SQL> col ratio for a20
SQL> col segment_name for a30
SQL> analyze index PK_ALEX_TAB validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'PK_ALEX_TAB';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 0%                           71

SQL> select segment_name,bytes from user_segments where segment_name='PK_ALEX_TAB';

SEGMENT_NAME                        BYTES
------------------------------ ----------
PK_ALEX_TAB                        327680

SQL> select pct_free from user_indexes where index_name='PK_ALEX_TAB';

  PCT_FREE
----------
        10

4. DUMP出主键索引的索引树结构

SQL> select data_object_id,object_id from dba_objects where owner=user and object_name='PK_ALEX_TAB';

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         50711      50711

SQL> alter session set events 'immediate trace name treedump level 50711';

会话已更改。

SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name
  2           from (select p.spid
  3                from v$mystat m, v$session s, v$process p
  4               where m.statistic# = 1
  5                 and s.sid = m.sid
  6                 and p.addr = s.paddr) p,
  7             (select t.instance
  8                from v$thread t, v$parameter v
  9              where v.name = 'thread'
 10                and (v.value = 0 or t.thread# = to_number(v.value))) i,
 11            (select value from v$parameter where name = 'user_dump_dest') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/paic/app/oracle/rdbms/otzj10g/admin/tthigh/udump/tthigh_ora_19509.trc

5. DUMP日志分析

----- begin tree dump
branch: 0x148333c 21508924 (0: nrow: 34, level: 1)
   leaf: 0x1483340 21508928 (-1: nrow: 332 rrow: 332)
   leaf: 0x148333f 21508927 (0: nrow: 253 rrow: 253)
   leaf: 0x148333d 21508925 (1: nrow: 484 rrow: 484)
   leaf: 0x148333e 21508926 (2: nrow: 451 rrow: 451)
   leaf: 0x14a4f3a 21647162 (3: nrow: 401 rrow: 401)
   leaf: 0x14a4f3b 21647163 (4: nrow: 401 rrow: 401)
   leaf: 0x14a4f39 21647161 (5: nrow: 401 rrow: 401)
   leaf: 0x14a4f3c 21647164 (6: nrow: 401 rrow: 401)
   leaf: 0x14a4f3d 21647165 (7: nrow: 401 rrow: 401)
   leaf: 0x14a4f3f 21647167 (8: nrow: 401 rrow: 401)
   leaf: 0x14a4f40 21647168 (9: nrow: 401 rrow: 401)
   leaf: 0x14a4f3e 21647166 (10: nrow: 401 rrow: 401)
   leaf: 0x14a4f44 21647172 (11: nrow: 401 rrow: 401)
   leaf: 0x14a4f48 21647176 (12: nrow: 401 rrow: 401)
   leaf: 0x14a4f45 21647173 (13: nrow: 401 rrow: 401)
   leaf: 0x14a4f43 21647171 (14: nrow: 206 rrow: 206)
   leaf: 0x14a4f46 21647174 (15: nrow: 253 rrow: 253)
   leaf: 0x14a4f42 21647170 (16: nrow: 206 rrow: 206)
   leaf: 0x14a4f47 21647175 (17: nrow: 203 rrow: 203)
   leaf: 0x14a4f4f 21647183 (18: nrow: 200 rrow: 200)
   leaf: 0x14a4f4e 21647182 (19: nrow: 200 rrow: 200)
   leaf: 0x14a4f50 21647184 (20: nrow: 217 rrow: 217)
   leaf: 0x14a4f4d 21647181 (21: nrow: 206 rrow: 206)
   leaf: 0x14a4f4a 21647178 (22: nrow: 205 rrow: 205)
   leaf: 0x14a4f49 21647177 (23: nrow: 206 rrow: 206)
   leaf: 0x14a4f4b 21647179 (24: nrow: 196 rrow: 196)
   leaf: 0x14a4f4c 21647180 (25: nrow: 206 rrow: 206)
   leaf: 0x14a4f52 21647186 (26: nrow: 245 rrow: 245)
   leaf: 0x14a4f54 21647188 (27: nrow: 401 rrow: 401)
   leaf: 0x14a4f57 21647191 (28: nrow: 206 rrow: 206)
   leaf: 0x14a4f53 21647187 (29: nrow: 199 rrow: 199)
   leaf: 0x14a4f55 21647189 (30: nrow: 206 rrow: 206)
   leaf: 0x14a4f58 21647192 (31: nrow: 196 rrow: 196)
   leaf: 0x14a4f56 21647190 (32: nrow: 112 rrow: 112)
----- end tree dump

6. 小结
(1)可以看到主键索引在以下叶块上进行分裂,并非像单进程时只在最右侧的叶块上进行分裂:

   leaf: 0x14a4f43 21647171 (14: nrow: 206 rrow: 206)
   leaf: 0x14a4f46 21647174 (15: nrow: 253 rrow: 253)
   leaf: 0x14a4f42 21647170 (16: nrow: 206 rrow: 206)
   leaf: 0x14a4f47 21647175 (17: nrow: 203 rrow: 203)
   leaf: 0x14a4f4f 21647183 (18: nrow: 200 rrow: 200)
   leaf: 0x14a4f4e 21647182 (19: nrow: 200 rrow: 200)
   leaf: 0x14a4f50 21647184 (20: nrow: 217 rrow: 217)
   leaf: 0x14a4f4d 21647181 (21: nrow: 206 rrow: 206)
   leaf: 0x14a4f4a 21647178 (22: nrow: 205 rrow: 205)
   leaf: 0x14a4f49 21647177 (23: nrow: 206 rrow: 206)
   leaf: 0x14a4f4b 21647179 (24: nrow: 196 rrow: 196)
   leaf: 0x14a4f4c 21647180 (25: nrow: 206 rrow: 206)
   leaf: 0x14a4f52 21647186 (26: nrow: 245 rrow: 245)
   leaf: 0x14a4f54 21647188 (27: nrow: 401 rrow: 401)
   leaf: 0x14a4f57 21647191 (28: nrow: 206 rrow: 206)
   leaf: 0x14a4f53 21647187 (29: nrow: 199 rrow: 199)
   leaf: 0x14a4f55 21647189 (30: nrow: 206 rrow: 206)
   leaf: 0x14a4f58 21647192 (31: nrow: 196 rrow: 196)
   leaf: 0x14a4f56 21647190 (32: nrow: 112 rrow: 112)

(2)此时的分裂就索引空间的使用率来说,效率就比较低了,使用率大约在71%左右。
(3)我们看到主键索引的PCT_FREE=10%,按照正常分裂来说,应该9:1的比例进行分裂出新叶块的,但实际情况并非如此。
(4)此时,非最右侧的叶块也参与了分裂,而且分裂过程是不参照PCT_FREE的比例来分裂的,基本上利用率达到50%(单块613*50%=306行数据),甚至不到50%,即开始分裂。
(5)B-Tree索引为了保证并发处理,当最右侧的叶块来不及分裂的时候,则会逐渐向左选择叶块参与分裂。
(6)B-Tree的非最右侧的叶块参与分裂时,是按照5:5的比例进行分裂的。

三、重建一下高并发后B-Tree索引,在分析一下
1. 重建一下主键,再分析一下结构。

SQL> alter index pk_alex_tab rebuild online;

索引已更改。

SQL> analyze index PK_ALEX_TAB validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'PK_ALEX_TAB';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 0%                           84

SQL> select segment_name,bytes from user_segments where segment_name='PK_ALEX_TAB';

SEGMENT_NAME                        BYTES
------------------------------ ----------
PK_ALEX_TAB                        262144

2. 再DUMP出重建后的主键索引的索引树结构

SQL> alter session set events 'immediate trace name treedump level 50711';

会话已更改。

SQL>
SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name
  2           from (select p.spid
  3                from v$mystat m, v$session s, v$process p
  4               where m.statistic# = 1
  5                 and s.sid = m.sid
  6                 and p.addr = s.paddr) p,
  7             (select t.instance
  8                from v$thread t, v$parameter v
  9              where v.name = 'thread'
 10                and (v.value = 0 or t.thread# = to_number(v.value))) i,
 11            (select value from v$parameter where name = 'user_dump_dest') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/paic/app/oracle/rdbms/otzj10g/admin/tthigh/udump/tthigh_ora_19509.trc

3. 再分析一下DUMP日志

----- begin tree dump
branch: 0x14a4f64 21647204 (0: nrow: 20, level: 1)
   leaf: 0x14a4f65 21647205 (-1: nrow: 520 rrow: 520)
   leaf: 0x14a4f66 21647206 (0: nrow: 513 rrow: 513)
   leaf: 0x14a4f67 21647207 (1: nrow: 513 rrow: 513)
   leaf: 0x14a4f68 21647208 (2: nrow: 513 rrow: 513)
   leaf: 0x14a4f69 21647209 (3: nrow: 513 rrow: 513)
   leaf: 0x14a4f6a 21647210 (4: nrow: 513 rrow: 513)
   leaf: 0x14a4f6b 21647211 (5: nrow: 513 rrow: 513)
   leaf: 0x14a4f6c 21647212 (6: nrow: 513 rrow: 513)
   leaf: 0x14a4f6d 21647213 (7: nrow: 513 rrow: 513)
   leaf: 0x14a4f6e 21647214 (8: nrow: 513 rrow: 513)
   leaf: 0x14a4f6f 21647215 (9: nrow: 513 rrow: 513)
   leaf: 0x14a4f70 21647216 (10: nrow: 513 rrow: 513)
   leaf: 0x14a4f72 21647218 (11: nrow: 513 rrow: 513)
   leaf: 0x14a4f73 21647219 (12: nrow: 513 rrow: 513)
   leaf: 0x14a4f74 21647220 (13: nrow: 513 rrow: 513)
   leaf: 0x14a4f75 21647221 (14: nrow: 513 rrow: 513)
   leaf: 0x14a4f76 21647222 (15: nrow: 513 rrow: 513)
   leaf: 0x14a4f77 21647223 (16: nrow: 513 rrow: 513)
   leaf: 0x14a4f78 21647224 (17: nrow: 513 rrow: 513)
   leaf: 0x14a4f79 21647225 (18: nrow: 246 rrow: 246)
----- end tree dump

4. 小结
可以看到此时的索引结构和单进程处理后结构差不多了。

四、总结
1. B-Tree索引正常情况的分裂,都是在最右侧的叶块进行的,并参照PCT_FREE参数(默认10%),当索引的叶块的利用率达到90%,最右侧的叶块是按9:1的比例进行分裂出新叶块的。
2. B-Tree索引最右侧的叶块来不及分裂的时候,比如高并发处理的时候,B-Tree索引为了保证并发处理,则会逐渐向左选择叶块参与分裂。
3. B-Tree索引非最右侧的叶块进行分裂时,并不参照PCT_FREE的比例,利用率达到50%(甚至不到50%),即开始分裂,非最右侧的叶块是按5:5的比例进行分裂出新叶块的。

Trackback

no comment untill now

Add your comment now

切换到手机版