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.
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.
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.
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:
getratio( ownername IN varchar2, tabname IN varchar2, sampling_percent IN number );
- '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.
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.