I have been using Oracle’s Advanced compression advisor recently. It is a great tool to estimate the benefit you will likely achieve from using the Oracle Advanced Compression option.
The DBMS_COMPRESSION, PL/SQL package is included with Oracle Database 11g Release 2 and above. This version of the advisor 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). You do not need Advanced Compression licenses to run the advisor.
An example of using the advisor:
The advisor is easy to use. Below is an example of running the advisor against a table called LINEORDER in a schema called SSB, which resides under a tablespace also called SSB.
set serveroutput on
DECLARE
blkcnt_cnt pls_integer;
blkcnt_uncmp pls_integer;
blkcnt_cmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('SSB', 'SSB', 'LINEORDER', '', DBMS_COMPRESSION.COMP_ADVANCED, blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = '|| blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = '|| blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = '|| row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = '|| row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = '|| comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '|| cmp_ratio);
END;
Running this example returns the following output:
Block count compressed = 1009 Block count uncompressed = 1672 Row count per block compressed = 117 Row count per block uncompressed = 70 Compression type = "Compress Advanced" Compression ratio = 2 PL/SQL procedure successfully completed.
From this output We can easily see the estimates for:
- the uncompressed and compressed block count.
- The rows per block uncompressed and compressed.
- The type of compression that was used for the estimate. The advisor can advise on different compression levels and types such as advanced compression, hybrid columnar compression and the like
- Finally, we are given a compression ratio estimate. We can see for this table we are likely to see a 2x compression on the table if we choose to compress it.
As these details are based on the actual data in the table being analysed the estimate is very accurate.
The best way to get started with the advisor, which is simple to use, is to read the getting started guide.
Whilst running the advisor I hit several small issues. I have documented them below. They were both easy to correct.
Issue one:
Whilst running the advisor on very large objects I was hitting the below error:
ORA-30562: SAMPLE percentage must be in the range [0.000001,100) ORA-06512: at "SYS.PRVT_COMPRESSION", línea 1313 ORA-06512: at "SYS.PRVT_COMPRESSION", línea 1307 ORA-06512: at "SYS.PRVT_COMPRESSION", línea 1026 ORA-06512: at "SYS.PRVT_COMPRESSION", línea 252 ORA-06512: at "SYS.DBMS_COMPRESSION", línea 250 ORA-06512: at line 62 ORA-06512: at line 62
I was missing a database patch: 30819629. Without this patch objects that have more than 20 million blocks can generate this error. With the patch applied the error did not occur.
Issue two:
After fixing the patching error I started to hit the following error:
ERROR en línea 1: ORA-00933: SQL command not properly ORA-06512: at "SYS.PRVT_COMPRESSION", línea 1313 ORA-06512: at "SYS.PRVT_COMPRESSION", línea 1307 ORA-06512: at "SYS.PRVT_COMPRESSION", línea 1026 ORA-06512: at "SYS.PRVT_COMPRESSION", línea 252 ORA-06512: at "SYS.DBMS_COMPRESSION", línea 250 ORA-06512: at line 62 ORA-06512: at line 62
After some research it tuned out that my NLS settings were affecting the advisor. I had NLS_NUMERIC_CHARACTERS parameter set to ‘,.’ . This was upsetting the advisor. I reset this NLS setting back to the default value, and the error was resolved.
I would recommend for the session running the advisor, to set the NLS_LANGAUGE to AMERICAN_AMERICA character set. Run the advisor and if you still get issues like the above look at other NLS settings that might be causing the issue such as the NLS_NUMERIC_CHARACTER setting.