Assess storage and performance impact of Advanced Compression on table data
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.
The following table shows the impact of Advanced Compression on table size and performance:
Full Table Scan Performance (s)
DML Performance (s)
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.
Case Study 2
Assess storage impact of Advanced Compression on table data using different block sizes
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.
Compressed, 8K block size
Compressed, 16K block size
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.
Case Study 3
Assess impact of Advanced Compression on backups and redo gap resolution
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.
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:
10g R2 RMAN Compression
11g Fast RMAN Compression
Tape Backup Time (s)
Disk Backup Time (s)
With Data Pump compression available in Advanced Compression, the dump file size was reduced by 75%.
With Advanced Compression, the redo gap resolution duration was reduced by about 35% for a Data Guard environment.
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.