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.
|
Database
Printer View