How to Evaluate Table Compression Space Savings (Doc ID 1374169.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Goal

When determining the space savings resulting from compression it is necessary to distinguish between the benefits of compression and the overhead of space management. If just the segment size (dba_segments.bytes or dba_segments.blocks) is considered then empty blocks resulting from extent allocation details can cause misleading results.

Common Examples

1) Minimum extent length or uniform extent settings can lead to extents with little data and many empty blocks. This is most evident when the amount of data tested yields a small number of extents. For instance, if the tablespace has a uniform extent size of 100M and the compressed data occupies 110M, then the segment size will be 200M with 90M unused space.

2) Segments created using parallelism will have at least one extent per parallel slave, compounding the skew resulting from empty blocks mentioned above. If alter table, create table as select, or insert select are used with degree 4 in the example above, then there will be 4 extents in the segment totaling 400M and the 110M of compressed data will be spread across those 4 extents.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms