One significant advantage is Oracle’s ability to read compressed blocks directly without having to first uncompress the block. Therefore, there is no measurable performance degradation for accessing compressed data.
I would like to pick up where we left off from my Advanced Compression Part 1 Blog. In this blog installment we will discuss and learn what advanced compression is in Oracle, and how we implement it in the Oracle databases.
What is Advanced Compression in Oracle?
Oracle has been a pioneer in database compression technology. While table compression was introduced already in Oracle 9i, nowadays, with Oracle 12c already available, it has become a mainstream feature used not only in Data Warehouses, but also in Online Transaction Processing (OLTP) databases as well. There are four types of compression that we can use as of now:
- Basic table compression
- OLTP table compression
- Index compression
- Hybrid columnar compression (Exadata feature)
Basic table compression and index compression are the core database features that are available for free, whereas OLTP table compression or Advanced Compression (ACO) comes as an extra separately licensed option. Oracle compresses data by eliminating duplicate values within a data-block.
Any repetitive occurrence of a value in a block is replaced by a symbol entry in a “symbol table” within the data block. For instance, if the surname=’Doe’ is repeated three times within a data block, it will only be stored once and for the other two occurrences, a symbol entry will be stored in the symbol table. It is very important to know that every data block is self-contained and sufficient to rebuild the uncompressed form of data.
The space savings come from eliminating redundant copies of data values in the table. The effects of table compression are transparent to a user or an application developer. Developers access a table the same way regardless of whether a table is compressed or not, SQL queries don't have to change once you decide to compress a table. Table compression settings are usually configured and managed by database administrators or architects, with little involvement from developers or users.
Why Advanced Compression?
Let us take a look, at how and when Oracle compresses data blocks.
- Oracle compresses blocks in batch mode rather than compressing data every time a write operation takes place.
- A newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed.
- Subsequently, as more data is added to the block and the threshold is again reached, the entire block is recompressed to achieve the highest level of compression.
- This process repeats until Oracle determines that the block can no longer benefit from further compression. Only transactions that trigger the compression of the block will experience the slight compression overhead. Therefore, a majority of OLTP transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.
So, by using the above procedure, Oracle provides the advantages outlined below with Advanced Compression.
- One significant advantage is Oracle’s ability to read compressed blocks directly without having to first uncompress the block. Therefore, there is no measurable performance degradation for accessing compressed data.
- In many cases performance may improve due to the reduction in I/O since Oracle will have to access fewer blocks.
- Further, the buffer cache will become more efficient by storing more data without having to add memory.
- One must say, there is additional work performed while writing data, making it impossible to eliminate performance overhead for write operations. However, Oracle has put in a significant amount of work in order to minimize this overhead for OLTP Table Compression.
- Last but not the least, the Advanced Compression product was part of the ULA (Unlimited License Agreement) between the customer and Oracle, so this feature could be used for free.
How to Implement Advanced Compression?
Oracle provides a tool called the Oracle Advanced Compression Advisor and it is easily available in My Oracle Support. It provides an estimate of the compression ratio that can be realized through the use of the Advanced Compression option. This estimate is based on analysis of sample of data and provides a good estimate of the actual results one may obtain once the OLTP Table compression feature is implemented.
As listed in ORACLE whitepapers, Advanced Compression can be implemented in OLTP tables in the following three ways.
ALTER TABLE <TABLE_NAME> COMPRESS FOR OLTP - This approach will enable OLTP Table Compression for all future DML -- however, the existing data in the table will remain uncompressed.
Online Redefinition (DBMS_REDEFINITION) - This approach will enable OLTP Table Compression for future DML and also compress existing data. DBMS_REDEFINITION keeps the table online for both read/write activity during the migration.
ALTER TABLE <TABLE_NAME> MOVE COMPRESS FOR OLTP - This approach will enable OLTP Table Compression for future DML and also compress existing data. While the table is being moved is online for read activity but has an exclusive (X) lock – so all DML will be blocked until the move command completes.
Advanced Compression in eBS
It does not take any specific approach for compressing OLTP tables for eBS Suite. The method is similar to compressing non-OLTP tables. The first thing is to identify the candidates for OLTP compression. Those are tables that have big sizes and were not updated for more than a year or so. However, there is no straight forward way to achieve this unless audit is enabled on database level.
Another option is to directly compress large tables, however this requires intensive testing and planning, mainly because large table compression might take a long time and also have an impact on performance. The recommendation is to check what is being purged on the system and what is not, and whether it’s worth going for compression at all.
There exist quite a few large objects in the database, which are not queried or updated during a transaction. Therefore, they do not cause significant impact on the performance of the eBS system. Hence it’s important that we evaluate what we may gain post compression. Once that is decided, one can search for objects that can be compressed, evaluate downtime possibilities and accordingly explore the above-discussed approaches for enabling Advanced Compression.
Advanced Compression works transparently with both Oracle eBS Suite Release 11i and 12. There’s no additional database or eBS Suite patches required. No special configuration options are required either.
In general, apart from the OLTP tables, tables that hold Automatic Workload Repository (AWR) data grow rapidly and amount to substantial database size.
Considerations for Enabling Advanced Compression in eBS
In addition to reducing the E-Business Suite database's size, here are some additional things that one can expect when enabling this option:
- Enabling OLTP compression doesn't compress existing data. One must do a table level reorg to free up space. The future data will automatically be in a compressed state depending upon which compression options is selected. In practice, we're more likely to do this at the partition level, especially with time-based partitioning.
- The time required to do any reorganizations will depend upon the amount of real data that one may have. The mileage will vary in two different environments.
In general, EBS performance is expected to improve, since the queries will benefit from improved I/O and memory efficiency. In the third installment of this blog series we will see an actual implementation of Advanced Compression techniques for a table and we will demonstrate how much benefit it can provide in regards to storage and performance.
For any questions on Part 2, click below:
To read Part 1 of the blog Click Here...