blevel 2 到 blevel 3的分裂

上午又做了个测试，由于昨天测试8K的数据块有一定难度，插入2000万行数据仍然没有blevel的改变，今天使用2K的数据块做测试

在level 2 到 level 3的时候数据库没有什么明显的等待，具体分裂的节点可以忽略不记，如有不同意见可以再讨论

这个主要是在顺序插入模式下做了实验，针对并发处理实验比较难做

具体测试结果如下

create tablespace indexsplit datafile '/oradata/db10g/indexsplit01.dbf' size 2048M autoextend off
extent management local uniform size 10M BLOCKSIZE 2k segment space management auto;

alter user scott quota unlimited on indexsplit;

grant dba to scott;

conn scott/tiger;
create table normal(a number) tablespace indexsplit;
create index idx_normal on normal (a) pctfree 20 initrans 10 tablespace indexsplit ;
SQL> select object_id,DATA_OBJECT_ID,object_type from dba_objects where object_name='IDX_NORMAL';

SQL> select object_id,DATA_OBJECT_ID,object_type from dba_objects where object_name='IDX_NORMAL';

OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
48914 48933 INDEX

create sequence seq_normal start with 9999999999 cache 20000;

实验开始，该表里没有任何数据，这时候我们看到生成的trace里该树上只有一个叶子节点

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 48914';

db10g_ora_3518.trc

----- begin tree dump
leaf: 0x2c01473 46142579 (0: nrow: 0 rrow: 0)
----- end tree dump


select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK + 1 from dba_segments where SEGMENT_NAME ='IDX_NORMAL';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK+1
--------------------------------------------------------------------------------- ----------- --------------
IDX_NORMAL 11 5235

alter system dump datafile 11 block 5235;

db10g_ora_3469.trc

Leaf block dump
===============
header address 208610596=0xc6f2524
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 1696=0x6a0
kdxcoavs 1660 ======================================>块上剩余空间较大
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 1696
----- end of leaf block dump -----

然后我们插入一批数据
set timing on
begin
for i in 1..1381000
loop
insert into normal(a) values (seq_normal.nextval);
end loop;
end;
/
commit;


针对索引做DUMP后确定树级别为2
branch: 0x2c01473 46142579 (0: nrow: 126, level: 2)
branch: 0x2c014fc 46142716 (-1: nrow: 126, level: 1)
leaf: 0x2c01486 46142598 (-1: nrow: 87 rrow: 87)
leaf: 0x2c01489 46142601 (0: nrow: 87 rrow: 87)
leaf: 0x2c0148c 46142604 (1: nrow: 87 rrow: 87)
leaf: 0x2c0148f 46142607 (2: nrow: 87 rrow: 87)
leaf: 0x2c01495 46142613 (3: nrow: 87 rrow: 87)
leaf: 0x2c01492 46142610 (4: nrow: 87 rrow: 87)
leaf: 0x2c01498 46142616 (5: nrow: 87 rrow: 87)
leaf: 0x2c0149b 46142619 (6: nrow: 87 rrow: 87)
leaf: 0x2c0149e 46142622 (7: nrow: 87 rrow: 87)
leaf: 0x2c01478 46142584 (8: nrow: 87 rrow: 87)
leaf: 0x2c01475 46142581 (9: nrow: 87 rrow: 87)
leaf: 0x2c0147b 46142587 (10: nrow: 87 rrow: 87)
leaf: 0x2c0147e 46142590 (11: nrow: 87 rrow: 87)
....
leaf: 0x2c01503 46142723 (113: nrow: 83 rrow: 83)

在该插入过程中，有索引分裂是很正常的

NAME VALUE
---------------------------------------------------------------- ----------
leaf node splits 15873
leaf node 90-10 splits 15873
branch node splits 125
queue splits 0


插入后检查root branch的剩余空间很小，测试下次插入就要做root分裂
Branch block dump
=================
header address 208610380=0xc6f244c
kdxcolev 2
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 125
kdxcofbo 278=0x116
kdxcofeo 289=0x121
kdxcoavs 11
kdxbrlmc 46142716=0x2c014fc
kdxbrsno 124
kdxbrbksz 1912
kdxbr2urrc 0


从这里开始要ROOT分裂,但是从执行结果上看，也只用了不到一秒
set timing on
begin
for i in 1..1000
loop
insert into normal(a) values (seq_normal.nextval);
end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
在当前分裂过程中，发现只是有两个BRANCH及部分LEAF的分裂，针对性能忽略不计
SQL> select n.name,s.value from v$statname n,v$mystat s where s.statistic#=n.statistic# and n.name like '%split%';

NAME VALUE
---------------------------------------------------------------- ----------
leaf node splits 12
leaf node 90-10 splits 12
branch node splits 2
queue splits 0


做DUMP确定索引的级别

----- begin tree dump
branch: 0x2c01473 46142579 (0: nrow: 2, level: 3)
branch: 0x2c06808 46163976 (-1: nrow: 126, level: 2)
branch: 0x2c014fc 46142716 (-1: nrow: 126, level: 1)
leaf: 0x2c01486 46142598 (-1: nrow: 87 rrow: 87)
leaf: 0x2c01489 46142601 (0: nrow: 87 rrow: 87)

Branch block dump
=================
header address 208610380=0xc6f244c
kdxcolev 3
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x83: opcode=3: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 3
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 1899=0x76b
kdxcoavs 1869
kdxbrlmc 46163976=0x2c06808
kdxbrsno 124
kdxbrbksz 1912
kdxbr2urrc 0[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/48010/viewspace-1030563/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/48010/viewspace-1030563/