Oracle 11g Release 2 – RMAN compression comparison

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.

Test environment

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.

Configure RMAN

To configure RMAN to use compression at all you can use:

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

or

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';

Test results

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.

Conclusion

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])

15 thoughts on “Oracle 11g Release 2 – RMAN compression comparison”

  1. Hi

    I would be interested to know how you tied the compression algorithms (LZO, BZIP & ZLIB) to the 11g RMAN compression settings (LOW/MED/HIGH)? I know the BASIC one is known. Care to share?

    Cheers
    F.

    1. If you only store textual data in the database it is up to 1:8 (basic / high compression). For low compression it is apporx 1:3

  2. Very good and useful information on comparing the different options. Good to know that BASIC option does not cost (No need buy Advanced Compression license).

    Thanks a lot!

  3. Hi Ronny,

    nice to read your test. I had no problem with the high compression rate. The main point is. if you can get a return of investment.

    I want to make you aware that your full article was copied by someone and given as his own work.
    I have no problem if information are shared but copying 1:1 and telling it is the own work is inacceptable.
    I thought you are interested to know this.

    http://neeraj-dba.blogspot.com/2011/12/configure-rman-backupset-compression.html

    All the best

    Tom

    1. Hi Thomas,

      thanks you for your comment.

      About the plagiatism… there is probably nothing i can do. But these people exist everywhere…

  4. HI,
    Can you please let me if there in any reference in oracle documentation for setting compression to NONE

    CONFIGURE COMPRESSION ALGORITHM ‘NONE’;

    I am getting below error message:
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of configure command at 08/21/2015 11:40:38
    RMAN-06806: compression algorithm ‘NONE’ of release DEFAULT not found

  5. Hi Ronny,

    I facing the same problem at one of my customer, while analyzing the high CPU load, I am sure this will help me to explain my customer that why CPUs are highly utilized at the time of backup. I must say you shared a good knowledge on RMAN compression algorithm.

    Muhammad

Leave a Reply

Your email address will not be published.