昨天在Oracle-l看到steve adams提到了一下alter table xxx minimize records_per_block這個命令,因為原來沒用過,所以比較好奇,決定研究一下. 做了一些實驗,基本上得出了一些結果. 最初是一個人想知道pctfree怎么設置才會避免出現行遷移,于是steve就提議用alter table xxx minimize records_per_block來幫助避免行遷移.這個命令本來是用來當創(chuàng)建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環(huán)境比較適合用這種方法來消除行遷移. 下面來說一下我的心得吧,上面我多次提到了需要查出每個塊容納的最大行數,這個Minimize會去計算,但是他究竟是如何計算的呢.我猜想是根據rowid來判定每個塊最大有多少行.我們來trace一下,果然在trace文件里面. select max(sys_op_rpb(rowid)) from "SYS"."TAB_BITMAP" ; 出現了這么一個函數,這個sys_op_rpb是一個undocument的內部函數,用來統(tǒng)計每個塊容納的最大行數.那么我也提到了它會把查出來的最大行數保存在數據字典,那么是那個表呢.繼續(xù)看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=:35whereobj#=:1 出現了,這里的spare1就保存著這個統(tǒng)計出來的值,假如沒有做過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'); --------------------------繼續(xù)插入行,看看是不是不答應每個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------------------
AAAMduAABAAANnyAAAAAAMduAABAAANnyAABAAAMduAABAAANnzAAAAAAMduAABAAANnzAABAAAMduAABAAANn0AAAAAAMduAABAAANn0AABAAAMduAABAAANn1AAAAAAMduAABAAANn1AABAAAMduAABAAANn2AAAAAAMduAABAAANn2AABAAAMduAABAAANn3AAAAAAMduAABAAANn3AABAAAMduAABAAANn4AAAAAAMduAABAAANn4AABAAAMduAABAAANn5AAA 這里可以看出每個塊最多為2行數據,看到minimize的效果了. 上面還提到一點說minimize的最初目的是為了縮小bitmap index的大小,看看效果如何. 做了minimize后建立的bitmap index block dump Start dump data blocks tsn: 0 file#: 1 minblk 55810 maxblk 55810buffer tsn: 0 rdba: 0x0040da02 (1/55810)scn: 0x0000.0015225d seq: 0x01 flg: 0x00 tail: 0x225d0601frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataBlock header dump: 0x0040da02Object id on Block? Yseg/obj: 0xc76f csc: 0x00.15225c itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0015225c Leaf block dump===============header address 68555356=0x416125ckdxcolev 0KDXCOLEV Flags = - - -kdxcolok 0kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Ykdxconco 4kdxcosdc 0kdxconro 1kdxcofbo 38=0x26kdxcofeo 7994=0x1f3akdxcoavs 7956kdxlespl 0kdxlende 0kdxlenxt 0=0x0kdxleprv 0=0x0kdxledsz 0kdxlebksz 8036row#0[7994] flag: ------, lock: 0, len=42col 0; len 15; (15): ce 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0ccol 1; len 6; (6): 00 40 d9 f2 00 00col 2; len 6; (6): 00 40 d9 f9 00 07col 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 55810buffer tsn: 0 rdba: 0x0040da02 (1/55810)scn: 0x0000.001523c5 seq: 0x01 flg: 0x00 tail: 0x23c50601frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataBlock header dump: 0x0040da02Object id on Block? Yseg/obj: 0xc770 csc: 0x00.1523c4 itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001523c4 Leaf block dump===============header address 68555356=0x416125ckdxcolev 0KDXCOLEV Flags = - - -kdxcolok 0kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Ykdxconco 4kdxcosdc 0kdxconro 1kdxcofbo 38=0x26kdxcofeo 7981=0x1f2dkdxcoavs 7943kdxlespl 0kdxlende 0kdxlenxt 0=0x0kdxleprv 0=0x0kdxledsz 0kdxlebksz 8036row#0[7981] flag: ------, lock: 0, len=55col 0; len 15; (15): ce 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0c 0ccol 1; len 6; (6): 00 40 d9 f2 00 00col 2; len 6; (6): 00 40 d9 f9 00 07col 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可以在某些調節(jié)下來防止行遷移,而且可以控制bitmap index的大小,在dw領域有比較好的效果.
