测试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)