ORACLE表启用压缩导致的UNDO量暴增测试记录

由于数据量过大，存储不足，计划启用ORACLE高级压缩以减少存储占用，在测试过程中发现启用压缩后，INSERT产生的UNDO量暴增，这里简单测试记录一下。

1、创建一个100万记录的源表

SQL> create table dbmt.test_TESTTAB_16_1_tmp tablespace USER9 as select * from test.TESTTAB202112 partition(TESTTAB_16_1) where rownum<1000000;

Table created.

SQL> @seg dbmt.test_TESTTAB_16_1_tmp

SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
   668 DBMT                 TEST_TESTTAB_16_1_TMP                                         TABLE                CDR9                                42752       2122     840002

2、创建未压缩表

create table dbmt.test_nocompress as select * from dbmt.test_TESTTAB_16_1_tmp where 1=0;

3、创建压缩表

create table dbmt.test_compress as select * from dbmt.test_TESTTAB_16_1_tmp where 1=0;
alter table dbmt.test_compress move compress for oltp;

4、分session测试ISNERT
—未压缩
insert into dbmt.test_nocompress select * from dbmt.test_TESTTAB_16_1_tmp;

—压缩
insert into dbmt.test_compress select * from dbmt.test_TESTTAB_16_1_tmp;

5、耗时
INSERT 未压缩(100万行）
SQL> insert into dbmt.test_nocompress select * from dbmt.test_TESTTAB_16_1_tmp;

999999 rows created.
Elapsed: 00:00:08.94

—INSERT 压缩(100万行）
SQL> insert into dbmt.test_compress select * from dbmt.test_TESTTAB_16_1_tmp;
999999 rows created.
Elapsed: 00:00:51.82

6、未压缩666M,压缩后409M,空间减少38%
SQL> @seg dbmt.test_nocompress

SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
666 DBMT TEST_NOCOMPRESS TABLE CDR6 42624 2317 612674
SQL> @seg dbmt.test_compress

SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
407 DBMT TEST_COMPRESS TABLE CDR6 26048 2316 624002
SQL>

7、对比UNDO使用量

SQL> @trans

   SID    SERIAL# USERNAME    TADDR            SES_ADDR          USED_UBLK  USED_UREC    0xFLAG STATUS                        START_DATE            XIDUSN    XIDSLOT     XIDSQN XID              PRV_XID          PTX_XID
   420       9231 SYS         0000000AEA94BBC8 0000000B193927B8      76007     208414      1E03 ACTIVE                        20211217 15:23:04        590          1     756270 4E0201002E8A0B00 0000000000000000 0000000000000000
   228      15763 SYS         0000000AEA9AC598 0000000B5935B538        454      78498      1E03 ACTIVE                        20211217 15:23:11        173         35    1966072 AD002300F8FF1D00 0000000000000000 0000000000000000

压缩表INSERT使用了76007 UNDO BLOCKS
非压缩表INSERT使用了454 UNDO BLOCKS
压缩表INSERT使用的UNDO量是未压缩的167倍
76007/454=167.4163

INSERT 666M的数据UNDO占用用了1159M
SQL> select 7600716384/1024/1024/1024 from dual;
7600716384/1024/1024/1024
1.15977478
8、总结
1、INSERT效率,比未压缩慢了6倍
2、压缩比例，压缩后409M,空间减少38%
3、UNDO占用，正常INSERT UNDO占用很少,压缩后INSERT UNDO增加167倍。