Oracle Advanced Compression Advisor

My main Oracle Applications Database has been growing steadily and is now around 270GB. In terms of databases this isn’t huge, but when you keep multiple development and test copies around on enterprise class storage, AND replicate it to your DR site, that can get expensive.

With Oracle 11g database, Oracle came out with two products to help manage space (and improve performance!) in your Oracle database – Oracle Advanced Compression and Oracle SecureFiles. Although both are for reducing disk usage, they are aimed at different areas of disk usage within an Oracle database.

SecureFiles is the next generation of storage for Oracle LOBs (Large OBjects). Oracle LOBs stored in the format before SecureFiles are said to be stored in BasicFiles. SecureFiles is aimed at attachments to the database – CLOBS (Character LOBs), BLOBs (Binary LOBs), and NCLOBs (multi-byte character LOBs). SecureFiles offers a number of benefits over BasicFiles. Two are relevant to reducing space usage – de-duplification and compression. SecureFiles is a free feature of Enterprise Edition and has no additional licensing costs. As a result, it’s the sort of low hanging fruit that should be of interest to any Oracle DBA out there – free improved performance and free reduced disk storage. What’s not to like? Because this feature is free, we’re actively testing with this in our environments and plan on rolling this out by end of year. I’ll post a much longer blog post with our space savings and details of converting data from BasicFiles to SecureFiles later.

Advanced Compression is aimed at table data – compressing the data stored in the tables. This not only saves space on the file system, but actually improves performance by reducing the amount of data that needs to be read from disk (reading data from disk is frequently the bottleneck with Oracle databases – which is why Oracle is so memory hungry and tries to cache much of the data it needs in the System Global Area (SGA)). Advanced Compression is a add-on feature to Enterprise Edition at a cost of $11,500 per x86 license (and remember it takes TWO x86 CORES to equal one x86 LICENSE) – and like everything Oracle, that is based on how many cores are in the box, not how many your database cpu_count is set to or VM (if you virtualize your Oracle database) utilizes.

With Oracle Enterprise Manager (OEM) 11g, one of the new features is a Compression Advisor. You can read about other reasons to upgrade to OEM 11g at this blog post on OEM 11g new features. When run against an Oracle 11gR2 database, this advisor will analyze your database objects, estimate the compression ratio you’ll achieve and even make recommendations on the best compression settings for your environment. Although my largest database is 11gR2, I have a variety of other database versions on those same physical hosts (gotta love virtualization!) that aren’t 11gR2 and hence don’t have the DBMS_COMPRESSION package.

Luckily, I stumbled across a standalone version on Oracle Technology Network. This standalone version will work with Oracle 9iR2 (9.2.0.X) through 11gR1 (11.1.0.X) and can give you the data you need to convince business areas to upgrade to 11g database.

One thing to be aware of with this script: it will create a temporary table of the compressed data so you may wish to reating a tablespace specifically for storing the temporary table and making that the default tablespace of the user executing the script. The temporary table gets dropped at the end.

Note: The example on the Oracle Technology Network link above is incorrect. It is using the DBMS_COMPRESSION package which is in 11gR2 Oracle database and NOT provided by this package. So if using an 11gR2 database, you use DBMS_COMPRESSION package, but if using a 9iR2 thru 11gR1 database, use the DBMS_COMP_ADVISOR package like in my example below

Here’s the output from running it against a 9.2.0.8 database with a table OM_DATA in a schema called OO_MAIL. The table has 4.5 million rows and is 9.5 GB in size. (The product that uses this database requires Oracle 9iR2, for those wondering)

SQL> exec DBMS_COMP_ADVISOR.getratio(‘OO_MAIL’,’OM_DATA’,’OLTP’,25);

Sampling table: OO_MAIL.OM_DATA

Sampling percentage: 25%

Compression Type: OLTP

Estimated Compression Ratio: 1.62

PL/SQL procedure successfully completed.


I also ran this against my largest table in my Oracle Applications (11gR2) instance (INV.MTL_TRANSACTION_ACCOUNTS) – a 2.5GB table with 14 million rows:


Sampling table: INV.MTL_TRANSACTION_ACCOUNTS

Sampling percentage: 25%
Compression Type: OLTP
Estimated Compression Ratio: 2.57

So that works out to 3.64GB space I would save on the 9i database and 1.57GB in my 11gR2 database. A total of about 5GB saved. Every database (and the data it contains) is different, so run the numbers against your database to decide if Advanced Compression is worth it in your environment… and check out SecureFiles. It’s free.

8 thoughts on “Oracle Advanced Compression Advisor

  1. Hi, just to clarify: SecureFiles using either compression or de-duplication (as well as encryption) is an extra cost option as part of the Advanced Compression extra cost option.

  2. Really? I checked with my Oracle sales rep and he told me otherwise.

    I’ll follow up with him after the holiday – Thanks!

  3. I’m afraid so, I also had the same answer off a sales rep and went digging and found this from the options and packs section of the documentation (abridged):

    # Oracle Advanced Compression

    …Advanced Compression can be used to compress any unstructured content using SecureFiles Compression. Deduplication provides the ability to automatically eliminate redundant copies of SecureFiles data

    The Oracle Advanced Compression option contains the following features:

    * SecureFiles Compression and Deduplication

  4. Joy. Guess I know what project just got postponed for production till 2011 :/.

    Seriously though, thanks for that. I’ll update blog post tomorrow when I’m in front of a proper PC.

    J

  5. Great blog, do you know if DBMS_COMP_ADVISOR package will determine compression ratio on unstructured data in 10gR2 database? This will need to be converted to SecureFiles but not sure if advisor is that intelligent.

    THanks,
    GW

  6. Off the top of my head I don’t think it will as the advisor seems focused only on table data vs LOBs. It should be easy to test if you have a test instance.

  7. I found secure files comression and de-duplication as licensed and my understanding is that it is packaged in ‘Advanced Compression’ feature.

    Below link detailed the features packaged with ‘Advanced Compression’ feature.
    http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#CJACCDBA

    The Oracle Advanced Compression option contains the following features:

    •Data Guard Network Compression

    •Data Pump Compression (COMPRESSION=METADATA_ONLY does not require the Advanced Compression option)

    •Multiple RMAN Compression Levels (RMAN DEFAULT COMPRESS does not require the Advanced Compression option)

    •OLTP Table Compression

    •SecureFiles Compression and Deduplication

    •Flashback Data Archive (Total Recall)

    Can somedbody please clarify.

Leave a Reply

Your email address will not be published. Required fields are marked *