oracle 11g对于表压缩改进

最新推荐文章于 2021-07-19 20:06:31 发布

cuiye1492

最新推荐文章于 2021-07-19 20:06:31 发布

阅读量275

点赞数

文章标签：数据库

测试目的：测试11g压缩性能

测试用表

1. test表为导入数据表

2. cm 表为普通表 未设置compress，普通insert插入

3. cm_cmp表 为设置compress,普通insert插入

4. cm_app表 为设置compress，insert apped 插入

5. cm_ldr表 为设置compress,sqlldr导入

6. cm_for_all 表设置compress for all operations ，普通insert插入

具体测试过程

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production

SQL> conn xh/a831115

Connected.

SQL> show user

USER is "XH"

SQL> create table test (a char(20),b char(20));

Table created.

SQL> declare

2 begin

3 for i in 1..1000 loop

4 insert into test values('a','b');

5 end loop;

6 for i in 1..1000 loop

7 insert into test values('a'||i,'b'||i);

8 end loop;

9 commit;

10 end;

11 /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('XH','TEST');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME ,num_distinct,num_nulls from user_tab_col_statistics where table_name='TEST';

COLUMN_NAME NUM_DISTINCT NUM_NULLS

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

A 1001 0

B 1001 0

SQL> select num_rows from user_tables where table_name='TEST';

NUM_ROWS

----------

2000

Test表有a,b字段共2000 行，其中重复字段1000行，按压缩表性质数据字段原位留指针,指向块头的符号表，此表理想压率应接近40%，使用测试表方便直观估计最佳压缩比率了解压缩性质，压缩算法未变.

建立测试表

SQL> create table cm as select * from test where 0=1;

Table created.

SQL> create table cm_cmp compress as select * from test where 0=1;

Table created.

SQL> create table cm_app compress as select * from test where 0=1;

Table created.

SQL> create table cm_ldr compress as select * from test where 0=1;

Table created.

SQL> create table cm_for_all compress for all operations as select * from test where 0=1;

Table created

查看表压缩性质

SQL> select table_name,num_rows,compression,compress_for from user_tables;

TABLE_NAME NUM_ROWS COMPRESS COMPRESS_FOR

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

TEST 2000 DISABLED

XHT 67424 DISABLED

CM DISABLED

CM_CMP ENABLED DIRECT LOAD ONLY

CM_APP ENABLED DIRECT LOAD ONLY

CM_LDR ENABLED DIRECT LOAD ONLY

CM_FOR_ALL ENABLED FOR ALL OPERATIONS

7 rows selected.

普通insert 插入 cm表（表未设置compress）

SQL> insert into cm select * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> col segment_name for a30

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM';

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS

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

CM 4 3657 8

CM 4 3705 8

SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM);

COUNT(DISTINCTBLOCK#)

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

结果：2个区，实际使用13个块

对cm表进行move compress

SQL> alter table cm move compress;

Table altered.

SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(owed) file#,dbms_rowid.rowid_block_number(owed) block# ,owed from CM);

COUNT(DISTINCTBLOCK#)

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name=’CM’;

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS

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

CM 4 3745 8

CM 4 3753 8

结果：2个区，实际使用8个block，可以看到最佳可以压缩到8个block

对cm_cmp表（表设置为compress），进行普通insert

SQL> insert into cm_cmp select * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_CMP';

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS

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

CM_CMP 4 3665 8

CM_CMP 4 3713 8

SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_CMP);

COUNT(DISTINCTBLOCK#)

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

结果：2个区，实际占用12个block ，针对表设置compress后普通insert 只压缩1个block(version 11g)

Cm_app表执行append insert

SQL> insert /*+append*/into cm_app select * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name=’CM_APP’;

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS

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

CM_APP 4 3673 8

CM_APP 4 3721 8

SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(owed) file#,dbms_rowid.rowid_block_number(owed) block# ,owed from CM_APP);

COUNT(DISTINCTBLOCK#)

结果：2个区，实际使用8个block

Cm_ldr表进行sqlldr导入

[oracle@vm11g sqlldr]$ vi cm_ldr.ctl

load data

infile cm_ldr.dat

append

into table cm_ldr

FIELDS TERMINATED BY WHITESPACE

(A,B)

[oracle@vm11g sqlldr]$ sqlldr xh/a831115 direct=y

control = cm_ldr.ctl

SQL> select count(*) from cm_ldr;

COUNT(*)

----------

2000

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_LDR';

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS

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

CM_LDR 4 3681 8

CM_LDR 4 3729 8

SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_LDR);

COUNT(DISTINCTBLOCK#)

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

结果：2个区 ,实际使用8个block与append一致

Cm_for_all表普通insert

SQL> insert into cm_for_all select * from test;

2000 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_FOR_ALL';

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS

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

CM_FOR_ALL 4 3697 8

CM_FOR_ALL 4 3737 8

SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_FOR_ALL);

COUNT(DISTINCTBLOCK#)

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

结果：针对设置成compress for all的表普通insert压缩效果也是很理想，2个区，实际占用9个block

测试11g compress是否对执行UPDATE 将造成row migrate 表变大进行解决.

SQL> analyze table cm compute statistics;

Table analyzed.

SQL> select num_rows,chain_cnt from dba_tables where table_name='CM' ;

NUM_ROWS CHAIN_CNT

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

2000 0s

现在表无行迁移

SQL> update cm set A='AA';

2000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> analyze table cm compute statistics;

Table analyzed.

SQL> select num_rows,chain_cnt from dba_tables where table_name='CM';

NUM_ROWS CHAIN_CNT

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

2000 994

表出现大量行迁移，通过dump block看到 nrid: 0x01000944.0~~~~ROW migrate 指向迁移到的block(通过NRID 找到 迁移行所在的block)

SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM';

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS

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

CM 4 3745 8

CM 4 3753 8

CM 4 3657 8

可以看到 多了一个区

综合结果：

在11g上针表设置compress后默认是ENABLED DIRECT LOAD ONLY，既只有通过直接路径插入append ,sqlldr才可以压缩，压缩比率与正常move compress最佳比率一致，apped ,sqlldr无明显区别（oracle按插入类型分类，直接插入类型压缩算法一致）,当表设置compress for all operations后，普通插入既可压缩，但压缩比率没有直接插入压缩比率好，但结果接近，这个新特性比较理想，但11g仍然在解压缩时候造成row migrate。