Is Oracle Advanced Compression underestimated?

In Database options, Database tuning, DBA, Oracle database on May 17, 2012 at 02:43

Abraham Lincoln said: “I can make more generals, but horses cost money”.

Often all I hear about Oracle Advanced Compression is: “.. but it is an extra option on top of Enterprise Edition”. And thus its hidden power is often neglected.

Advanced Compression is not just about having the data occupy less space on disk, tape or in the network. It is also about better performance of the database, deduplication and the utilization of less hardware resources: memory and CPU. Thus, you can consolidate more databases on the same server and reduce the license costs.

I will try to compress this blog post, I will make it as short as possible:

1. What and which tables to compress?

Compress the segments that are involved in most reads: physical, logical or unoptimized. Consider also the segments that are used in SQL statements having most reads.

But do not compress all of them! Use DBMS_COMPRESSION.GET_COMPRESSION_RATIO in order to verify that you will benefit from the compression. Do only those with “Compression Ratio” 2 to 1 and higher. Here is an example of how you can estimate the compression ratio:

set serveroutput on
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
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 = '||round(blkcnt_uncmp/blkcnt_cmp,2)||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);

2. How to measure the performance benefits of compression?

For the SQL statements run awrsqrpt.sql and compare the results:

Before Compression:

After Compression:

3. What comes after compression?

Follow the average CPU and I/O statistics for the compressed tables:

col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Owner’               format a10
col c2 heading ‘Object|Type’         format a10
col c3 heading ‘Object|Name’         format a15
col c4 heading ‘Average|CPU|Cost’    format 9,999,999,999
col c5 heading ‘Average|IO|Cost’     format 9,999,999
 to_char(sn.begin_interval_time,'mm-dd hh24') c0,  
 p.object_owner                               c1,
 p.object_type                                c2,
 p.object_name                                c3,
 avg(p.cpu_cost)                              c4,
 avg(p.io_cost)                               c5
 dba_hist_sql_plan p,
 dba_hist_sqlstat  st,
 dba_hist_snapshot sn
 p.object_name = '/BI0/PMATERIAL' 
 and p.sql_id = st.sql_id and st.snap_id = sn.snap_id     
group by
 to_char(sn.begin_interval_time,'mm-dd hh24'),
order by
 1,2,3 desc;  

P.S. About Exadata: do not mix HCC and DML, i.e., do not use HCC in OLTP.

  1. Hi Julian
    What is your recommendation to use HCC in EXADATA environment on X2-2 for datawarehouse.

  2. Hi Julian
    What is your recommendation for tables containing xml columns. We are planning to convert to LOB columns and use securefiles and use compress high for securefiles.

  3. Answer to both questions is: recommended.

    But when you store the XMLs as securefiles, use the CLOB option instead of the binary XML option in order to avoid the double compression which will have the reverse effect: the compressed XML will occupy more space than the non-compressed one. In other words use: “STORE AS SECUREFILE clob (COMPRESS HIGH)” instead of “STORE AS SECUREFILE binary XML (COMPRESS HIGH)”.

    • Thank You. The problem I have in implementing HCC is :
      1. I need to partition the table with old partitions as warehouse compression and new partition as oltp
      2. change the appln code to use new partition key
      3. what tables should I implement HCC for?

  4. Enable OLTP Compression on tables:

    I am trying to enable OLTP compression on tables and at tablespace level for the tables

    Steps I am following are:
    1. Move indexes to its own tablespace
    2. enable OLTP compression at table level:
    alter table table_name move compress for OLTP
    3. Rebuild indexes
    4. Issue I have is what to do with tables with LOB columns
    ALTER TABLE lob_table MOVE LOB (LOB_COL) STORE AS (TABLESPACE index_tbsp); — Is this correct?
    5. alter tablespace data_tablespace default compress for OLTP;

    I have a question, is the sequence of steps correct. For tables with LOB columns do we needto move lobindex to index tablespace. Beacuse lobsegment and lobindex are created in data tablespace?


  5. […] Tables with a compression ratio above 2.0. For doing to I wrote a small SQL script you can execute and it will tell you the compression ratio for a specific table. The SQL code is not my creation and I don’t remember where I found it, probable on Julian Dontcheff’s Blog: […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: