Oracle Advanced Compression

Objective

Assess storage and performance impact of Advanced Compression on table data

Scenario

This case study involved compression of a table from a real world ERP database. Data from the existing table was loaded into a compressed table to assess the impact of Advanced Compression on size. Further, full table scan as well as DML operations were run on the compressed and uncompressed tables to assess impact on performance. 

Results

The following table shows the impact of Advanced Compression on table size and performance:

Measure	Table_A Uncompressed	Table_A Compressed
Size (MB)	1408	336
Compression Ratio	N/A	4.19
Full Table Scan Performance (s)	58.6	18.9
DML Performance (s)	48.5	50.3

Conclusion

In the case of this ERP database table, Advanced Compression provided close to 76% reduction in size. Full table scan ran three times faster and for DML operations, there was minimal impact on  performance.

Objective

Assess storage impact of Advanced Compression on table data using different block sizes

Scenario

The test involved compression of a table from a custom application database. Data from an existing table was loaded into a compressed table to assess the impact of Advanced Compression on size. To study the effect of block size, the same test was repeated with the compressed table being created with a larger block size.

Results

Table Type	Size (MB)	Compression Ratio
Uncompressed	2360	N/A
Compressed, 8K block size	896	2.63
Compressed, 16K block size	856	2.76

Conclusion

In this case, the table size was reduced by approximately 62% for 8K block size. Increase in block size from 8K to 16K provided marginal increase in compression ratios.

Objective

Assess impact of Advanced Compression on backups and redo gap resolution

Scenario

This case study involved testing of RMAN and Data Pump backup compression for a custom application. Data Guard redo transport compression was also tested as a part of this case study.

Results

Backup Compression

For testing RMAN compression, data was backed up using the fast backup algorithm (ZLIB) available with Advanced Compression. This was compared with regular RMAN compression available with Oracle Database 10g Release 2. The results are as below:

Metric	No Compression	10g R2 RMAN Compression	11g Fast RMAN Compression
Compression Ratio	N/A	5.94	5.92
Tape Backup Time (s)	228	201	81
Disk Backup Time (s)	18	201	81

With Data Pump compression available in Advanced Compression, the dump file size was reduced by 75%.

Network Compression

With Advanced Compression, the redo gap resolution duration was reduced by about 35% for a Data Guard environment.

Conclusion

These tests show that Advanced Compression can reduce backup storage requirements and compress data at high speeds. It also speeds up Data Guard gap resolution process resulting in faster synchronization of standby databases.