The Advanced Compression Option includes a comprehensive set of compression features designed to maximize resource utilization and reduce costs by enabling compression for structured data – Advanced Row Compression - unstructured data - Advanced LOB Deduplication and Compression – database backups - RMAN and Data Pump - as well as Advanced Network Compression and Data Guard Redo Log Network Transport. The Advanced Compression Option also includes optimization for Flashback Data Archive - FDA - history tables, which reduce the storage requirements for using FDA to track changes to tables.In Oracle Database 12c, several new features have been added which enhance the capabilities of Oracle Database. Heat Map automatically tracks data modification times at the row and segment level, and access times at the segment level, providing unprecedented insights into how data is being accessed. Automatic Data Optimization – ADO - provides declarative syntax to automatically move and compress data based on usage statistics collected by Heat Map. Together these capabilities help implement Information Lifecycle Management (ILM) strategies.
What are the benefits of using Advanced Row Compression?
Advanced Row Compression uses a unique compression algorithm specifically designed to work with OLTP applications. 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 header. When compared with competing compression algorithms that maintain a global database symbol table, Oracle’s approach offers significant performance benefits by not introducing additional I/O when accessing compressed data.The compression ratio achieved in a given environment depends on the data being compressed, specifically the cardinality of the data. In general, organizations can expect to reduce their storage space consumption by a factor of 2x to 4x by using Advanced Row Compression. That is, the amount of space consumed by uncompressed data will be two to four times larger than that of the compressed data. The benefits of Advanced Row Compression go beyond just on-disk storage savings. One significant advantage is Oracle’s ability to read compressed blocks directly without uncompressing the blocks. This helps improve performance due to the reduction in I/O, and the reduction in system calls related to the I/O operations. Further, the buffer cache becomes more efficient by storing more data without having to add memory..
I am already using the BASIC Compression feature introduced in Oracle9i. What additional benefits do I get from Advanced Row Compression option?
Oracle Database 9i introduced Basic Compression which only compressed data that was loaded using bulk load operations. Advanced Row Compression, a feature of Advanced Compression, allows data to be compressed during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. In addition, Advanced Row Compression reduces the associated compression overhead of write operations making it suitable for transactional/OLTP environments. Advanced Row Compression, therefore, extends the benefits of compression to all application workloads. Although storage cost savings and optimization across servers (production, development, QA, Test, Backup and etc...) are often seen as the most tangible benefits, additional innovative technologies included in Advanced Compression are designed improve performance and to reduce CapEx and OpEx costs for all components of your IT infrastructure, including memory and network bandwidth as well as heating, cooling and floor-space costs.
I am already using the basic RMAN backup compression feature. In what way is the RMAN backup compression included in Advanced Compression superior to this?
Advanced Compression provides three levels of RMAN Compression: LOW, MEDIUM, and HIGH. The amount of storage savings increases from LOW to HIGH, while potentially consuming more CPU resources. Compression Level LOW provides the fastest compression algorithm and is best suited when backup is constrained by CPU. Compression Level MEDIUM provides a balance between CPU usage and compression ratio and finally, Compression LEVEL HIGH provides the best compression ratio and highest CPU utilization and is best suited when backup is constrained by network or I/O.
What are SecureFiles? What is the relationship between Advanced Compression and SecureFiles?
SecureFiles, a feature in Oracle Database, offers a ‘best-of-both-worlds’ architecture for storing unstructured content, such as documents, spreadsheets and XML files. SecureFiles is specifically engineered to deliver high performance for file data comparable to that of traditional file systems while retaining the advantages of the Oracle database. SecureFiles is designed as a superset of the ANSI standard LOB data type and offers easy migration from existing BasicFile LOBs, the precursor to SecureFiles. With SecureFiles, organizations can now manage all relational data and associated file data in Oracle using a single security/audit model, a unified backup & recovery process and perform seamless retrievals across all information. Advanced Compression has two storage optimization features that can be leveraged with SecureFiles. The first feature, Advanced LOB Deduplication, is an intelligent technology that eliminates duplicate copies of SecureFiles data. The second feature, Advanced LOB Compression, utilizes industry standard compression algorithms to further minimize the storage requirements of SecureFiles data. There are three levels of Advanced LOB compression available: LOW, MEDIUM, and HIGH. By default, Advanced LOB Compression uses the MEDIUM level, which typically provides good compression with a modest CPU overhead of 3-5%. SecureFiles Compression LOW is optimized for high performance and maintains about 80% of the compression achieved through MEDIUM, while utilizing 3x less CPU. Finally, Advanced LOB Compression HIGH achieves the highest storage savings but incurs the most CPU overhead.
What kind of technology is used to compress data?
Advanced Row Compression uses a unique compression algorithm specifically designed to work with OLTP applications. 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 optimizations has Oracle done to minimize compression overhead for OLTP applications?
Advanced Row Compression has no adverse impact on read operations. 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 to minimize this overhead for Advanced Row Compression. 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.
Does table data get decompressed before it is read?
No. Oracle has the 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 fact, 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.
How much compression can I expect by using Advanced Row Compression?
The compression ratio achieved in a given environment depends on the nature of the data being compressed; specifically the cardinality of the data. In general, customers can expect to reduce their storage space consumption by a factor of 2x to 4x by using the Advanced Row Compression feature.
What is the performance impact of using Advanced Row Compression?
For DML operations on a compressed table, Advanced Row Compression’s specialized batch algorithm keeps the performance overhead to a minimum. Internal tests at Oracle showed a minimal overhead of less than 5% CPU for a DML workload. It is important to note that Oracle compresses blocks in batch mode rather than compressing data every time a write operation takes place. When a transaction causes the data in the block to reach an internal 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..
With cost of disk storage falling, why do I still need to care about compression?
Enterprises are experiencing an explosion in the volume of data required to effectively run their businesses. This trend in data growth can be attributed to several key factors. Recent changes in the regulatory landscape, such as Sarbanes-Oxley and HIPAA, are contributing to this trend by mandating that enterprises retain large amounts of information for long periods of time. Mass distribution of rich and multimedia content over the Internet, made possible through advancements in broadband technologies, also contribute to the growth in overall data volume. Various estimates indicate that data volume is almost doubling every 2-3 years. This sudden explosion in data volume presents a daunting management challenge for IT administrators. First and foremost are the spiraling storage costs: even though the cost per MB of storage has been declining dramatically in the last few years, the enormous growth in the volume of data that needs to be retained online makes storage one of the biggest cost elements of most IT budgets. In addition, application scalability and performance must continue to meet the demands of the business – even as data volumes explode. Oracle Database 12c, with the Advanced Compression Option, helps customers cope with these challenges. Innovations in Oracle compression technologies help customers reduce the resources and costs of managing large data volumes. Another important benefit is in the performance area. A major bottleneck for many systems is I/O bandwidth. Advanced Compression can help alleviate that bottleneck in several cases by reducing the amount of data that needs to be transferred across I/O channel and also further boost performance through improved memory efficiencies.
How much storage will I save using Advanced Compression option?
Advanced Compression Advisor is a PL/SQL package that is used to estimate potential storage savings, for Advanced Row Compression, based on analysis of a sample of data. It provides a good estimate of the actual compression ratio that may be obtained after implementing Advanced Row Compression. A version of Advanced Compression Advisor, which supports Oracle Database 9i Release 2 through 11g Release 1, is available for free on the Oracle Technology Network website. The Advanced Compression Advisor (DBMS_COMPRESSION) is built in to Oracle Database 11g Release 2 and above.
Can I compress data at a partition level?
Yes. Compression can be done at a tablespace, table or partition level.
How do I compress an existing table?
There are three recommended approaches to enabling Advanced Row Compression on existing tables:
1. ALTER TABLE … COMPRESS FOR ADVANCED ROW This approach will enable compression for all future DML. However, the existing data in the table will remain uncompressed.
2. Online Redefinition (DBMS_REDEFINITION) This approach will enable compression for future DML and compress existing data. Using DBMS_REDEFINITION keeps the table online for both read and write activity during the migration.
3. ALTER TABLE … MOVE COMPRESS FOR ADVANCED ROW This approach will enable compression for future DML and compress existing data. While the table is being moved it is online for read activity but has an exclusive lock – so all DML will be blocked until the move command completes. ALTER TABLE ... MOVE PARTITION ONLINE enables non-blocking online DDL where DML operations continue to run uninterrupted on the partition that is being moved.
What does Advanced Compression provide in terms of Information Lifecycle Management?
Information Lifecycle Management (ILM) is the practice of applying policies for the effective management of information throughout its useful life. ILM includes every phase of a “row” from its beginning to its end, and consists of the policies, processes, practices, and tools used to align the business value of information with the most appropriate and cost effective IT infrastructure from the time information is created through its final dispostion.In Oracle Database 12c, Automatic Data Optimization can be used to create policies, and automate actions based on those policies, to implement your ILM strategy. ADO utilizes the usage statistics collected by Heat Map, and depending on your ILM requirements, may also require use of Partitioning, Advanced Row Compression, and Hybrid Columnar Compression.