oracle 11g對於表壓縮改進

fufuh2o發表於2010-01-27
Oracle

測試目的：測試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#)

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

                   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），進行普通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#)

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

                   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/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#)

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

                    8

 

結果：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#)

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

                    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）

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-626159/，如需轉載，請註明出處，否則將追究法律責任。

相關文章