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
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.
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:
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
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!"
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
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
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
. . .
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