How to Evaluate Table Compression Space Savings
(Doc ID 1374169.1)
Last updated on FEBRUARY 02, 2022
Applies to:Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 18.104.22.168 [Release 10.2 to 11.2]
Information in this document applies to any platform.
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.
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document