怎么理解oracle索引块分裂

这篇文章主要介绍“怎么理解oracle索引块分裂”，在日常操作中，相信很多人在怎么理解oracle索引块分裂问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么理解oracle索引块分裂”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！B*tree索引是包括oracle在内的数据库中最常见的索引结构。它是一种类似树状结构的索引，最底层的块叫做叶块（leaf block）。叶节点（leaf node）这一层实际上是一个双向链表。如果是一个高度（height）为3的B*tree索引，那么叶块上一层叫做分支块（branch block）。最顶层叫做根块（root block）。根块是索引的入口。但实际上，根块也可以看做是分支块的一种，所以B*tree索引实际只包含两种块：分支块和叶块。分支块用来search，叶块用来存储键值。什么是索引块分裂呢？简单来说，索引块分裂就是当事务的增删改操作需要去维护索引，如果索引块没有足够的空间容纳新数据，就要新增一个索引块，旧的索引块数据一分为二，将其中一部分数据搬迁到新增的索引块上面去。这里的一分为二并不一定是平分，有可能是50-50的平分，也可能是90-10的分裂。这里足够的空间也不一定就是指该块的空闲空间，还有可能是ITL事务槽不够用了。 与索引块分裂有关的等待事件是enq: TX – index contention，同时可能会伴随row lock waits。 索引块分裂分为下面四种情况：l 叶节点90-10分裂l 叶节点50-50分裂l 分支节点分裂l 根节点分裂 其中，根节点分裂可以看做特殊的分支节点分裂。当叶节点空间不够发生分裂时，会多一个叶块出来；叶块持续增加，当分支块的空间也不够用时，分支块也要发生分裂；当分支块持续增加，根块空间不够时，根块也要发生分裂。而根块的分裂会使得B*tree索引的高度（height）增加。如果这种增加是不必要的（高度与索引规模不匹配），那么这会增加不必要的IO，造成索引性能衰退。这种情况是要避免的，此时可以通过重建索引来减小高度。发生90-10分裂时，表的事务并发量较低，索引的键值是单调递增的，这种情况下，只会把较大的那部分索引键值搬迁到新块上，这样新块上空闲空间较多，满足后续递增检查插入。而50-50分裂是在高并发的情况下发生的，而且对索引键值的操控是无序的，但是插入的值还是会比块上的最大值要小，这样每个分裂的块上都会有一半的空闲空间，这样高并发无序的维护索引的时候才会成本较低。想象一下，如果高并发无序维护索引时，索引分裂时按照90-10的方式，那么新块的空闲空间是足够大了，但是旧块上几乎还是满的，无法进行维护，这样会拖累整个索引维护行为。而如果低并发有序操纵索引的行为是按照50-50的方式分裂，那么旧块上因为键值的有序性不会再插入数据，会造成空间浪费，索引块数增加造成索引效率下降，这也是不可取的。下面模拟叶节点90-10分裂：创建测试表：SQL> create table tab91 (id number,name
varchar2(100),created date);创建索引,这里指定pctfree参数为0,即在一个块上不保留空间：SQL> create index idx_tab91_01 on tab91(id)
pctfree 0;这里先简单介绍一个oralce的诊断事件：10224。该事件可以转储索引的分隔与删除。用法：开启:ALTER SESSION SET EVENTS ‘10224 trace name context
forever , level 1’;ALTER SYSTEM SET EVENTS ‘10224 trace name context
forever , level 1’;关闭:ALTER SESSION SET EVENTS ‘10224 trace name context
off’;ALTER SYSTEM SET EVENTS ‘10224 trace name context
off’;再简单介绍一个转储索引结构的语句：alter session set events ‘immediate trace name
treedump level n’;n:索引的object_idselect object_id from dba_objects where
object_name=upper(‘idx_tab91_01’);低并发，正常插入数据时，维护索引的行为：SQL> ALTER SESSION SET EVENTS ‘10224 trace name
context forever , level 1′;declarebegin for i in 1 ..
10000 loop insert into
tab91 values (i,’mingshuo’,sysdate); end loop; commit;end;/SQL> ALTER SESSION SET EVENTS ‘10224 trace name
context off’;检查索引信息：SQL> analyze
index idx_tab91_01 validate structure;Index analyzed.SQL> set line
200SQL> col NAME
for a20SQL> select
height,blocks,name,lf_rows,lf_blks,pct_used from index_stats; HEIGHT BLOCKS NAME LF_ROWS LF_BLKS PCT_USED———-
———- ——————– ———- ———- ———- 2 24 IDX_TAB91_01 10000 19 94可以看到叶块的个数为19.10224事件生成的trace文件：splitting leaf,dba 0x01c0059b,time 03:03:15.462kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c0059b,time 03:03:15.559kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c0059b,time 03:03:15.559kdisnew_bseg_srch_cbk using block,dba 0x01c0059f,time
03:03:15.560kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c0059f,time 03:03:15.560kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c0059f,time 03:03:15.560kdisnew_bseg_srch_cbk using block,dba 0x01c0059c,time
03:03:15.560splitting leaf,dba 0x01c0059c,time 03:03:15.597kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c0059c,time 03:03:15.597kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c0059c,time 03:03:15.597kdisnew_bseg_srch_cbk using block,dba 0x01c0059d,time
03:03:15.597splitting leaf,dba 0x01c0059d,time 03:03:15.622kdisnew_bseg_srch_cbk using block,dba 0x01c0059e,time
03:03:15.622splitting leaf,dba 0x01c0059e,time 03:03:15.633kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c0059e,time 03:03:15.633kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c0059e,time
03:03:15.633kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c0059d,time 03:03:15.633kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c0059d,time 03:03:15.633kdisnew_bseg_srch_cbk using block,dba 0x01c005ab,time
03:03:15.633splitting leaf,dba 0x01c005ab,time 03:03:15.644kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005ab,time 03:03:15.644kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005ab,time 03:03:15.644kdisnew_bseg_srch_cbk using block,dba 0x01c005ac,time
03:03:15.644splitting leaf,dba 0x01c005ac,time 03:03:15.654kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005ac,time 03:03:15.669kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005ac,time 03:03:15.669kdisnew_bseg_srch_cbk using block,dba 0x01c005ad,time
03:03:15.669splitting leaf,dba 0x01c005ad,time 03:03:15.696kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005ad,time 03:03:15.696kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005ad,time 03:03:15.696kdisnew_bseg_srch_cbk using block,dba 0x01c005ae,time 03:03:15.696splitting leaf,dba 0x01c005ae,time 03:03:15.706kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005ae,time 03:03:15.706kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005ae,time 03:03:15.706kdisnew_bseg_srch_cbk using block,dba 0x01c005af,time
03:03:15.706splitting leaf,dba 0x01c005af,time 03:03:15.715kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005af,time 03:03:15.715kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005af,time 03:03:15.715kdisnew_bseg_srch_cbk using block,dba 0x01c005a8,time
03:03:15.715splitting leaf,dba 0x01c005a8,time 03:03:15.727kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005a8,time 03:03:15.727kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005a8,time 03:03:15.727kdisnew_bseg_srch_cbk using block,dba 0x01c005a9,time
03:03:15.727splitting leaf,dba 0x01c005a9,time 03:03:15.738kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005a9,time 03:03:15.738kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005a9,time 03:03:15.738kdisnew_bseg_srch_cbk using block,dba 0x01c005aa,time
03:03:15.738splitting leaf,dba 0x01c005aa,time 03:03:15.747kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005aa,time 03:03:15.747kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005aa,time 03:03:15.747kdisnew_bseg_srch_cbk using block,dba 0x01c005c6,time
03:03:15.747splitting leaf,dba 0x01c005c6,time 03:03:15.757kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005c6,time 03:03:15.757kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005c6,time 03:03:15.757kdisnew_bseg_srch_cbk using block,dba 0x01c005c7,time
03:03:15.757splitting leaf,dba 0x01c005c7,time 03:03:15.768kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005c7,time 03:03:15.768kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005c7,time 03:03:15.768kdisnew_bseg_srch_cbk using block,dba 0x01c005c1,time
03:03:15.768splitting leaf,dba 0x01c005c1,time 03:03:15.778kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005c1,time 03:03:15.778kdisnew_bseg_srch_cbk rejecting block ,dba 0x01c005c1,time
03:03:15.778kdisnew_bseg_srch_cbk using block,dba 0x01c005c2,time
03:03:15.778splitting leaf,dba 0x01c005c2,time 03:03:15.789kdisnew_bseg_srch_cbk using block,dba 0x01c005c3,time
03:03:15.789splitting leaf,dba 0x01c005c3,time 03:03:15.799kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005c3,time 03:03:15.799kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005c3,time 03:03:15.799kdisnew_bseg_srch_cbk using block,dba 0x01c005c4,time
03:03:15.799splitting leaf,dba 0x01c005c4,time 03:03:15.807kdisnew_bseg_srch_cbk reject block -mark full,dba
0x01c005c4,time 03:03:15.807kdisnew_bseg_srch_cbk rejecting block ,dba
0x01c005c4,time 03:03:15.807kdisnew_bseg_srch_cbk using block,dba 0x01c005c5,time
03:03:15.807查询：SQL> select s.SID, n.NAME, s.VALUE 2 from v$sesstat s, v$statname n 3 where s.STATISTIC# = n.STATISTIC# 4 and sid in (select userenv(‘SID’) from dual) 5 and n.NAME like ‘%split%’; SID NAME VALUE———-
—————————— ———- 1 leaf node splits 19 1 leaf node 90-10 splits 18 1 branch node splits 0 1 root node splits 0 1 queue splits 0可以看到发生了18次叶节点90-10分裂，所以叶块正好是19个。转储索引结构：SQL> select object_id from dba_objects where
object_name=upper(‘idx_tab91_01′) and owner=’SCOTT’;OBJECT_ID———- 77956SQL> alter session set events ‘immediate trace
name treedump level 77956’;Dump信息：—– begin tree
dumpbranch: 0x1c0059b
29361563 (0: nrow: 19, level: 1) leaf: 0x1c0059f 29361567 (-1: nrow: 540
rrow: 540) leaf: 0x1c0059c 29361564 (0: nrow: 533 rrow:
533) leaf: 0x1c0059d 29361565 (1: nrow: 533 rrow:
533) leaf: 0x1c0059e 29361566 (2: nrow: 533 rrow:
533) leaf: 0x1c005ab 29361579 (3: nrow: 533 rrow:
533) leaf: 0x1c005ac 29361580 (4: nrow: 533 rrow:
533) leaf: 0x1c005ad 29361581 (5: nrow: 533 rrow:
533) leaf: 0x1c005ae 29361582 (6: nrow: 533 rrow:
533) leaf: 0x1c005af 29361583 (7: nrow: 533 rrow:
533) leaf: 0x1c005a8 29361576 (8: nrow: 533 rrow:
533) leaf: 0x1c005a9 29361577 (9: nrow: 533 rrow:
533) leaf: 0x1c005aa 29361578 (10: nrow: 533
rrow: 533) leaf: 0x1c005c6 29361606 (11: nrow: 533
rrow: 533) leaf: 0x1c005c7 29361607 (12: nrow: 533
rrow: 533) leaf: 0x1c005c1 29361601 (13: nrow: 533
rrow: 533) leaf: 0x1c005c2 29361602 (14: nrow: 533
rrow: 533) leaf: 0x1c005c3 29361603 (15: nrow: 533
rrow: 533) leaf: 0x1c005c4 29361604 (16: nrow: 533
rrow: 533) leaf: 0x1c005c5 29361605 (17: nrow: 399
rrow: 399)—– end tree dump因为之前设置的pctfree为0，所以一个叶块可认为能够存储533行左右。下面模拟叶节点50-50分裂：创建测试表：SQL> create table tab55 (id number,name
varchar2(100),created date);创建索引,这里指定pctfree参数为0,即在一个块上不保留空间：SQL> create index idx_tab55_01 on tab55(id)
pctfree 0;倒序插入数据时，维护索引的行为：SQL> ALTER SESSION SET EVENTS ‘10224 trace name
context forever , level 1′;declarebegin for i in 1 ..
10000 loop insert into
tab55 values (10001-i,’mingshuo’,sysdate); end loop; commit;end;/SQL> ALTER SESSION SET EVENTS ‘10224 trace name
context off’;检查索引信息：SQL> analyze
index idx_tab55_01 validate structure;Index analyzed.SQL> set line
200SQL> col NAME
for a20SQL> select
height,blocks,name,lf_rows,lf_blks,pct_used from index_stats; HEIGHT BLOCKS NAME LF_ROWS LF_BLKS PCT_USED———-
———- ——————– ———- ———- ———- 2 48 IDX_TAB55_01 10000 38 49可以看到叶块的个数为38.10224事件生成的trace文件：splitting leaf,dba
0x01c005e3,time 03:36:47.553kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005e3,time 03:36:47.701kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005e3,time 03:36:47.701kdisnew_bseg_srch_cbk
using block,dba 0x01c005e7,time 03:36:47.703kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005e7,time 03:36:47.703kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005e7,time 03:36:47.703kdisnew_bseg_srch_cbk
using block,dba 0x01c005e4,time 03:36:47.703splitting leaf,dba
0x01c005e7,time 03:36:47.723kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005e4,time 03:36:47.723kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005e4,time 03:36:47.723kdisnew_bseg_srch_cbk
using block,dba 0x01c005e5,time 03:36:47.723splitting leaf,dba
0x01c005e7,time 03:36:47.734kdisnew_bseg_srch_cbk
using block,dba 0x01c005e6,time 03:36:47.734splitting leaf,dba
0x01c005e7,time 03:36:47.744kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005e6,time 03:36:47.745kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005e6,time 03:36:47.745kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005e5,time 03:36:47.745kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005e5,time 03:36:47.745kdisnew_bseg_srch_cbk
using block,dba 0x01c005ef,time 03:36:47.746splitting leaf,dba
0x01c005e7,time 03:36:47.757kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005ef,time 03:36:47.757kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005ef,time 03:36:47.757kdisnew_bseg_srch_cbk
using block,dba 0x01c005e8,time 03:36:47.757splitting leaf,dba
0x01c005e7,time 03:36:47.766kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005e8,time 03:36:47.766kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005e8,time 03:36:47.766kdisnew_bseg_srch_cbk
using block,dba 0x01c005e9,time 03:36:47.766splitting leaf,dba
0x01c005e7,time 03:36:47.774kdisnew_bseg_srch_cbk
using block,dba 0x01c005ec,time 03:36:47.784splitting leaf,dba
0x01c005e7,time 03:36:47.789kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005ec,time 03:36:47.790kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005ec,time 03:36:47.790kdisnew_bseg_srch_cbk
using block,dba 0x01c005ed,time 03:36:47.790splitting leaf,dba
0x01c005e7,time 03:36:47.796kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005ed,time 03:36:47.796kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005ed,time 03:36:47.796kdisnew_bseg_srch_cbk
using block,dba 0x01c005ee,time 03:36:47.796splitting leaf,dba
0x01c005e7,time 03:36:47.801kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005ee,time 03:36:47.801kdisnew_bseg_srch_cbk
rejecting b免费主机域名lock ,dba 0x01c005ee,time 03:36:47.802kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005e9,time 03:36:47.802kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005e9,time 03:36:47.802kdisnew_bseg_srch_cbk
using block,dba 0x01c005ea,time 03:36:47.802splitting leaf,dba
0x01c005e7,time 03:36:47.806kdisnew_bseg_srch_cbk
using block,dba 0x01c005eb,time 03:36:47.806splitting leaf,dba
0x01c005e7,time 03:36:47.813kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005eb,time 03:36:47.813kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005eb,time 03:36:47.813kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005ea,time 03:36:47.813kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005ea,time 03:36:47.813kdisnew_bseg_srch_cbk
using block,dba 0x01c005fc,time 03:36:47.814splitting leaf,dba
0x01c005e7,time 03:36:47.819kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005fc,time 03:36:47.819kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005fc,time 03:36:47.819kdisnew_bseg_srch_cbk
using block,dba 0x01c005fd,time 03:36:47.819splitting leaf,dba
0x01c005e7,time 03:36:47.903kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005fd,time 03:36:47.903kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005fd,time 03:36:47.903kdisnew_bseg_srch_cbk
using block,dba 0x01c005fe,time 03:36:47.903splitting leaf,dba
0x01c005e7,time 03:36:47.908splitting leaf,dba
0x01c005e7,time 03:36:47.909kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005fe,time 03:36:47.909kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005fe,time 03:36:47.909kdisnew_bseg_srch_cbk
using block,dba 0x01c005ff,time 03:36:47.909splitting leaf,dba
0x01c005e7,time 03:36:47.914kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005ff,time 03:36:47.914kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005ff,time 03:36:47.914kdisnew_bseg_srch_cbk
using block,dba 0x01c005f9,time 03:36:47.914splitting leaf,dba
0x01c005e7,time 03:36:47.920kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005f9,time 03:36:47.920kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005f9,time 03:36:47.920kdisnew_bseg_srch_cbk
using block,dba 0x01c005fa,time 03:36:47.920splitting leaf,dba
0x01c005e7,time 03:36:47.926kdisnew_bseg_srch_cbk
using block,dba 0x01c005fb,time 03:36:47.926splitting leaf,dba
0x01c005e7,time 03:36:47.932kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005fb,time 03:36:47.932kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005fb,time 03:36:47.932kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x01c005fa,time 03:36:47.932kdisnew_bseg_srch_cbk
rejecting block ,dba 0x01c005fa,time 03:36:47.932kdisnew_bseg_srch_cbk
using block,dba 0x0300008f,time 03:36:47.932splitting leaf,dba
0x01c005e7,time 03:36:47.938kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300008f,time 03:36:47.938kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300008f,time 03:36:47.938kdisnew_bseg_srch_cbk
using block,dba 0x03000088,time 03:36:47.938splitting leaf,dba
0x01c005e7,time 03:36:47.943kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x03000088,time 03:36:47.943kdisnew_bseg_srch_cbk
rejecting block ,dba 0x03000088,time 03:36:47.943kdisnew_bseg_srch_cbk
using block,dba 0x03000089,time 03:36:47.943splitting leaf,dba
0x01c005e7,time 03:36:47.948kdisnew_bseg_srch_cbk
using block,dba 0x0300008c,time 03:36:47.949splitting leaf,dba
0x01c005e7,time 03:36:47.954kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300008c,time 03:36:47.954kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300008c,time 03:36:47.954kdisnew_bseg_srch_cbk
using block,dba 0x0300008d,time 03:36:47.954splitting leaf,dba
0x01c005e7,time 03:36:47.960kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300008d,time 03:36:47.960kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300008d,time 03:36:47.960kdisnew_bseg_srch_cbk
using block,dba 0x0300008e,time 03:36:47.960splitting leaf,dba
0x01c005e7,time 03:36:47.965kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300008e,time 03:36:47.965kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300008e,time 03:36:47.965kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x03000089,time 03:36:47.965kdisnew_bseg_srch_cbk
rejecting block ,dba 0x03000089,time 03:36:47.965kdisnew_bseg_srch_cbk
using block,dba 0x0300008a,time 03:36:47.965splitting leaf,dba
0x01c005e7,time 03:36:47.972kdisnew_bseg_srch_cbk
using block,dba 0x0300008b,time 03:36:47.973splitting leaf,dba
0x01c005e7,time 03:36:47.981kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300008b,time 03:36:47.981kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300008b,time 03:36:47.981kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300008a,time 03:36:47.981kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300008a,time 03:36:47.981kdisnew_bseg_srch_cbk
using block,dba 0x0300009c,time 03:36:47.982splitting leaf,dba
0x01c005e7,time 03:36:47.987kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300009c,time 03:36:47.987kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300009c,time 03:36:47.987kdisnew_bseg_srch_cbk
using block,dba 0x0300009d,time 03:36:47.987splitting leaf,dba
0x01c005e7,time 03:36:47.993kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300009d,time 03:36:47.993kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300009d,time 03:36:47.993kdisnew_bseg_srch_cbk
using block,dba 0x0300009e,time 03:36:47.993splitting leaf,dba
0x01c005e7,time 03:36:47.999kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300009e,time 03:36:47.999kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300009e,time 03:36:47.999kdisnew_bseg_srch_cbk
using block,dba 0x0300009f,time 03:36:47.999splitting leaf,dba
0x01c005e7,time 03:36:48.06kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300009f,time 03:36:48.06kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300009f,time 03:36:48.06kdisnew_bseg_srch_cbk
using block,dba 0x03000099,time 03:36:48.06splitting leaf,dba
0x01c005e7,time 03:36:48.13kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x03000099,time 03:36:48.13kdisnew_bseg_srch_cbk
rejecting block ,dba 0x03000099,time 03:36:48.13kdisnew_bseg_srch_cbk
using block,dba 0x0300009a,time 03:36:48.13splitting leaf,dba
0x01c005e7,time 03:36:48.20kdisnew_bseg_srch_cbk
using block,dba 0x0300009b,time 03:36:48.20splitting leaf,dba
0x01c005e7,time 03:36:48.25kd免费主机域名isnew_bseg_srch_cbk
reject block -mark full,dba 0x0300009b,time 03:36:48.25kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300009b,time 03:36:48.25kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x0300009a,time 03:36:48.25kdisnew_bseg_srch_cbk
rejecting block ,dba 0x0300009a,time 03:36:48.25kdisnew_bseg_srch_cbk
using block,dba 0x030000af,time 03:36:48.26splitting leaf,dba
0x01c005e7,time 03:36:48.31kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x030000af,time 03:36:48.31kdisnew_bseg_srch_cbk
rejecting block ,dba 0x030000af,time 03:36:48.31kdisnew_bseg_srch_cbk
using block,dba 0x030000a8,time 03:36:48.31splitting leaf,dba
0x01c005e7,time 03:36:48.36kdisnew_bseg_srch_cbk
reject block -mark full,dba 0x030000a8,time 03:36:48.36kdisnew_bseg_srch_cbk
rejecting block ,dba 0x030000a8,time 03:36:48.36kdisnew_bseg_srch_cbk
using block,dba 0x030000a9,time 03:36:48.36splitting leaf,dba
0x01c005e7,time 03:36:48.43kdisnew_bseg_srch_cbk
using block,dba 0x030000ac,time 03:36:48.43查询：SQL> select
s.SID, n.NAME, s.VALUE 2 from v$sesstat s, v$statname n 3 where s.STATISTIC# = n.STATISTIC# 4 and sid in (select sid from v$mystat) 5 and n.NAME like ‘%split%’ 6 ; SID NAME VALUE———-
—————————— ———- 35 leaf node splits 37 35 leaf node 90-10 splits 0 35 branch node splits 0 35 root node splits 0 35 queue splits 0可以看到发生了37次叶节点50-50分裂，所以叶块正好是38个。转储索引结构：SQL> select object_id from dba_objects where
object_name=upper(‘idx_tab55_01′) and owner=’SCOTT’;OBJECT_ID———- 77958SQL> alter session set events ‘immediate trace
name treedump level 77958’;Dump信息：—– begin tree
dumpbranch: 0x1c005e3
29361635 (0: nrow: 38, level: 1) leaf: 0x1c005e7 29361639 (-1: nrow: 289
rrow: 289) leaf: 0x30000ac 50331820 (0: nrow: 262 rrow:
262) leaf: 0x30000a9 50331817 (1: nrow: 262 rrow:
262) leaf: 0x30000a8 50331816 (2: nrow: 262 rrow:
262) leaf: 0x30000af 50331823 (3: nrow: 262 rrow:
262) leaf: 0x300009b 50331803 (4: nrow: 262 rrow:
262) leaf: 0x300009a 50331802 (5: nrow: 266 rrow:
266) leaf: 0x3000099 50331801 (6: nrow: 262 rrow:
262) leaf: 0x300009f 50331807 (7: nrow: 262 rrow:
262) leaf: 0x300009e 50331806 (8: nrow: 262 rrow:
262) leaf: 0x300009d 50331805 (9: nrow: 262 rrow:
262) leaf: 0x300009c 50331804 (10: nrow: 262
rrow: 262) leaf: 0x300008b 50331787 (11: nrow: 262
rrow: 262) leaf: 0x300008a 50331786 (12: nrow: 262
rrow: 262) leaf: 0x300008e 50331790 (13: nrow: 266
rrow: 266) leaf: 0x300008d 50331789 (14: nrow: 262
rrow: 262) leaf: 0x300008c 50331788 (15: nrow: 262
rrow: 262) leaf: 0x3000089 50331785 (16: nrow: 262
rrow: 262) leaf: 0x3000088 50331784 (17: nrow: 262
rrow: 262) leaf: 0x300008f 50331791 (18: nrow: 262
rrow: 262) leaf: 0x1c005fb 29361659 (19: nrow: 262
rrow: 262) leaf: 0x1c005fa 29361658 (20: nrow: 262
rrow: 262) leaf: 0x1c005f9 29361657 (21: nrow: 266
rrow: 266) leaf: 0x1c005ff 29361663 (22: nrow: 262
rrow: 262) leaf: 0x1c005fe 29361662 (23: nrow: 262
rrow: 262) leaf: 0x1c005fd 29361661 (24: nrow: 262
rrow: 262) leaf: 0x1c005fc 29361660 (25: nrow: 262
rrow: 262) leaf: 0x1c005eb 29361643 (26: nrow: 262
rrow: 262) leaf: 0x1c005ea 29361642 (27: nrow: 262
rrow: 262) leaf: 0x1c005ee 29361646 (28: nrow: 262
rrow: 262) leaf: 0x1c005ed 29361645 (29: nrow: 267
rrow: 267) leaf: 0x1c005ec 29361644 (30: nrow: 262
rrow: 262) leaf: 0x1c005e9 29361641 (31: nrow: 262
rrow: 262) leaf: 0x1c005e8 29361640 (32: nrow: 262
rrow: 262) leaf: 0x1c005ef 29361647 (33: nrow: 262
rrow: 262) leaf: 0x1c005e6 29361638 (34: nrow: 262
rrow: 262) leaf: 0x1c005e5 29361637 (35: nrow: 262
rrow: 262) leaf: 0x1c005e4 29361636 (36: nrow: 262
rrow: 262)—– end tree dump可以看到一个50%的索引块大概存储262行左右数据。现在对比一下两个索引：索引名称块的个数叶块的个数索引大小/Midx_tab91_012419192idx_tab55_014838384解决措施：l重建索引，哈希分区索引或者建反向键索引（reverse key index）。重建索引可以降低索引树的高度，反向键索引可以杜绝插入操作导致的索引热块竞争，但是有利就有弊，反向键索引会影响索引范围扫描的效率。哈希分区索引的思路与反向键索引是一致的，都是分散索引热块。在一些OLTP的环境中，存在单调递增的某些列，在插入这些列的时候，只会向索引块的右侧插入，这时因为一些索引页，buffer，latch等争用以及一些附加的索引维护活动，索引右侧反而变成了hotspot。哈希分区索引打散索引块，避免了上述情况下的性能衰退。l更改序列cache值有一下单调递增的列值是用序列值填充的。设置大的cache值，不同的实例会采用不同范围的（noorder）序列值，这样不同实例插入数据的时候插入到同一个块上的概率就会小的多了。避免了热块的出现。用物理上的极限思想来考虑一下，如果cache小到1，那么实例1插入1和实例2插入2大概率在同一个块上。如果cache值为1亿，那么实例1插入1亿1和实例2插入1亿2不大坑在一个块上。l大量数据删除之后rebuild或者shrink索引l增加索引的PCT_FREE值高的PCT_FREE值能够防止ITL槽不够用。这个参数主要对update的系统有用。到此，关于“怎么理解oracle索引块分裂”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注云技术网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: 如何使用Docker构建Nebula Graph源码

这篇文章主要介绍如何使用Docker构建Nebula Graph源码，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！Nebula Grap免费主机域名h 是开源的高性能分布式图数据库。项目使用 C++ 语言开发，cmake 工具构建。其中…