Oracle 数据压缩(Compression) 技术 说明

一．官网说明

1.1 Oracle 11g Advanced Compression

https://www.360docs.net/doc/f318718737.html,/demo_c284_i515.html

Oracle 11g EE版本中只有：Basic T able Compression ，而AdvanceCompression Feature需要单独购买。

11g Advanced Compression 有如下特性：

1. Compression for T able Data

--支持了DML 语句的compress，下面会重点关注。

2. Compression for File Data

3. Compression for Backup Data

--包括RMAN和expdp/impdp.对数据泵，compress 是inline的，在impdp时不需要进行解压缩，直接导入即可。

4. Compression for Network Traffic

--在DG中使用，Redo在传输中被压缩和解压缩。

关于11g Advanced Compression，参考Oracle 的compression 白皮书：

https://www.360docs.net/doc/f318718737.html,/technetwork/database/storage/advanced-compression-whitepaper-130502.pdf

这里我们只看Compressionfor T able Data。

The OracleDatabase 11g Advanced Compression Option introduces a comprehensive set ofcompression capabilities to help customers maximize resource utilizationand reduce costs. It allows IT administrators to significantly reduce theiroverall database storage footprint by enabling compression for all types ofdata – be it relational (table), unstructured (file), or backup data. Althoughstorage cost savings are often seen as the most tangible benefit ofcompression, innovative technologies included in the Advanced CompressionOption are designed to reduce resource requirements and technology costs forall components of your IT infrastructure, including memory and networkbandwidth.

1.1 Compression for Table Data

Oracle has beena pioneer in database compression technology. Oracle Database 9i introducedBasic Table Compression several years ago that compressed data that was loadedusing bulk load operations. Oracle Database 11g Release 1 introduced a newfeature called OLTP T able Compression that allows data to be compressed duringall types of data manipulation operations, including conventional DML such

asINSERT and UPDATE. In addition, OLTP Table Compression reduces the associatedcompression overhead of write operations making it suitable for transactionalor OLTP environments as well. OLTP Table Compression, therefore, extends thebenefits of compression to all application workloads.

--Oracle 是数据压缩技术的先驱，在Oracle 9i中就引入了Basic Table Compression。9i中是使用bulk 进行装载时进行压缩。到Oracle 11g，Table Compress特性进一步增强。

It should benoted that Basic Table Compression is a base feature of Oracle Database 11gEnterprise Edition (EE). OLTP Table Compression is apart of the Oracle Advanced Compression option, which requires a license inaddition to the Enterprise Edition.

注意：

Oracle 11g EE版本中只有：Basic T able Compression ，而AdvanceCompression Feature需要单独购买。

1.2 OLTP Table Compression

Oracle’s OLTPT able Compre ssion uses a unique compression algorithm specifically designed towork with OLTP applications. The algorithm works by eliminating duplicatevalues within a database block, even across multiple columns. Compressed blockscontain a structure called a symbol table that maintains compression metadata.When a block is compressed, duplicate values are eliminated by first adding asingle copy of the duplicate value to the symbol table. Each duplicate value isthen replaced by a short reference to the appropriate entry in the symboltable.

--Oracle 表压缩使用一个唯一的压缩算法。该算法用来消除一个database block中的重复值，该重复值甚至可以跨多个列。被压缩的blocks包含一个叫作symbol table的structure，该structure 用来维护压缩的元素。当一个block 被压缩时，字段值第一次该被copy到symbol table中，然后

每次的重复值都是被一个short reference 代替，该reference 指向symbol table 中对应的entry。

Through thisinnovative design, compressed data is self-contained within the database blockas the metadata used to translate compressed data into its original state isstored in the block. When compared with competing compression algorithms thatmaintain a global database symbol table, Oracle’s unique approach offerssignificant performance benefits by not introducing additional I/O whenaccessing compressed data.

通过这张图，可以清楚的看到compressed的block 比not compressed 的block 多了一个symbol Table。正式因为该structure的使用，才使数据占用的空间降低很多。

1.3 Benefits of OLTP Table Compression

The compressionratio achieved in a given environment depends on the nature of the data beingcompressed; specifically the cardinality of the data. In general, customers canexpect to reduce their storage space consumption by a factor of 2x to 4x byusing the OLTP Table Compression feature. That is, the amount of space consumedby uncompressed data will be two to four times larger than that of thecompressed data.

--压缩率由被压缩的数据性质决定，特别是重复值的数量。重复值越多，压缩率越高。一般来说，通过压缩，可以降低2x 到4x的空间的消耗。但是在uncompress时，还是需要增加原来的空间。

The benefits ofOLTP Table Compression go beyond just on-disk storage savings. One significantadvantage is Oracle’s ability to read compressed blocks directly without havingto first uncompress the block. Therefore, there is no measurable performance degradationfor accessing compressed data. In fact, in many cases performance may improvedue to the reduction in I/O

since Oracle will have to access fewerblocks. Further, the buffer cache will become more efficient by storing moredata without having to add memory.

--OLTP Table Compression 的好处不仅仅是存储空间的节省，另一个重要的影响Oracle 直接读压缩数据块的能力，因为不需要读uncompress 的block，在重复值越多的情况下，读compress 会降低I/O，从而提高性能，并且buffer cache因为存储更多的数据而更高效。

1.4 Minimal Performance Overhead

As stated above,OLTP Table Compression has no adverse impact on read operations. There isadditional work performed while writing data, making it impossible to eliminateperformance overhead for write operations. However, Oracle has put in asignificant amount of work to minimize this overhead for OLTP TableCompression. Oracle compresses blocks in batch mode rather than compressingdata every time a write operation takes place. A newly initialized blockremains uncompressed until data in the block reaches an internally controlledthreshold. When a transaction causes the data in the block to reach thisthreshold, all contents of the block are compressed. Subsequently, as more

datais added to the block and the threshold is again reached, the entire block isrecompressed to achieve the highest level of compression.

--正如上面锁描述，T able Compression 对read 没有不利的影响。但对write 操作时需要做一些附加的操作，正因如此，对compress block 不适合进行写操作。

Oracle 批处理的compress要优于每次写操作时进行压缩。当一个block 初始化时会保持uncompress状态，直到数据接近block 控制阀值，当某个事务导致数据达到这个threshold，block 里的所有数据都会被compressed。随后，又更多的数据被添加进来，再次接近阀值，在次被压缩，直到整个block 达到最高的compression。

This processrepeats until Oracle determines that the block can no longer benefit fromfurther compression. Only transactions that trigger the compression of theblock will experience the slight compression overhead. Therefore, a majority ofOLTP transactions on compressed blocks will have the exact same performance asthey would with uncompressed blocks.

上图显示了block 不断被压缩的过程。

1.5 Migration and Best Practices

For new tablesand partitions, enabling OLTP T able Compression is as easy as simply CREATEingthe table or partition and specifying “COMPRESS FOR OLTP”. See the examplebelow:

CREATE TABLE emp(emp_id NUMBER, first_name VARCHAR2(128), last_name VARCHAR2(128)) COMPRESS FOROLTP;

--对于新表或者分区，可以在创建时指定参数：COMPRESS FOR OLTP.

For existingtables and partitions, there are three recommended approaches to enabling OLTPTable Compression:

--对于已经存在的表或者分区，可以使用如下三种方法来实现Table Compress：

1.5.1. ALTER TABLE … COMPRESS FOR OLTP

TableCompression for all future DML -- however, the existing data in the table willremain uncompressed.

--启动compress后，之后的所有DML操作都会进行compress，但是对于已经存在的数据，会继续保持uncompressed。

1.5.

2. Online Redefinition (DBMS_REDEFINITION)

P T ableCompression for future DML and also compress existing data. UsingDBMS_REDEFINITION keeps the table online for both read/write activity duringthe migration. Run DBMS_REDEFINITION in parallel for best performance.

--在线重定义支持以后的DML的压缩，同时也会compress 已经存在的数据。

y maintained during the sync (refresh) operation so there is nointerrupt in the use of the indexes during, or after, the online redefinition.The only exception is when online redefinition is used for redefining apartition -- the global index is invalidated and needs to be rebuilt after theonline redefinition.

--在线重定义操作期间会clone索引到临时表。这个和索引的online rebuild 类似。但是online redefinition 会导致分区表的全局索引失效，需要在操作完成后进行重建。

参考我的blog：

Oracle alterindex rebuild 与ORA-08104 说明

https://www.360docs.net/doc/f318718737.html,/tianlesoftware/article/details/6538928

1.5.3. ALTER TABLE … MOVE COMPRESS FOR OLTP

ure DML and also compress existing data. While the table isbeing moved it is online for read activity but has an exclusive (X) lock – soall DML will be blocked until the move command completes. Run ALTER TABLE MOVEin parallel for best performance.

--该方法支持表以后的DML的压缩，同时也会已经存在的数据进行压缩，但是在move 期间会对表加上exclusive（X）锁，其他的DML操作会被block。使用并行来执行ALTER TABLEMOVE 会增加操作的效率。

-partitioned table, or of a partition of apartitioned table, into a new segment, and optionally into a differenttablespace. ALTER TABLE MOVE COMPRESS compresses the data by creating newextents for the compressed data in the tablespace being moved to -- it isimportant to note that the positioning of the new segment can be anywherewithin the datafile, not necessarily at the tail of the file or head of thefile. When the original segment is released, depending on the location of theextents, it may or may not be possible to shrink the datafile.

ndexes on the partition or table; those indexes will need to be rebuilt afterthe ALTER TABLE MOVE. Alternatively, the use of ALTER TABLE MOVE with theUPDATE INDEXES clause will maintain indexes (it places an exclusive (X) lock soall DML

will be blocked until the move command completes).

--MOVE 操作会导致分区或者表上的所有失效，需要操作结束后rebuild 索引，或者在执行MOVE操作时加上UPDATE INDEXES，来维护索引。

Below are some best practices andconsiderations for the capabilities that are included as part of the AdvancedCompression Option: test environment foreach Advanced Compression capability is where you can most closely duplicatethe production

environment– this will provide the most realistic (pre- andpost- compression) performance comparisons.

Compression enabled gives the best results where the most duplicate data isstored (low cardinality). This is especially true for backups -- greatercompression will result in less data backed up and hence shorter recovery time.Sorting data (on the columns with the most duplicates) prior to bulk loads mayincrease the compression ratio.

ycles, as compression will have additional, although minoroverhead for some DML operations.

pression based on analysis of a sample of data. It provides a good estimateof the actual results that may be obtained after implementing Oracle AdvancedCompression’s OLTP Table compression feature. Oracle Advanced CompressionAdvisor, which supports Oracle Database 9i Release 2 through 11g Release 1, isavailable for free on the Oracle Technology Network website. The AdvancedCompression Advisor is built in to Oracle Database 11g Release 2.

--OLTP T ableCompression 不支持超过255个字段的表，或者表里有LONG 类型的字段。

tion, they can use SecureFiles Compression and Deduplication to potentiallyreduce the amount of storage required for LOBs.

二．Compression 压缩说明

2.1 说明

一般来说，数据压缩对OLAP 系统更有效，因为其数据很大。但对现在的很多OLTP系统来说，数据也非常巨大，比如如下的几个用户：

OWNER SUM(BYTES/1024/1024)||'M'

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

ECHN_HAND 781433.8125M

ECHNWEB 627652.75M

STAT_PRM 559465.875M

PRM 517039.4375M

加起来也有2T多的数据，如果不使用压缩或者转对数据进行转历史，那么会对磁盘空间带来不小的压力。而且数据压缩不仅仅是对磁盘空间的减少，从某些环境下，对性能也会有提高。

Oracle 是数据压缩技术的先驱，在Oracle 9i中就引入了Basic Table Compression。

Oracle 表压缩使用唯一压缩算法。该算法用来消除一个database block中的重复值，该重复值甚至可以跨多个列。被压缩的blocks 会包含一个叫作symboltable的structure，该structure 用来维护压缩的元素。当一个block 被压缩时，字段值第一次该被copy到symbol table中，然后每次的重复值都是被一个short reference 代替，该reference 指向symbol table 中对应的entry。

压缩率由被压缩的数据性质决定，特别是重复值的数量。重复值越多，压缩率越高。一般来说，通过压缩，可以降低2x 到4x的空间的消耗。但是在uncompress时，还是需要增加原来的空间。

OLTP T ableCompression 的好处不仅仅是存储空间的节省，另一个重要的影响Oracle 直接读压缩数据块的能力，因为不需要读uncompress的block，在重复值越多的情况下，读compress 会降低I/O，从而提高性能，这种查询性能的提升体现在物理I/O和逻辑I/O上，而减少数据块扫描的同时，又降低了对CPU 资源的消耗，并且由于更少的数据块被加载到内存，所以对内存资源的消耗也会降低，从而使buffer cache可以存储更多的数据。

TableCompression 对read 没有不利的影响。但对write操作时需要做一些附加的操作，正因如此，对compress block 不适合进行写操作。

Oracle 批处理的compress要优于每次写操作时进行压缩。当一个block 初始化时会保持uncompress状态，直到数据接近block 控制阀值（PCTFREE），当某个事务导致数据达到这个threshold，block 里的所有数据都会被compressed。随后，又更多的数据被添加进来，再次接近阀值，在次被压缩，直到整个block 达到最高的compression。