Advertisements

Conor O'Mahony's Database Diary

Your source of IBM database software news (DB2, Informix, Hadoop, & more)

Truth in Advertising – Advanced Data Compression in Oracle 11g

with 2 comments

You may have seen the following advertisement from Oracle. They claim that Advanced Data Compression for Oracle 11g cuts your data storage requirements in half. Anyone who knows anything about data compression know that this is misleading. Sure, its possible that data compression could cut your data storage requirements in half. But that depends on the kind of data you have and on your environment. As we will see below, compression rates vary greatly depending on the nature of the data.

Oracle Compression Advertisement

Let’s take a quick look at the results of an InfoWorld investigation at Oracle Database 11g Advanced Compression Testbed, Methodology, and Results. In this investigation, InfoWorld analyzes two data sets: 1) the test kit that Oracle provides and 2) several tables from the TPC-C order entry benchmark data.

Let’s focus on the industry benchmark data. The first thing to notice is the variation in the compression rates. They vary greatly, depending on the nature of the data. The second thing to notice is that if do your math and figure out the before and after storage numbers, you will see that Advanced Data Compression for Oracle 11g reduces the total storage for these tables from 273,034 MB to 210,808 MB. This is a long way short of the 50% number that Oracle is touting! In fact, its actually saving less than 23%. However, as I have said, compression rates do vary greatly depending on the nature of the data and TPC-C data does contain random strings which do not compress well.

Table Name Original Size (MB) Compression Rate (%)
C_District 5 0
C_Item 11 0
C_New_Order 624 28
C_Customer 93,882 17
C_History 7,680 57
C_Order_Line 104,528 30
C_Stock 66,304 15

 

The next thing to notice in the InfoWorld article are the performance findings. While Oracle claims that “it runs faster” in the advertisement, the results of the InfoWorld investigation did not back this up. In fact, the InfoWorld investigation observed transactions per second for a mixed workload “falling from 28 to 13, which “represents a nearly 50% performance hit“. However, InfoWorld do point out that their tests are not a true representation of a real-world workload, and that the results are within acceptable parameters.

The point of this blog post is not to question whether Oracle can achieve high compression rates or whether performance is acceptable when compression is turned on. The point of this blog post is to make sure you are aware that compression rates and performance are highly dependent on the nature of your data and the nature of your environment, and don’t let vendors like Oracle tell you otherwise.

Advertisements

Written by Conor O'Mahony

August 6, 2009 at 11:27 am

2 Responses

Subscribe to comments with RSS.

  1. […] O’Mahony’s DB2 News and Personal Views shares Truth in Advertising – Advanced Data Compression in Oracle 11g. “The point of this blog post is not to question whether Oracle can achieve high compression […]

  2. Conor,

    Thanks for the blog posting. Compression figures have been high on my own agenda this week, having just upgraded a server from DB2 V8.2 to 9.7 and turned compression on. Full details are here –

    http://it.toolbox.com/blogs/database-geek/db2-97-migration-achieving-maximum-benefit-particularly-compression-33254

    Having turned compression on across the board, the overall compression ratio was 61% which I think is pretty good for a BI environment with a fairly large part of the data being financial (which I wouldn’t expect to compress as well as text data).

    Philip Nelson

    August 7, 2009 at 2:12 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: