specific test case.
Please click on image below if the image is blur. Results and Analysis are based on following:
Only Tables were compressed in test case using 11g compression feature (compress for all operations)
Results:
1. Comparing Number of Rows with Size of Table before and after compression
a. CUSTOMERS : Inserted 381 records less after compression and table size went
up by 64 bytes
b. LOGON : Without compression 2167 more customers were able to login which
tells compression effects the scalability of system.
c. ORDERS & ORDER_ITEMS : Saved 1984 + 2112 = 4096 KB after compression but
at the same time it processed 1788 orders (6233 items ) less compared to that
of before compression. Overall savings looks good but in my test case avg row
length of orders table was 53 bytes and that of order_items was 30 bytes.
Why it may now work in production? Oracle 11g Advanced compression algorithm:
Oracle’s compression algorithm is based upon eliminating duplicate values in
each block. Avg Row length in production environment will be much higher compared to one in my test case.
Compression feature works best if row length in production is low enough and
repetitive data is loaded sequentially which is still possible in DSS but not OLTP. Unique inline LOB's (avg row length 3-4k) will hardly compress anything.
2. Tablespace Free Space
Overall space utilization went up with compression by 768 KB.
3. Performance
-With compression Total Transaction went down by 85945 - 83625 = 2320 in 45 mins.
-With compression number of logons went down by 2167. For companies counting revenues by transaction/hr this could mean loss of xxxx dollars in N hours.
-With compression Avg T/S went down.
-With compression Avg Wait/IO CPU went up.
-With compression Avg Response time (timings in milliseconds) went up.
-With compression Oracle uncompresses data in RAM which is very fast. This will
require additional RAM on servers where memory foot print shows uptrend.
Synopsis:
Oracle 11g compression feature is licensed separately at $10,000 per processor. Before recommending this feature to your client/company make sure you know your databases very well and how much % of compression you will gain after paying
$10,000 per processor.
"Oracle’s compression algorithm is based upon eliminating duplicate values in
EACH BLOCK." - $10,000 per processor?
In OLTP environment DBA's don't have control on how data will be inserted. In OLTP block size will be relatively small compared to that of DSS. So it will be difficult to guesstimate % of compression achived and $$ saved with overhead of performance.
If you are using RAM SAN (SSD) it might be worth using 11g compression feature. It will also be useful in a warehouse environment where data is loaded sorted by key columns to have more and more repetitive data in same block to achieve maximum compression.
Advanced compression can also be used to compress datapump dump files, compress archives for physical standby database, avoid duplication of LOB's etc.
Last but not least consider cost of SAN Vs 11g compression.
