Starting with Oracle 11g Oracle added several compression algorithms to compress data. They can be used for compressing tables, LOBs , compressed data pump exports or even RMAN backups. Unfortunately for some compression algorithms you need to purchase the “Advanced Compression Option”. The following table lists the available RMAN compression options, the most likely compression algorithm being used and states if an additional license is required:
[table id=13 /]
This article is intended to take a look at the different compression methods available in Oracle 11g and to compare them.
The environment being used was a freshly created 11g Release 2 database with some smaller tables in it. The total sum of all segments equals to 4.88 GB. All database data files excluding the temporary ones are 7.3 GB total. Excluding temporary and undo data files total size equates to 5.9 GB.
The server was running on VMWARE with one dedicated CPU core per host and 4 GB memory.
To configure RMAN to use compression at all you can use:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE TAPE BACKUP TYPE TO COMPRESSED BACKUPSET;
To configure the different backup algorithms:
CONFIGURE COMPRESSION ALGORITHM 'BASIC'; CONFIGURE COMPRESSION ALGORITHM 'NONE'; CONFIGURE COMPRESSION ALGORITHM 'LOW'; CONFIGURE COMPRESSION ALGORITHM 'MEDIUM'; CONFIGURE COMPRESSION ALGORITHM 'HIGH';
The following table displays the test results for the chosen test environment:
[table id=14 /]
As you can see from the table HIGH compression does an incredibly high load on the machine and take extremely long but produces the smallest backup set size. I suppose the long backup time might be explained by the fact that the environment is running under VMWARE which adds additional overhead and slows down CPU. Surprisingly BASIC compression (which is available without advanced compression license) does a good job as well and produces the second smallest backup set but takes nearly as long as doing uncompressed backups. But in other environment with faster CPUs this will change!
In the test environment used either LOW or MEDIUM compression seems to be the best choice. Due to the fact MEDIUM produces a approx. 15% smaller backup set but taking only a few seconds more time to complete i would rank MEDIUM on 1st and LOW on second.
RMAN offers backup compression since version 10. But with 11g Oracle added several new compression algorithms which lets you adjust the IO- and CPU-consumption and the resulting backup set size by choosing between five algorithms: NONE, DEFAULT, LOW, MEDIUM, HIGH. The stronger the compression the smaller the backup size but the more CPU-intensive the backup is. You basically change CPU-cycles for IOPs. If you do not have the advanced compression license BASIC compression will produce reasonable compression rates at moderate Load. If you have the licence you have a lot more options to suit your needs.
Regardless of the results in THIS test case YOUR results will be different due to: different data which leads to different compression ratios, different storage architecture for data files and backup destination (local disks, SAN, NAS, Tape), different CPU resources (faster/slower CPUs), rman parallelism). The test performed here are intended to give you some basic idea how backup time and backup size will change depending on the compression chosen.
!! Because of many factors influencing backup speed and backup size you have to test backup and backup compression in your environment yourself. There is no general rule of thumb. !!
If you want to test and optimize your rman backup you basically have three major switches to play with:
- compression algorithmn,
- rman parallelism and
- data transfer mechanism (SAN or Ethernet [this includes: iSCSI, NFS, CIFS, Backup to tape over Ethernet])