Oracle 11g Feature – Compress for OLTP
Oracle Corporation introduced a few nice features in Oracle 11g. In this blog, I will explain one of 11g features, Advanced Compression.
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.
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
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.
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.