Kevin Kempf's Blog

March 23, 2009

11i Advanced Compression

Filed under: Oracle — Tags: , — kkempf @ 10:35 am

I’m pretty pleased with the results of my first foray into advanced compression with 11i.  It was so successful (and expensive) that I’m regression testing it now and hope to go live within a month.   The short of it is that it reduced my apps_ts_data from 77G to 20G, apps_ts_idx from 60G to 37G , and saved some room in apps_ts_archive, apps_ts_interface, and system as well.  Granted, some of these gains are  simply a result of the alter table … move reorg, but it’s still a healthy savings.   Multiply it by all my environments, and we’re easily over the half-terrabyte range in savings.  No metrics on performance yet, but I’ll post more statistics and observations as I get them.  The one bugger is that while this reduces my used space, I’ve had little success in reclaiming allocated space.  So I guess I simply won’t need to add datafiles to existing tablespaces for a long, long time….

Things to know about compression:

  1. alter table x compress for all operations only affects new inserts/updates
  2. alter table move reorganizes the table in compressed manner
  3. alter tablespaces for future upgrades/table adds: alter tablespace ts1 default compress;

Rough outline of my compression strategy:

  1. Determine what’s in the buffer cache, and use sql to write sql to compress objects > 60% in memory as a starting point
  2. gather “before” statistics
  3. compress existing object
    1. set timing on
    2. alter table schema.name compress for all operations move;
  4. gather “after” statistics
Advertisements

Blog at WordPress.com.