11g Data Compression - too much overhead?

11g Data Compression - too much overhead?

Oracle 11g data compression promises some amazing saving in disk storage as well as important performance improvements.  Please read my notes on the benefits of Oracle 11g data compression:

 

- Oracle 11g Data Compression Tips

Tests show that 11g compression result is slower transaction throughput but creates less writes because of higher row density on the data block.  See this benchmark of transparent data encryption.

Note:  When rows are first inserted into a data block with 11g compression, Oracle does not compress the row.  Oracle compression always begins after the first row insert when a subsequent insert bleeds into the block free space, as determined by the PCTFREE parameter.  At this time, all existing rows within the block are compressed.

But with 11g being so new, there are legitimate concerns about the performance overhead.  Roby Sherman published some negative test results about the 11g data compression utility, suggesting that there may be some performance issues, noting that the performance was "quite horrible":

For anyone interested, I ran some very quick benchmarks on 11g's new Advanced Compression table option COMPRESS FOR ALL OPERATIONS that Oracle is claiming was "specifically written to create only the most 'minimal' of performance impacts in OLTP environments.

The results are here:

http://web.mac.com/tikimac/iWeb/silicon/Roby_Sherman/ Oracle_Certifiable/Entries/2007/8/16_11G_TABLE_COMPRESSION_- _Don’t_Believe_the_Hype.html


I guess their definition of minimal and my definition of minimal must be different... Anyway, if you're interested in this feature, feel free to take a quick look!

Other comments of Sherman's 11g compression test included:

Rather than commenting that the advanced compression is "quite horrible" I'd comment that your choice of tests are quite horrible.

 

I don't consider Roby's test cases are horrible. Anyone who criticizes the hype seems to be criticized. Look at the argument of Roby: "But, then again, if your operations are that minimal, you probably aren't creating enough data to need compression in the first place!"
 

Robert Freeman, a trainer for Burleson Consulting noted that his results did not show the same degradation and he offers insightful comments about the dangers of using a "negative proof":

 

I've read this particular post several times. I just have to believe that I'm not getting something here, because ..... I want to be charitable but the point that is being made is just asinine in my opinion. I hope, I really hope, that I've missed something obvious and that I'm the fool (would not be the first time - I freely confess my imperfections).

>> The common nonsense peddled is like: It all depends.

EXCUSE ME????? Common nonsense? The whole scientific method is all about Ceteris paribus. Research is influenced heavily on IT DEPENDS. Drop a rock and a feather on the Earth and then on the moon and tell me the results are not a matter of IT DEPENDS. I must have missed your point, because nobody could be so short sighted as to presuppose that there are no dependencies.

Can you explain negative cases? Sure. I can explain the negative case of the rock falling faster than the feather to the difference in location and criteria of the experiment. I can explain Roby's negative results in numerous ways, including accepting the *possibility* that his results reflect truth, and that compression is a performance killer. Did he provide sufficient evidence to review his results, of course not. How do we know the issue isn't one of the optimizer changing the plan, as opposed to the physical implementation of compression, for example? We don't because no execution plans were provided.

That being said, his results do not mirror mine. Explain that negative case.  Oh, is it because my results are on Oracle Beta 5? Or is it that my results  are on a faster CPU? Can we always explain negative cases? No. . .

Additionally, I argue that one can never, ever, systematically prove everything 100%. Perhaps to a high degree of confidence, but never for sure. Why? Because the conditions of that result and that analysis can change because the dependencies change. You can not control the entire environment, thus you can not 100% guarantee an outcome, ever. If you have never had the frustrating experience of having two different result sets and not being
able to figure out why they differ, then you are luckier than I (or younger, or you have more time or less experience).
. . .

While I have not tested compression in the production code (yet, I'm running the book through production now), when I did my chapter on compression in Beta 5, I found the results to be very different from Roby's. Still, I'm glad to see him testing this stuff and reminding us that not every new feature is a panacea.