Skip to Content
Author's profile photo Bala Prabahar

Oracle 11g Feature – Compress for OLTP

Introduction 

Oracle Corporation introduced a few nice features in Oracle 11g. In this blog, I will explain one of 11g features, Advanced Compression.

Licensing

Customers who acquired the Oracle license directly from Oracle need to purchase Advanced Compression option separately.

What is Advanced Compression?

An Oracle whitepaper “Advanced Compression with Oracle Database 11g Release 2” released in September 2009 states:

Oracle’s OLTP Table Compression uses a unique compression algorithm specifically designed to work with OLTP application. The algorithm works by eliminating duplicate values within a database block, even across multiple columns. Compressed blocks contain a structure called a symbol table that maintains compression metadata. When a block is compressed, duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table. Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table. Through this innovative design, compressed data is self-contained within the database block as the metadata used to translate compressed data into its original state is stored in the block. When compared with competing compression algorithms that maintain a global database symbol table, Oracle’s unique approach offers significant performance benefits by not introducing additional I/O when accessing compressed data.”

What are the differences between Basic Compression available since 9i and Advanced Compression released in 11g?

Basic compression works only for Direct Path Inserts, Direct Path SQL *Loader operations and CREATE TABLE… AS SELECT statements whereas Advanced Compression works for all DML operations in addition to the operations supported by Basic Compression.

Advantages

1) Reduced Disk Space Requirement

2) I/O Speed Up

3) Reduced Buffer Cache Requirement

4) Reduced Network Traffic

5) Reduction in backup time

6) Reduction in time for System Copies

7) Reduction in Backup Tape Requirements (100GB savings in disk space would potentially reduce 3TB of tape space assuming backup retention period of 30 days

Disadvantages

1) Increased CPU Utilization

As data is uncompressed after reading from storage sub system, CPU utilization would go up.

2) Hot Blocks

Due to increased number of records per block, the probability of hot blocks might increase.

3) Unpredictable response time

Even though compression is triggered in the background when threshold PCTFREE is reached, the DML operation which triggered the compression would take longer.

Factors to consider before implementing Advanced Compression

1) Additional cost to purchase Advanced Compression License

2) Increased CPU Utilization

As stated before, CPU utilization would go up when Advanced compression is implemented. We used CPH (Central Performance History) reports to check current CPU Utilization. They showed not more than 7-8% of average CPU utilization during any hour for 30 days. This meant we have a plenty of unused CPU cycles.

image

As you can see, average CPU utilization for the month of May is less than 5% in two DB servers in our landscape. Since average CPU utilization for any given hour is not more than 12%, we believe we have enough CPU power to handle additional overheads introduced by advanced compression.

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann
      The blocks are not uncompressed after they are read from disk. Instead the data blocks are kept in their compressed state in the buffer cache.

      This also means that there are fewer blocks to be handled in memory (e.g. scans that are done in-memory).

      The additional CPU-load caused by the compression/decompression of the data (which is basically a kind of quick lookup) may be even overcompensated by the savings generated by handling far fewer blocks.

      regards,
      Lars

      Author's profile photo Bala Prabahar
      Bala Prabahar
      Blog Post Author
      Lars,
      Thanks for your comments. This feature really looks attractive. We are currently evaluating it in our sandbox environment. I will write another blog with our experiences.

      Regards,
      Bala