Before implementing one or more of the Oracle compression options that are available within an Oracle database, there are a couple of things you would like to examine before implementing one or more of these compression options. One thing you would like to know in front, without actually compressing an table, table partition or index, how much storage you would save when compressing such an object.
Oracle is delevering the so called Oracle Compression Advisor for Oracle database versions ranging from Oracle 9.2 up to 11.1 (this package is built-in into version 11.2), but the functionality of this package is rather limited (only determine compression ratio for tables).
This is the main reason why I’ve created a package (MCL_COMPRESS_CALC) which has the following functionality:
- Determine compression ratio (and some other compression statistics) when implementing DIRECT_LOAD or OLTP compression for a table or table partition
- Determine the compression ratio and optimal prefix length for when implementing index key compression for an index
- Use a worklist for batch-mode calculation of the compression ratio (and others) for a list of objects and storing the results in a statistics table
- Store the results in a statistics table for reporting (in worklist mode and single table/index mode)
The package should be installed in a schema (with enough quota on your default tablespace), where the schema should have the following privileges granted:
GRANT SELECT ON sys.v_$instance TO pkgowner; GRANT SELECT ON sys.v_$parameter TO pkgowner; GRANT SELECT ON dba_segments TO pkgowner; GRANT SELECT ON dba_tablespaces TO pkgowner; GRANT CREATE TABLE TO pkgowner; GRANT CREATE SYNONYM TO pkgowner; GRANT ANALYZE ANY TO pkgowner; GRANT SELECT ANY TABLE TO pkgowner; or GRANT SELECT ON owner.table TO pkgowner; (you should do that for every table you would like to run the calculator on)
After granting the required privileges connect as the schema (package owner) where you would like to have the package installed and run the following scripts to install the pacakge definition, package body and setup of the tables used by the package:
@mcl_compress_calc.sql @mcl_compress_calc.plb BEGIN MCL_COMPRESS_CALC.SETUP_TABLES; END; /
That’s it. You will now have a package called MCL_COMPRESS_CALC (compression calculator) which you can use to determine the compression ratio for the tables, table partitions and indexes in your database. You can now simple determine the compression ratio for a table by using the command:
SET SERVEROUTPUT ON BEGIN MCL_COMPRESS_CALC.GET_RATIO_FOR_TABLE('table owner','table name'); END; /
That’s it. The package will use a default sample size to determine the compression ratio (along with some other compression statistcs) for the specified table and will show the results to you. By default a pretty small sample is used (1000 blocks) which will return the results very quickly.
To determine the compression ratio for all tables larger than for example 3 GB, use the following query to generate a list of statements that you can copy/paste, which will add these tables to the worklist table used by the package:
SELECT 'EXEC MCL_COMPRESS_CALC.ADD_TO_WORKLIST(obj_type=>''TABLE'',obj_owner=>'''||a.owner||''',obj_name=>'''||a.table_name||''',compress_type=>''DIRECT_LOAD'');' FROM dba_tables a, dba_segments b WHERE a.owner = b.owner AND a.table_name = b.segment_name AND a.partitioned = 'NO' AND b.bytes > 3221225472 ORDER BY b.bytes;
Copy/Paste the generated statements and after that start the processing of these tables by using the following command:
SET SERVEROUTPUT ON BEGIN MCL_COMPRESS_CALC.PROCESS_WORKLIST; END; /
You can follow the progress of the action by querying the MCL_COMP_CALC_WORKLIST table in another session using the query:
SELECT obj_type,obj_owner,obj_name,status FROM mcl_comp_calc_stats;
A status of “IN PROGRESS” means that the MCL_COMPRESS_CALC package is working on the specific object, a status “DONE” means the package was finished with it. An status of “ERROR” will (of course) mean an error has occurred while calculating the object, where you can find the actual error in the column named “emsg” of this table. Objects that have an empty status (NULL) are not processed yet.
You can find the compression ratio and some other useful compression statistics in the table called MCL_COMP_CALC_STATS for all successfully processed objects in the worklist table.
If you are interested in using the package, go to the “Downloads” page an download the zipfile containing the package script and an readme.txt with a description of all procedures, tables and some useful examples.