Heap Segment Compression - Table and Index

Submitted by dave on Tue, 04/06/2010 - 15:43

Indexes
Determining Benefit

For each index, one at a time run the following:

ANALYZE INDEX [index_name] VALIDATE STRUCTURE;
select height, lf_blks, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

Looking at the following columns:
OPT_CMPR_COUNT - Optimal Compression Count
OPT_CMPR_PCTSAVE - Estimated amout of space saved by compressing this index.

Tables

Determining Benefit
In 11gR2 you can use the DBMS_COMPRESSION.GET_COMPRESSION_RATIO procedure to estimate the ratio. Christian Antognini has a post about it here.

Otherwise you are on your own. I usually grab a subset of the data from a table and dump it into 2 tables - one compressed and one uncompressed. Then I can get an idea of how much space 'x' rows takes up. Oracle recommends testing 1000+ blocks worth of data for each object you would like to compress (table or index).

Instance Level Performance

What kind of activity are we seeing on Compressed Blocks?

SELECT dba_hist_sysstat.*,
dba_hist_snapshot.begin_interval_time
FROM dba_hist_sysstat, dba_hist_snapshot
WHERE dba_hist_sysstat.snap_id = dba_hist_snapshot.snap_id
AND stat_name LIKE 'HSC%' AND VALUE <> 0
AND STAT_NAME <> 'HSC Heap Segment Block Changes';

Gotchas and Restrictions
As with most Oracle features, they don't alway coexist nicely.

Randolf Geist has posted a helpful article which covers some restrictions on the use of segment compression.