oracle 压缩 插入速度,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)