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.