- Posted by Gavin Soorma
- On December 1, 2009
- 1 Comments
Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well.
The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.
To use this option, we can run the following RMAN commands
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
followed by ..
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;
Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.
Compression Level ‘HIGH’
backupset size: 226.18M
Compression Level ‘Medium’
backupset size: 293.80M
Compression Level ‘Low’
backupset size: 352.59M
Compression Level ‘Basic’
backupset size: 276.55M
To summarise we can conclude:
LOW – corresponds to LZO (11gR2) – smallest compression ratio, fastest
MEDIUM – corresponds to ZLIB (11gR1) – good compression ratio, slower than LOW
HIGH – corresponds to unmodified BZIP2 (11gR2) – highest compression ratio, slowest
BASIC (which is free) – corresponds to BZIP2 (10g style compression) – compression ratio in the range of MEDIUM, but slower