測試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 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導入
[[email protected] sqlldr]$ vi cm_ldr.ctl
load data
infile cm_ldr.dat
append
into table cm_ldr
FIELDS TERMINATED BY WHITESPACE
(A,B)
~
[[email protected] 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）