Analysis Capabilities of Oracle Advanced Compression in E-Business Suite Applications

 

Data Growth Challenges

The enormous and exponential growth in data volume currently experienced by organizations introduces daunting challenges and is the biggest cost element for IT budgets across most industry verticals. Business leaders must swiftly adapt and efficiently manage their existing infrastructure to control costs yet continue to deliver extraordinary application performance while staying within budget and maintaining the bottom line.

Advanced Compression Overview

OLTP Table Compression uses a unique compression algorithm specifically designed to work with OLTP applications. This algorithm works by eliminating duplicate values within a database block and even across multiple columns. Compressed blocks contain a structure called a ‘symbol table’ that maintains the compression metadata.

First introduced in Oracle 9.2.0.1 (Table Compression), Oracle Advanced Compression is supported for use with Oracle E-Business Suite Release 11i and 12.  Advanced Compression is one of the Oracle Database 11gR1 Enterprise Edition options that seems to generate a lot of interest amongst E-Business Suite customers. Previously, blocks containing many types of chained rows could not be compressed. This limitation has been removed starting with Oracle Database 12c Release 2.

E-Business Suite Considerations

As far as E-Business Suite databases are concerned, reducing EBS database size by even a modest amount could result in big savings, when combined across all non-production E-Business Suite instances.

• Advanced Compression performs transparently with both Oracle E-Business Suite Release 11i and 12
• No additional database or E-Business Suite patches are required
• No special configuration options are required

In addition to the benefit of saving storage with OLTP Table Compression, Oracle Database can read compressed blocks directly without decompression, either on disk or in memory. Thus, there is no measurable performance degradation for accessing compressed data.

Exhibit 1: An example of database block compression, which can be enabled at the table or partition level.

The Advanced Compression option allows IT administrators to significantly reduce their overall database storage footprint including intermediary instances standing between their Development and Production stages by enabling compression for all types of data – relational (table), unstructured (file), index, and network and backup data.

But remember, the overall application performance depends on the users’ transactional mix, the amount of historical production data, as well as database server and storage configurations. Hence, it is recommended to perform detailed performance benchmarking with this option and EBS applications reference data.

What to Expect When Enabling the Advanced Compression Option

First, to measure the impact of Advanced Compression, it is recommended to collect storage consumption and performance statistics including Automatic Workload Repository reports, host CPU statistics, batch program runtimes, and end-user response times. Then, compare the expected and actual results using the following techniques in Proof-of-Concept test scenarios.

• Structured data compression (OLTP Table Compression)
• Unstructured data compression (SecureFiles Deduplication and Compression)
• Backup data compression (Data Pump Export and RMAN Compression)

Besides the obvious goal of reducing E-Business Suite database size, there are couple of things that you can expect when enabling this option:

1. Use Table Level Reorg to free up space at the partition level, especially with time-based partitioning, as enabling compression doesn’t compress existing data.
2. The speed and timing of your reorganisation will vary significantly, depending upon the real amount of data.
3. Queries will benefit from improved I/O and memory efficiency which will result in significant improvement in EBS performance, due to the reduction in I/O as fewer blocks are accessed for a given set of rows.
4. Tables that include columns with the LONG data type cannot be compressed using OLTP Table Compression as it is not supported.
5. Columns with BasicFiles LOBs will not be converted to SecureFiles LOBs and therefore will not compress.

Merits	Demerits
Reduce storage requirements	CPU overhead
Save costs	SQL plan regressions
May improve query performance	Row chaining
Reduction in I/O as fewer blocks are accessed for the given set of rows	ITL contention
Indexes will not be compressed	Not suited for high transaction table

Exhibit 2: Merits and Demerits of the Advanced Compression Option.

Suggested Best Practices

Oracle Advanced Compression can be implemented for existing database servers and new designs as well. There are several best practices identified.

1. Each Oracle Advanced Compression feature necessitates thorough testing. The best test environment is one where you can most closely duplicate the production environment; however, it is often difficult to achieve given constraints such as budget, staffing, and infrastructure limitations.
2. The best space usage reduction is achieved when Oracle Advanced Compression is enabled where the most duplicate data is stored (low cardinality). This is especially true for backups – greater compression will result in less data backed up, thus shorter recovery time.
3. The OLTP Table Compression feature often improves read performance because database blocks stay compressed when they’re read into the buffer cache which reduces memory used in the buffer cache.
4. Note, redo generation can be up to 30% higher (depending on your environment). This could result in more archive logs to be backed up, shipped to standby, and increased recovery time with Oracle Advanced Compression.
5. Although CPU overhead is typically minimal, it is recommended to implement Oracle Advanced Compression on systems with available CPU cycles – as compression will have added overhead for certain DML operations.
6. Tables can be redefined online. Queries will continue to remain associated with tables that have been converted to a compressed format.