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

Last updated on FEBRUARY 02, 2022

Applies to:

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 worker, 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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.