Installation guide

Appendix 4: Oracle Advanced Compression

Many customers are looking for solutions that provide a means for reducing the size of their rapidly growing

databases without negatively affecting their end user performance. Oracle 11gR2 offers integrated database

compression to address this requirement.

We often think of compression as being a trade-off between performance and storage: compression reduces the

amount of storage required, but the overhead of compressing and decompressing makes things slower. However,

while there is always some CPU overhead involved in compression the effect on table scan I/O can be favorable,

since if a table is reduced in size it will require fewer I/O operations to read it.

Prior to 11g, table compression could only be achieved when the table was created, rebuilt or when using direct load

operations. However, in 11gR2, the Advanced Compression option allows data to be compressed when manipulated

by standard DML (Data Manipulation Language). The data compression feature in Oracle 11gR2 Enterprise Edition

reduces the size of tables and indexes while providing full row level locking for updates. There are two types of

compression.

1. Row compression enables storing fixed-length data types in a variable-length storage format.

2. Page compression is a superset of row compression. It minimizes the storage of redundant data on the page by

storing commonly-occurring byte patterns on the page once, and then referencing these values for respective

columns.

Oracle’s Advanced Compression offers three distinct levels: low, medium, and high. HP and Oracle recommend

using the “low” method for best overall OLTP workload performance when data compression is desired. Oracle has

provided a compression algorithm specifically designed to work with OLTP type workloads. This recommendation is

based upon tests performed by HP and Oracle on industrial-standard x86 hardware (see the reference at the end of

this document). Users may wish to evaluate other compression options to determine if the “medium” or “high” setting

offers superior performance for their specific workload.

As one would expect, Oracle Advanced Data Compression was very effective at reducing disk utilization of

traditional storage arrays. The result was improved large data scans from storage into the database instance for

processing and reduced I/O wait overhead. Using table compression can reduce disk and memory usage, often

resulting in better scale-up performance for read-only operations. Table compression can also speed up query

execution by minimizing the number of round trips required to retrieve data from the disks. Compressing data

however imposes a performance penalty on the load speed of the data. The overall performance gain typically

outweighs the cost of compression. If you decide to use compression, consider sorting your data before loading it to

achieve the best possible compression rate. Testing conducted by HP’s Oracle Alliances team showed that Advanced

Data Compression scaled linearly across the full range of CPU cores on HP 8-socket servers. The increases in CPU

requirements from using Advanced Compression were around 5-10% for most tests.