从底向上第五篇--了解表的压缩属性

sundog315

关于SYS用户无法使用insert /*+ append */ 压缩数据，做了如下的测试：

SQL> create tablespace test_assm datafile 'D:\oracle\app\oradata\test\test_assm01.dbf' size 5m autoextend on next 1m extent management local segment space management auto;                                                          -- 创建ASSM表空间

表空间已创建。

SQL> create user test identified by test;

用户已创建。

SQL> grant connect,dba to test;

授权成功。

SQL> conn test/test
已连接。
SQL> create table t tablespace test_assm compress as select * from dba_objects where 1=0;          --用户TEST，建立在ASSM表空间TEST_ASSM上

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71890行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0                   309            0 ENABLED  BASIC                              --压缩了

SQL> alter table t move;

表已更改。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0                   309            0 ENABLED  BASIC

SQL> conn / as sysdba
已连接。

SQL> create tablespace test_manu datafile 'D:\oracle\app\oradata\test\test_manu01.dbf' size 5m autoextend on next 1m extent management local segment space management manual;                                                                   --建立MSSM表空间

表空间已创建。

SQL> conn test/test
已连接。
SQL> create table t tablespace test_manu compress as select * from dba_objects where 1=0;                      --用户TEST,建立在MSSM表空间test_manu上

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71890行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        295            0 ENABLED  BASIC                                                 --压缩了

SQL> alter table t move;

表已更改。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        295            0 ENABLED  BASIC

SQL> conn / as sysdba
已连接。
SQL> create table t tablespace test_manu compress as select * from dba_objects where 1=0;               --SYSTEM用户，建立在MSSM表空间TEST_MANU上

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71891行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        295            0 ENABLED  BASIC

SQL> create table t tablespace test_assm compress as select * from dba_objects where 1=0;               --SYSTEM用户，建立在ASSM表空间TEST_ASSM上

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71891行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0                   309            0 ENABLED  BASIC

SQL> create table t tablespace system compress as select * from dba_objects where 1=0;                    --SYSTEM用户，建立在MSSM表空间SYSTEM上

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71891行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。


SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        919            0 ENABLED  BASIC                              --没有压缩

SQL> conn test/test
已连接。
SQL> drop table t;

表已删除。

SQL> create table t tablespace system compress as select * from dba_objects where 1=0;                        --TEST用户，建立在MSSM表空间SYSTEM上

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71891行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        295            0 ENABLED  BASIC                                                 --压缩了

SQL> select tablespace_name,segment_space_management,compress_for from dba_table
spaces;

TABLESPACE_NAME                SEGMEN COMPRESS_FOR
------------------------------ ------ ------------------------
SYSTEM                         MANUAL
SYSAUX                         AUTO
UNDOTBS1                       MANUAL
TEMP                           MANUAL
USERS                          AUTO
TEST_ASSM                      AUTO
TEST_MANU                      MANUAL

已选择7行。

结论：对于sys用户，如果表建立在SYSTEM表空间上，则无法通过insert /*+ append */直接路径插入来压缩数据。除此之外，sys用户建立在其他表空间，以及其他用户建立在任何表空间的压缩表都可以通过insert /*+ append */ 直接路径插入来压缩数据。换句话说，与ASSM，MSSM没有关系。

不知道这是不是个BUG？反正文档里没有说SYS用户与其他用户有区别。

[ 本帖最后由 sundog315 于 2010-4-12 20:18 编辑 ]

Yong Huang

Thank you for your comprehensive test.

> 结论：对于sys用户，如果表建立在SYSTEM表空间上，则无法通过insert /*+ append */直接路径插入来压缩数据。除此之外，sys用户建立在其他表空间，以及其他用户建立在任何表空间的压缩表都可以通过insert /*+ append */ 直接路径插入来压缩数据。

Or simply, [1] if the user is SYS, direct path insert into [2] SYSTEM tablespace will not compress data. Otherwise (i.e. one of the two conditions is not met), direct path insert compresses data.

> 换句话说，与ASSM，MSSM没有关系。

Correct. But your test seems to show ASSM does not compress as efficiently as MSSM. It's another interesting little fact.

> 不知道这是不是个BUG？反正文档里没有说SYS用户与其他用户有区别。

Could be. If anybody finds the bug number, please post here.

Yong Huang

wang5

11.2的高级压缩 要额外购买
按CPU付费 一个cpu大概20000美金

sundog315

原帖由 Yong Huang 于 2010-4-13 01:22 发表
Thank you for your comprehensive test.

> 换句话说，与ASSM，MSSM没有关系。

Correct. But your test seems to show ASSM does not compress as efficiently as MSSM. It's another interesting little fact.

Yong Huang

差异在于ASSM的FIRST LEVEL BITMAP BLOCK，

SQL> select extent_id,file_id,block_id from dba_extents where owner='TEST' and s
egment_name='T';

EXTENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
         0          6        512
         1          6        520
         2          6        528
         3          6        536
         4          6        544
         5          6        552
         6          6        560
         7          6        568
         8          6        576
         9          6        584
        10          6        592

EXTENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
        11          6        600
        12          6        608
        13          6        616
        14          6        624
        15          6        632
        16          6        640
        17          6        768

已选择18行。

SQL> alter system dump datafile 6 block 513;

系统已更改。

Dump of Second Level Bitmap Block
   number: 12      nfree: 2       ffree: 10     pdba:     0x01800202
   Inc #: 0 Objd: 73521
  opcode:0
xid:
  L1 Ranges :
  --------------------------------------------------------
   0x01800200  Free: 1 Inst: 1
   0x01800210  Free: 1 Inst: 1
   0x01800220  Free: 1 Inst: 1
   0x01800230  Free: 1 Inst: 1
   0x01800240  Free: 1 Inst: 1
   0x01800250  Free: 1 Inst: 1
   0x01800260  Free: 1 Inst: 1
   0x01800270  Free: 1 Inst: 1
   0x01800280  Free: 1 Inst: 1
   0x01800281  Free: 1 Inst: 1
   0x01800300  Free: 5 Inst: 1
   0x01800301  Free: 5 Inst: 1
  
一共12个L1，在加额外的2个头块（ASSM 3个头块，MSSM1个头块），一共多14个块。
309-295=14；