An easy way to get started with Advanced Compression is by using compression advisor. The “DBMS_COMPRESSION” PL/SQL package (commonly called compression advisor) gathers compression-related information within a database environment.

The output of running compression advisor is an estimation of the compression ratio for the specific table or partition that was the target of the compression advisor. The compression advisor output will indicate the “COMPRESSION RATIO” presented as a number such as 2.1. This number indicates that, for this specific table or partition, the estimated compression ratio is 2.1x, representing about a 50% reduction in the footprint, of the table, when compression is enabled.

Typical compression ratios reported by compression users:

Compression Type                              Typical Compression Ratio
————————                               ————————————  

Advanced Row Compression:                      2x to 4x
Advanced LOB Compression:                       2x to 3x
Hybrid Columnar Compression:                 6x to 15x
Advanced Index Compression:                    2x to 5x

Note that generally, the compression ratio achieved in a given environment depends on the data being compressed, specifically the cardinality of the data

A version of compression advisor, which supports Oracle Database 9i Release 2 through 11g Release 1, is available on the Advanced Compression page on Oracle.com. However, this version can only report the compression ratio for data tables – those tables (and partitions) that would be targeted for OLTP Table Compression.

Another version of the DBMS_COMPRESSION, PL/SQL package is included with Oracle Database 11g Release 2 and above. This version can report the compression ratios for data tables (targets for Advanced Row Compression), LOBS using SecureFiles (targets for Advanced LOB Compression), and indexes (targets for Advanced Index Compression).

Here are some general insights, and best practices, to keep in mind when using compression advisor:

  • Advanced Compression is not required to use compression advisor, and using compression advisor for Hybrid Columnar Compression does not require Exadata
  • Tables residing in uniform tablespaces can be compressed. However, compression adviser has the restriction that the scratch tablespace cannot be uniform

  • If you get this type of message when estimating Hybrid Columnar Compression ratios, you may want to re-test using the solution below

    ORA-12801: error signaled in parallel query server P002

    ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

    Solution: Disable parallel processing for the session (set parallel_max_servers=0)

  • Compression advisor builds two temporary tables (for comparison purposes) as part of the estimation process for Advanced Row Compression (Hybrid Columnar Compression uses four tables). The temporary tables are created using the prefix ‘cmp3$’ or ‘cmp4$’ and are dropped by the compression advisor when no longer required. While these temporary tables are removed after the compression advisor completes, you need available free space for the compression advisor to build the temporary tables.

For more information and examples about compression advisor, please see this MOS note:

How Does Compression Advisor Work (DOC ID: 1284972.1)

In addition, please see the Compression Advisor Oracle Tech Brief here.