oracle 11g压缩性能

测试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 11gEnterpriseEdition 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 operationsas 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#)
---------------------
                  13
结果：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#)
                   8
 

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），进行普通insertSQL> 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#)
---------------------
                  12
 

结果：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#)
                   8
结果：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/a831115direct=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#)
---------------------
                   8
 

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

 

Cm_for_all表普通insertSQL> 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#)
---------------------
                   9
 

结果：针对设置成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。

 

 

 

 数据结果：
普通表普通insert，为13个block，move compress得到最佳压缩为8个block，压缩比为38%
表设置compress后普通insert为12个block，压缩比为7%
表设置compress后append ,为8个block，压缩比为38%
表设置compress后sqlldr，为8个block，压缩比为38%
表设置compress for all operations后普通insert，为9个block，压缩比为30%
11gr2有了for oltp将得到更好的压缩效果（针对普通insert）