Kevin Kempf's Blog

July 20, 2009

Advanced Compression in 11i, by the numbers

Filed under: 11g, advanced compression, Oracle — kkempf @ 2:30 pm

Aside from the obvious “it doesn’t play nice with Dataguard” issue I’m having with 11i/11g Advanced Compression, I thought I’d throw out some hard numbers to show just how much this technology is buying me so far. I’ve not compressed everything, but have been steadily running compresses as time permits in maintenance windows. My first priority was to compress the items in my buffer cache; so I created a table holding the “pre-compression” state of everything.   I then add information to this table as I compress tables.  Extracting data from this statistics table, here’s my “top twenty” tables based upon size saved:

TABLE_NAME PRE_COMP_MB POST_COMP_MB MB_REDUCED COMP_RATIO
BOM.CST_ITEM_COST_DETAILS 2013.25 391.75 1621.5 0.81
INV.MTL_MATERIAL_TRANSACTIONS 2334.88 774.88 1560 0.67
INV.MTL_TRANSACTION_ACCOUNTS 1641.38 526.88 1114.5 0.68
AR.AR_TRANSACTIONS_REP_ITF 962.25 136.75 825.5 0.86
MRP.MRP_AD_RESOURCE_REQUIREMENTS 808.25 212.75 595.5 0.74
INV.MTL_TRANSACTION_LOT_NUMBERS 922.5 376 546.5 0.59
WIP.WIP_TRANSACTIONS 797.75 332.38 465.37 0.58
APPLSYS.FND_LOGINS 233 10.38 222.62 0.96
MRP.MRP_AD_OPR_RESS 431.63 220.88 210.75 0.49
AR.RA_CUSTOMER_TRX_LINES_ALL 268.25 102.75 165.5 0.62
APPLSYS.WF_NOTIFICATION_OUT 132.5 0.13 132.37 1
PO.RCV_TRANSACTIONS 189.5 57.5 132 0.7
WSH.WSH_DELIVERY_DETAILS 263.5 141.25 122.25 0.46

That’s a sum of 8405.5 MB, or 8.2 GB so far in just 20 tables (all objects to date put me in the 9.2 GB range of savings). In my case, this is about 1/3 of my SGA saved, as most of these objects were 100% in the buffer cache.

It raises some interesting questions. Is the cost of Advanced Compression justified?  Right now, I would have to say, no way, because of the Dataguard bug. But let’s assume Oracle manages to fix that.  A few other considerations off the top of my head are:

  • What is the cost of instead putting another 8/16/32GB of RAM in the server(s)?
    • Does the hardware support more RAM or is it maxed out?
    • In the case of RAC,multiple instances make this expensive fast
  • What is the value of having faster RMAN backups?
    • Backups take less time because compressed blocks don’t write as much data
    • ZLIB compression option is now available and it runs faster than the old BZIP
  • Do the users even perceive a benefit in terms of items being in the buffer cache?
    • Performance may be more limited by latency across the WAN than a few disk reads
    • Likely highly variable depending upon the complexity of reports and queries
  • What is the savings in disk usage, when all the objects shrink?
    • Allocated space will go down a significant amount, across all tablespaces
    • May defer the cost of  Infomation Lifecycle Management (ILM) solutions such as Applimation
    • Multiply this savings times all environments: regression testing, dataguard, training, development, etc
  • What kind of deal can you get from Oracle?
    • Everyone’s discount is different, based on the economy, the “fire sale” end of quarter/fiscal year incentives Oracle may give you

I’m not trying to be to contrary in my analysis, but I think the only right answer is “it depends”.

Advertisements

Blog at WordPress.com.