昨天在oracle-l看到steve adams提到了一下alter table xxx minimize records_per_block这个命令,因为原来没用过,所以比较好奇,决定研究一下.
做了一些实验,基本上得出了一些结果.
最初是一个人想知道pctfree怎么设置才会避免出现行迁移,于是steve就提议用alter table xxx minimize records_per_block来帮助避免行迁移.这个命令本来是用来当创建bitmap index时缩小index size的.但是steve提出一个不错解决方案.他提到可以用alter table xxx minimize records_per_block来找到当前表所有block中容纳的最大行数,并会把这个数字记录到数据字典,以后任何导致block行数超过这个数字的插入都会被拒绝.(听到这里好像跟行链接又相关了,steve好像容易跑踢,呵呵)然后他又提议重建表并导入n(n=先前运行minimize命令得出的行数)条数据,然后运行alter table xxx minimize records_per_block,再读入其他剩余数据.然后他还补充这时候应该把Pctfree设成1为了预留itl的增长,说到最后其实还是说到 dw去了,他这样做来消除行迁移还是要些必要条件的.这些行的长度将来不能变化很大,否则行迁移还会出现.或者还有另一种办法就是插入一些每列都是最大长度的数据进去另一张临时表,然后用minimize分析出最大行数,再按第一种方法搞,不过这样可能空间浪费比较大.所以总的来说还是dw环境比较适合用这种方法来消除行迁移.
下面来说一下我的心得吧,上面我多次提到了需要查出每个块容纳的最大行数,这个Minimize会去计算,但是他究竟是如何计算的呢.我猜想是根据rowid来判断每个块最大有多少行.我们来trace一下,果然在trace文件里面.
select max(sys_op_rpb(rowid)) from "SYS"."TAB_BITMAP" ;
出现了这么一个函数,这个sys_op_rpb是一个undocument的内部函数,用来统计每个块容纳的最大行数.那么我也提到了它会把查出来的最大行数保存在数据字典,那么是那个表呢.继续看trace,
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,
null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
spare6=:35
where
obj#=:1
出现了,这里的spare1就保存着这个统计出来的值,如果没有做过minimize,那么这个值是个默认的最大值(会比正常值大很多),如果用了minimize,他会保存真实的值.
SQL> CREATE TABLE tab_bitmap (col1 number,col2 varchar2(50),col3 varchar2(50)) ;
Table created.
SQL> INSERT INTO tab_bitmap values (1111111111111111111111111111,'AAAAAAAAAAAAAAAAAAAA','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_id from dba_objects where object_name='TAB_BITMAP';
OBJECT_ID
----------
SQL> SELECT SPARE1 FROM TAB$ WHERE OBJ#=51054;
SPARE1
----------
736 ------------------- 默认的值
SQL> alter table tab_bitmap MINIMIZE RECORDS_PER_BLOCK;
Table altered.
SQL> SELECT SPARE1 FROM TAB$ WHERE OBJ#=51054;
SPARE1
----------
32769 --------------------- 这里的32769实际上是32767+2,2才是真正能够容纳的最大行数,这里为什么是2而不是1呢,可能是oracle做了限制最小是2行.
SQL> SELECT EXTENT_ID FROM DBA_EXTENTS WHERE SEGMENT_NAME='TAB_BITMAP';
EXTENT_ID
----------
0
SQL> INSERT INTO tab_bitmap values (1111111111111111111111111111,'AAAAAAAAAAAAAAAAAAAA','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
--------------------------继续插入行,看看是不是不允许每个block多于1行
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> SELECT EXTENT_ID,blocks FROM DBA_EXTENTS WHERE SEGMENT_NAME='TAB_BITMAP';
EXTENT_ID BLOCKS
---------- ----------
0 8
SQL> INSERT INTO tab_bitmap values (1111111111111111111111111111,'AAAAAAAAAAAAAAAAAAAA','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> SELECT EXTENT_ID,blocks FROM DBA_EXTENTS WHERE SEGMENT_NAME='TAB_BITMAP';
EXTENT_ID BLOCKS
---------- ----------
0 8
1 8
果然,在插入了10多行后便又分配了一个extent(8 blocks)
我们来看rowid
SQL> select rowid from tab_bitmap;
ROWID
------------------
AAAMduAABAAANnyAAA
AAAMduAABAAANnyAAB
AAAMduAABAAANnzAAA
AAAMduAABAAANnzAAB
AAAMduAABAAANn0AAA
AAAMduAABAAANn0AAB
AAAMduAABAAANn1AAA
AAAMduAABAAANn1AAB
AAAMduAABAAANn2AAA
AAAMduAABAAANn2AAB
AAAMduAABAAANn3AAA
AAAMduAABAAANn3AAB
AAAMduAABAAANn4AAA
AAAMduAABAAANn4AAB
AAAMduAABAAANn5AAA
这里可以看出每个块最多为2行数据,看到minimize的效果了.
上面还提到一点说minimize的最初目的是为了缩小bitmap index的大小,看看效果如何.
做了minimize后建立的bitmap index block dump
Start dump data blocks tsn: 0 file#: 1 minblk 55810 maxblk 55810
buffer tsn: 0 rdba: 0x0040da02 (1/55810)
scn: 0x0000.0015225d seq: 0x01 flg: 0x00 tail: 0x225d0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0040da02
Object id on Block? Y
seg/obj: 0xc76f csc: 0x00.15225c itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0015225c
Leaf block dump
===============
header address 68555356=0x416125c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 7994=0x1f3a
kdxcoavs 7956
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[7994] flag: ------, lock: 0, len=42
col 0; len 15; (15): ce 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c
col 1; len 6; (6): 00 40 d9 f2 00 00
col 2; len 6; (6): 00 40 d9 f9 00 07
col 3; len 9; (9): cf 03 03 03 03 03 03 03 01 -----注意这里,只有9的长度
----- end of leaf block dump -----
没有做minimize的bitmap index block dump
Start dump data blocks tsn: 0 file#: 1 minblk 55810 maxblk 55810
buffer tsn: 0 rdba: 0x0040da02 (1/55810)
scn: 0x0000.001523c5 seq: 0x01 flg: 0x00 tail: 0x23c50601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0040da02
Object id on Block? Y
seg/obj: 0xc770 csc: 0x00.1523c4 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001523c4
Leaf block dump
===============
header address 68555356=0x416125c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 7981=0x1f2d
kdxcoavs 7943
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[7981] flag: ------, lock: 0, len=55
col 0; len 15; (15): ce 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c
col 1; len 6; (6): 00 40 d9 f2 00 00
col 2; len 6; (6): 00 40 d9 f9 00 07
col 3; len 22; (22): ----------------这里是22的长度
c8 03 f8 56 03 f8 56 03 f8 56 03 f8 56 03 f8 56 03 f8 56 03 c0 44
----- end of leaf block dump -----
做了minimize后建bitmap index就会根据spare1来屏蔽掉一些不存在的row,表现在col 3里面少了很多bit 0的位,这样bitmap index的大小会缩小很多.
下个结论吧:
minimize可以在某些调节下来防止行迁移,而且可以控制bitmap index的大小,在dw领域有比较好的效果.