Oracle Advanced Compression Advisor

Cloud Account Sign in to Cloud Sign Up for Free Cloud Tier

Oracle Account

Compression Advisor

Compression Advisor provides an estimate of the compression ratio that can be realized through the use of the Oracle Advanced Compression option. This estimate is based on analysis of a sample of data and provides a good estimate of the actual results you may obtain once you implement the OLTP Table compression feature in your environment.

Overview

The Compression Advisor PL/SQL package you use is dependent upon which Oracle Database release you currently have deployed. Those customers that want to use Compression Advisor with Oracle Database 9i Release 2 through Oracle Database 11g Release 1 will use the DBMS_COMP_ADVISOR package available for download below. Customers that want to use Compression Advisor with Oracle Database 11g Release 2 through Oracle Database 12c will use the DBMS_COMPRESSION package that is included with the database.

Learn more about Oracle Advanced Compression

This package can be used on Oracle Databases running Oracle Database 9i Release 2 through 11g Release 1.

A compression advisor (DBMS_COMPRESSION) is included with Oracle Database 11g Release 2 and Oracle Database 12c.

Using Compression Advisor

This procedure can be used with Oracle Database 9i Release 2 through Oracle Database 11g Release 1. Running this procedure will create tables in the default tablespace of the user running the procedure. While these tables will get dropped at the end of the procedure they will consume space while the procedure runs. Oracle recommends creating a tablespace specifically for storing these tables and assigning it as the default tablespace to the user running the procedure. The DBMS_COMP_ADVISOR advisor package is only available as a free download.

Compression Advisor consists of the DBMS_COMP_ADVISOR package containing the following procedure:

Copy




	getratio(
    ownername            IN     varchar2,
    tabname                IN     varchar2,
    sampling_percent   IN     number
  );

where

- 'ownername' is the schema that the table belongs to

- 'tabname' is name of the table for which compression ratio is to be estimated

- 'sampling_percent' is any value between 0.000001 and 99

The output of this procedure is the estimated compression ratio.

Example:

Copy



SQL>  set serveroutput on
SQL>  exec dbms_comp_advisor.getratio('SH','SALES',10);

Sampling table: SH.SALES
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression option: 2.96

PL/SQL procedure successfully completed.