Oracle Compression Advisor

Every once and awhile I come across a valuable utility, I have come across a new
Oracle utility that can be utilized starting with 9i release 2 that can help estimate the space savings from compression.

The process creates tables in the default tablespace of the user executing the procedure. It is recommended that you make sure the default tablespace is other then SYSTEM or SYSAUX.

The process is created via a package procedure available via a download from Oracle Technet at

http://www.oracle.com/technology/products/database/compression/compression-advisor.html

Example of the package installation
[oracle@oel54node1 compression_advisor]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 23 22:33:57 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @dbmscomp.sql

Package created.


Synonym created.


Grant succeeded.

No errors.
SQL> @prvtcomp.plb

Package body created.


Synonym created.


Grant succeeded.

No errors.


Here is an example of its execution

SQL> set serveroutput on
SQL> exec dbms_comp_advisor.getratio('SYS','AUD$','OLTP',20) ;
Sampling table: SYS.AUD$
Sampling percentage: 20%
Compression Type: OLTP
Compression ratio: 1.53

PL/SQL procedure successfully completed.