Advanced Compression is an option introduced in Oracle Database 11 g. It includes compression for structured data (numbers, characters, etc.), unstructured data (documents, images, etc.), backups (RMAN and Data Pump) and network transport (redo log transport during Data Guard gap resolution).
What are the benefits of using Advanced Compression?
Advanced Compression provides storage reduction through compression of all data types. It also provides performance improvements in several cases as compressed blocks result in more data being moved per I/O. It further improves memory efficiency as Oracle Database keeps data compressed in memory. All these benefits cascade through all the environments such as staging, QA, development, standby, backup, etc., magnifying the benefits several times. Advanced Compression includes enhanced compression capabilities for backups and also helps in faster synchronization of databases using Data Guard during gap resolution process.
I am already using the Table Compression feature introduced in Oracle9i. What additional benefits do I get from Advanced Compression option?
With Table Compression feature of Oracle9i Database, one could only compress table data during bulk load operations. With Advanced Compression, new and modified data is also compressed. Further, Advanced Compression includes compression and deduplication of SecureFiles. This can provide significant savings for applications that were storing unstructured data as LOBs in previous versions. Improved compression for backups and compression of redo logs are the other capabilities available only with Advanced Compression option.
I am already using the RMAN backup compression feature in Oracle Database 10 g Release 2. In what way is the RMAN backup compression included in Advanced Compression superior to this?
RMAN backup compression available in Advanced Compression provides much faster backups. It significantly reduces the duration for compressed backups with very little impact on compression ratios, when compared with RMAN backup compression available in Oracle Database 10 g Release 2. During lab tests conducted at Oracle, it provided a 2.5X performance improvement i.e. backup time was reduced to 40% of the time taken by Oracle Database 10 g Release 2.
What are SecureFiles? What is the relationship between Advanced Compression and SecureFiles?
Oracle Database 11 g has introduced SecureFiles as the next generation infrastructure for managing unstructured data. Using SecureFiles, applications can get the high level of performance earlier available only with file systems, and also attain the same level of security, scalability, transaction consistency and high availability as structured data within Oracle Database. Advanced Compression provides capabilities for compression and deduplication of SecureFiles data. Click here for additional information on SecureFiles.
What kind of technology is used to compress data?
For compression of table data, Advanced Compression extends industry standard algorithms to compress data at a block level. Repeating data patterns are stored in the block header as symbols, and occurrences of such data in the block are replaced with pointers to symbols. For unstructured data compression, backup compression and network compression, Advanced Compression uses industry standard algorithms that work on a similar concept of eliminating data redundancies.
What optimizations has Oracle done to minimize compression overhead for OLTP applications?
Oracle compresses a block in batch mode rather than compressing data every single time a write operation takes place. When a transaction causes the data in the block to reach an internally controlled threshold, all contents of the block are compressed. Only the transactions that trigger compression of the block will experience the minimal 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 Database does not need to decompress table blocks when reading data. Oracle can keep blocks compressed in memory and read them directly. Hence, more data can be packed in memory which results in improved cache hit ratio and reduced I/O.
How much compression can I expect by using Advanced Compression?
Compression ratios are dependent on the nature of data being compressed and can vary from a dataset to another. A two to four times reduction in size could be expected in most of the cases.
What is the performance impact of using OLTP Table Compression?
For DML operations on a compressed table, Oracle Database’s specialized batch algorithm keeps the performance overhead to a minimum. Internal tests at Oracle showed a minimal overhead of less than 3% for a DML workload. In many cases, query performance will improve due to improved I/O efficiencies. During internal benchmarking tests, table scans on compressed table provided a three times improvement in performance i.e. scan on a compressed table completed in one third the time when compared to table scan on the corresponding uncompressed table.
With cost of disk storage falling, why do I still need to care about compression?
While cost of storage on a per unit (GB) basis is falling, the data volumes are growing at a much faster rate. By industry estimates, databases are growing to twice or thrice their original size every couple of years. Thus, on a total basis, storage expenditure for organizations is still increasing. Further, data center overheads such as cooling, power, floor space, storage administration, etc. continue to grow with hardware getting added to handle higher data volumes. By reducing the need to add new hardware, Advanced Compression helps in managing these overheads. 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 will I save using Advanced Compression option?
To estimate savings from Advanced Compression, the following cost elements need to be considered:
Size of production database
Size of other copies (staging, standbys, QA, development, backup, etc.)
Excess storage capacity provisioned for future growth (this should also go down with compression – excess capacity provisioned for a 6 TB database will be much more than excess capacity provisioned for a 2TB compressed database)
Data center overheads (cooling, power, floor space, administration, etc.)
Apart from storage cost savings, Advanced Compression also improves memory efficiency and boosts query performance in many cases.
Can I compress data at a partition level?
Yes. Compression can be done at a tablespace, table or partition level.
How do I turn on various features under Advanced Compression option?
For table compression, click here.
For SecureFiles compression and deduplication, click here.
For RMAN compression, click here.
For Data Pump table compression, click here.
For Data Guard network compression, click here.
How do I compress an existing table?
There are multiple options available to compress existing tables. For offline compression, one could use ALTER TABLE Table_Name MOVE COMPRESS statement. A compressed copy of an existing table can be created by using CREATE TABLE Table_Name COMPRESS FOR ALL OPERATIONS AS SELECT *. For online compression, Oracle’s online redefinition utility can be used. More details for online redefinition are available here.
In addition to using Advanced Compression, what else can I do to reduce my storage costs?
Oracle Database’s Information Lifecycle Management (ILM) capabilities provide a comprehensive strategy for storage cost reduction. More information on ILM can be found here.