ITPub博客

首页 > 数据库 > Oracle > OLTP压缩表在delete、insert操作下的空间分配

OLTP压缩表在delete、insert操作下的空间分配

原创 Oracle 作者:oliseh 时间:2014-09-14 22:54:23 0 删除 编辑

在使用oltp压缩方式存放的表中delete操作后释放出来的空间并不会被后来的insert语句所重用,换而言之,频繁的对oltp压缩表进行delete & insert操作会占用愈来愈多的存储空间。

通过测试验证一下,测试步骤:

创建压缩表并插入记录à收集表的block使用信息àdump data blockà删除表中一半记录à收集表的block使用信息àdump data blockà重新插入被删除的记录àdump data blockà收集表的block使用信息

 

 

##创建压缩表并往里灌入10W条数据,特地设计一些内容重复的字段,否则没法压缩

drop table system.numtabc;

 

create table system.numtabc(id number,col2 varchar2(2),col3 varchar2(2),col4 varchar2(2)) tablespace ts_zwcs01_dat compress for oltp;

 

select compression,compress_for from dba_tables where table_name='NUMTABC' ;

COMPRESS COMPRESS_FOR

-------- ------------

ENABLED  OLTP

 

declare

begin

for i in 1..100000 loop

insert into system.numtabc values(mod(i,2),'AA','BB','CC');

end loop;

commit;

end;

/

 

col segment_name format a40

set linesize 130

select segment_name,blocks,header_file,relative_fno,header_block,bytes from dba_segments where segment_name='NUMTABC';

 

SEGMENT_NAME                                 BLOCKS HEADER_FILE RELATIVE_FNO HEADER_BLOCK      BYTES

---------------------------------------- ---------- ----------- ------------ ------------ ----------

NUMTABC                                         256         230          230       145338    2097152

 

 

 

##为达到对比效果,创建了一个非压缩的表,表里的记录和压缩表完全一样

drop table system.numtabnc;

create table system.numtabnc(id number,col2 varchar2(2),col3 varchar2(2),col4 varchar2(2)) tablespace ts_zwcs01_dat;

insert数据的步骤和前面一样)

 

select compression,compress_for from dba_tables where table_name='NUMTABNC' ;

COMPRESS COMPRESS_FOR

-------- ------------

DISABLED

 

select segment_name,blocks,header_file,relative_fno,header_block,bytes from dba_segments where segment_name='NUMTABNC';

 

SEGMENT_NAME                                 BLOCKS HEADER_FILE RELATIVE_FNO HEADER_BLOCK      BYTES

---------------------------------------- ---------- ----------- ------------ ------------ ----------

NUMTABNC                                        256         230          230       145346    2097152

 

##对比两张表在dba_segments中显示占用的空间完全一样

SQL> select segment_name,blocks,header_file,header_block,bytes from dba_segments where segment_name='NUMTABNC';

 

SEGMENT_NAME                                 BLOCKS HEADER_FILE HEADER_BLOCK      BYTES

---------------------------------------- ---------- ----------- ------------ ----------

NUMTABNC                                        256         230       145346    2097152

 

SQL> select segment_name,blocks,header_file,header_block,bytes from dba_segments where segment_name='NUMTABC';

 

SEGMENT_NAME                                 BLOCKS HEADER_FILE HEADER_BLOCK      BYTES

---------------------------------------- ---------- ----------- ------------ ----------

NUMTABC                                         256         230       145338    2097152

 

##但两张表在data block的使用上有所不同,dbms_space.space_usage的运行结果可以看出压缩表中有unformatted block,非fullblock也比非压缩表要多

            numtabc    numtabnc

unformated:   62         0

<25% :        26         16

<50% :        0          1

75%  :        1          0

100% :        0          0

full :         155        227

 

##对于非压缩的表进行压缩比的评估,得到压缩比为1.4:1

exec DBMS_COMPRESSION.GET_COMPRESSION_RATIO('TS_ZWCS01_IND','SYSTEM','NUMTABNC',null,DBMS_COMPRESSION.comp_for_oltp,:blkcnt_cmp,:blkcnt_uncmp,:row_cmp,:row_uncmp,:cmp_ratio,:comptype_str);

 

CMP_RATIO

----------

       1.4

 

##验证一下1.4:1这个压缩比与实际情况的相似度:

压缩表中实际使用的blocks256-62(unformated)=194,非压缩表中实际使用的Blocks256,256:194=1.32,所以1.4这个值还是预估的比较准确的

 

##删除前dump一下230/145339块的内容到numtabc.blk.txt

Dump块的内容摘要如下:

tab 0, row 0, @0x1f89

tl: 15 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 2]  c1 02

bindmp: 01 37 04 ca 41 41 ca 42 42 ca 43 43 ca c1 02

tab 0, row 1, @0x1f7b

tl: 14 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 1]  80

bindmp: 01 37 04 ca 41 41 ca 42 42 ca 43 43 c9 80

tab 1, row 0, @0x1f76

tl: 5 fb: --H-FL-- lb: 0x1  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 2]  c1 02

bindmp: 2c 01 01 04 00

tab 1, row 1, @0x1f71

tl: 5 fb: --H-FL-- lb: 0x1  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 1]  80

bindmp: 2c 01 01 04 01

tab 1, row 2, @0x1f6c

tl: 5 fb: --H-FL-- lb: 0x1  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 2]  c1 02

bindmp: 2c 01 01 04 00

 

##删除压缩表中的一半记录,删除前备份一下即将被删除的记录到临时表,以便后续插入时使用

create table system.numtabc_tmp tablespace ts_zwcs01_dat as select * from system.numtabc where id=1;

 

select count(1) from  system.numtabc_tmp;

  COUNT(1)

----------

     50000

 

delete system.numtabc where id=1;

50000 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system checkpoint;

 

System altered.

 

 

##删除后dump 230/145339块的内容,可以看出tab 1中被删除的块内容变成了--HDFL—

tab 0, row 0, @0x1f89

tl: 15 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 2]  c1 02

bindmp: 00 00 04 ca 41 41 ca 42 42 ca 43 43 ca c1 02

tab 0, row 1, @0x1f7b

tl: 14 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 1]  80

bindmp: 01 37 04 ca 41 41 ca 42 42 ca 43 43 c9 80

tab 1, row 0, @0x1f76

tl: 2 fb: --HDFL-- lb: 0x2

bindmp: 3c 02

tab 1, row 1, @0x1f71

tl: 5 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 1]  80

bindmp: 2c 00 01 04 01

tab 1, row 2, @0x1f6c

tl: 2 fb: --HDFL-- lb: 0x2

bindmp: 3c 02

 

##删除前后的block space使用情况对比,发现很多原来塞满的表现在利用率都降到了50%~75%之间

            numtabc删除后 numtabc删除前   

unformated:   62            62

<25% :        26            26

<50% :        1             0 

75%  :       155           1 

100% :        0             0 

full :          0             155

 


 

##再重新插入被删除的这部分记录

SQL> select count(1) from system.numtabc_tmp;

 

  COUNT(1)

----------

     50000

 

insert into system.numtabc select * from system.numtabc_tmp;

##重新插入后表的大小未发生变化

SQL> select segment_name,blocks,header_file,relative_fno,header_block,bytes from dba_segments where segment_name='NUMTABC';

 

SEGMENT_NAME                                 BLOCKS HEADER_FILE RELATIVE_FNO HEADER_BLOCK      BYTES

---------------------------------------- ---------- ----------- ------------ ------------ ----------

NUMTABC                                         256         230          230       145338    2097152

 

##dump重新插入之后的145339,块的内容保存在numtabc.insert.txt中,摘录部分块信息,发现此块内容和delete前的一样,结果中仍然有--HDFL--,说明重新插入时没有重用此块中的可用空间

tab 0, row 0, @0x1f89

tl: 15 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 2]  c1 02

bindmp: 00 00 04 ca 41 41 ca 42 42 ca 43 43 ca c1 02

tab 0, row 1, @0x1f7b

tl: 14 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 1]  80

bindmp: 01 37 04 ca 41 41 ca 42 42 ca 43 43 c9 80

tab 1, row 0, @0x1f76

tl: 2 fb: --HDFL-- lb: 0x2

bindmp: 3c 02

tab 1, row 1, @0x1f71

tl: 5 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 1]  80

bindmp: 2c 00 01 04 01

tab 1, row 2, @0x1f6c

tl: 2 fb: --HDFL-- lb: 0x2

bindmp: 3c 02

 

##通过执行compress_row.shcalc_row_perblk.sh等脚本,脚本可参照附录,找出五种包含行数不同的块,

5类块的详细信息依次包含在如下文件中(详见附录2)
numtabc_reinsert_comp_fullblk.txt

numtabc_reinsert_comp_mostblk.txt

numtabc_reinsert_comp_halfblk.txt

numtabc_reinsert_comp_littleblk.txt

numtabc_reinsert_nocomp_blk.txt

其中

(1) 56block,每个block包含642条记录,均为压缩,例如:555/281391block dump的主要特征是

--Block header部分

flag=-0----X-

ntab=2

nrow=643

frre=-1

--data block部分,均为H-FL,没有D标记

tab 1, row 641, @0x1358

tl: 16 fb: --H-FL-- lb: 0x1  cc: 4

col  0: [ 2]  c1 02

col  1: [ 2]  41 41

col  2: [ 2]  42 42

col  3: [ 2]  43 43

bindmp: 2c 01 04 00 ca c1 02 ca 41 41 ca 42 42 ca 43 43

 

(2) 75block,每个block包含506条记录,均为压缩,例如:452/67165block dump的主要特征是

--Block header部分

flag=-0------

ntab=2

nrow=644

frre=372   --表示row directory里首个可用的行条目序号

--row directory部分,从372个条目开始往后开始,每隔一条出现一个可用的行条目

0x312:pri[371] offs=0x1841

0x314:pri[372] sfll=374

0x316:pri[373] offs=0x1837

0x318:pri[374] sfll=376

 

(3) 80block,每个block包含321条记录,均为压缩,例如:555/281460block dump的主要特征是

--Block header 部分,虽然block中有可以重用的行,但frre并没有指向首个可用的行条目序号,而是显示为-1,这个和block clean out[C3] 机制有关

frre=-1

--Data block 部分,--HDFL——H-FL—间隔出现,和delete之后的情况一致,表明该block并没有在重新插入时被重复利用

tab 1, row 0, @0x1f76

tl: 2 fb: --HDFL-- lb: 0x2

bindmp: 3c 02

tab 1, row 1, @0x1f71

tl: 5 fb: --H-FL-- lb: 0x0  cc: 4

col  0: [ 2]  41 41

col  1: [ 2]  42 42

col  2: [ 2]  43 43

col  3: [ 1]  80

bindmp: 2c 00 01 04 01

 

(4) 1block,这个block包含245条记录,均为压缩,例如:555/281429block dump的主要特征同(3)

 

(5) 1block,这个block包含173条记录,没有压缩,例如:555/281370block dump的主要特征是

--Block header部分

flag=--------          此处没有0,表示为非压缩块

ntab=1             ntab=1,表示没有符号表,块里仅有一个表的数据

nrow=173

frre=-1

 

##插入后的block space使用情况,可以看出插入后原本unformatted的空间被利用了

              numtabc 重新插入后     numtabc删除后插入前   numtabc删除前

unformated:    0                      62                    62           

<25% :         32                     26                    26           

<50% :         1                      1                     0            

75%  :        155                    155                   1            

100% :         0                      0                     0            

full :           56                     0                     155          

 

 

 

结论: 删除后插入前,fullblock里有一半的行被删掉了,所以155block从原来的full区间落到了50%~75%这个区间。 在之后重新插入的过程中,oracle会优先使用unformatted62block,而不是重用删除后留有空闲空间的block,对于空间使用率在50%~75%的155个块= 75block (每个包含506行记录)+ 80block (每个包含321行记录),仍然保持删除后的状态。重新插入后full状态的block56个,其数目正好等于包含642行的block个数(尚无法确认的是62unformatted block中的56个已经变成了full状态,剩下的6个可能变成了<25%的状态,包含245条记录的block 555/281429,包含273条记录的555/281370,照理都应归到<50%这个区间,但dbms_space.space_usage结果显示该区间只有1block
上述测试告诉我们为了达到较理想的压缩效果,对于oltp压缩表尽量少的进行dml操作,以保持其每个blocks的空间利用率接近100%,降低sparse blocks的数量。

 

附录1:脚本说明

***********compress_row.sh返回每一行对应的relative_fnoblock_number、压缩类型

sqlplus '/as sysdba' << EOF > compress_row_numtabc_insert.log

set pagesize 20000

select dbms_compression.get_compression_type('SYSTEM','NUMTABC',rowid) comp_type,rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) blkno,DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) rfno from system.numtabc;

EOF

 

**********生成numtabc_blk.txt.all,包含rowid中的7-15位,即rfile+blocknum部分

cat compress_row_numtabc_insert.log | awk '$1==1' | awk '{print $2}' | cut -c 7-15|sort -u > numtabc_blk.txt.1

cat compress_row_numtabc_insert.log | awk '$1==2' | awk '{print $2}' | cut -c 7-15|sort -u > numtabc_blk.txt.2

cat numtabc_blk.txt.1 numtabc_blk.txt.2 >> numtabc_blk.txt.all

 

**********calc_row_perblk.sh 返回每个block包含的行数

> numtabc_rows_per_block.txt

cat numtabc_blk.txt.all| while read v_blkno

do

echo "$v_blkno""  : " `grep $v_blkno compress_row_numtabc_insert.log|awk '{print $3"  "$4}' | head -n 1` `grep $v_blkno compress_row_numtabc_insert.log | wc -l` >> numtabc_rows_per_block.txt

done


 

附录2block dump输出:

 20140426 compress table delete insert操作空间分配附件.rar

 

 

附录3:查看表中每行的压缩类型:

SELECT CASE comp_type

         WHEN 1 THEN 'No Compression'

         WHEN 2 THEN 'Advanced compression level'

         WHEN 4 THEN 'Hybrid Columnar Compression for Query High'

         WHEN 8 THEN 'Hybrid Columnar Compression for Query Low'

         WHEN 16 THEN 'Hybrid Columnar Compression for Archive High'

         WHEN 32 THEN 'Hybrid Columnar Compression for Archive Low'

         WHEN 64 THEN 'Compressed row'

         WHEN 128 THEN 'High compression level for LOB operations'

         WHEN 256 THEN 'Medium compression level for LOB operations'

         WHEN 512 THEN 'Low compression level for LOB operations'

         WHEN 1000 THEN 'Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated'

         WHEN 4096 THEN 'Basic compression level'

         WHEN 5000 THEN 'Maximum number of LOBs used to compute the LOB compression ratio'

         WHEN 1000000 THEN 'Minimum required number of rows in the object for which HCC ratio is to be estimated'

         WHEN -1 THEN 'To indicate the use of all the rows in the object to estimate HCC ratio'

         WHEN 1 THEN 'Identifies the object whose compression ratio is estimated as of type table'

         ELSE 'Unknown Compression Type'

       END AS Compression_type,

       n as num_rows

FROM   (SELECT comp_type,

               Count(*) n

        FROM   (SELECT

               dbms_compression.Get_compression_type('SCOTT', 'TABCOMP', ROWID)

                       AS comp_type

                FROM  scott.tabcomp)

        GROUP  BY comp_type);

 

 

附录4:查看压缩表所真正使用的blocks数量:

WITH blocks AS (

    SELECT COUNT(*) usedblocks, AVG(rowcount) avgrowsperusedblock

    FROM (

        SELECT

            DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),

            DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),

            COUNT(*) rowcount

        FROM &&tablename

        GROUP BY

            DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),

            DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

        )

)

SELECT extents, blocks allocatedblocks, usedblocks, avgrowsperusedblock

FROM blocks, user_segments

WHERE segment_name = '&tablename';



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

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1642270