Exadata HCC compression live example and ratios

Hi,

I implemented HCC compression of partitioned table recently for one of my client. The results i got for compression ratio was tremendous and thought of sharing with you to show live compression ration.
(before enabling HCC compression i would suggest to go through this post once, this will tell what you should check before implementing HCC on partitioned tables)
In Exaadata HCC we have following ways to compress the data according to our requirement and design of business.

1) Compress for Archive high
2) Compress for Archive Low
3) Compress for Query high
4) Compress for Query low

And of course we have OLTP and basic compression too. But above are only relevant when Exadata is in place.

So lets see how much space can be freed up when using HCC.  Please be noted that time (to compress)and resource required to top to bottom varies i.e from Archive high to Query low.
The least resource consumed is with Query low and highest with Archive high. I have also considered the time it takes to implement the HCC compress and these time also varies from we go from top to bottom.
Thanks of checking this post :)


Below is my partitioned table and its current size and size of each individual partitions:

Elapsed: 00:00:00.00
15:59:10 SQL> select partition_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART' group by partition_name;

PARTITION_NAME                 SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
XXX_TRANSACTION_PART_ONE                          4.875
XXX_TRANSACTION_PART_THREE                     .6796875
XXX_TRANSACTION_PART_FO                          2.1875
XXX_TRANSACTION_PART_FA                      2.45397949
XXX_TRANSACTION_PART_TWO                     2.40252686

Elapsed: 00:00:00.09

Total size is:

16:01:09 SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION' and owner='BOB_UAT_ODS';

SUM(BYTES/1024/1024/1024)
-------------------------
                13.078125

Elapsed: 00:00:00.06


Let see for Compress for Archive High option, how much it saves out space:

16:01:33 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_ONE compress for archive high parallel 12 update indexes;

Table altered.

Elapsed: 00:01:21.03


16:04:12 SQL> select partition_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART' group by partition_name;

PARTITION_NAME                 SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
XXX_TRANSACTION_PART_ONE                     .433959961
XXX_TRANSACTION_PART_THREE                     .6796875
XXX_TRANSACTION_PART_FO                          2.1875
XXX_TRANSACTION_PART_FA                      2.45397949
XXX_TRANSACTION_PART_TWO                     2.40252686

Elapsed: 00:00:00.09

Wow, Its almost 90% reduce, so final size we got is only 10%. Look at first partiiton i.e. XXX_TRANSACTION_PART_ONE


Now its time for Compress for Query high:

16:04:17 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_TWO compress for query high parallel 12 update indexes;

Table altered.

Elapsed: 00:00:19.74

16:05:06 SQL> select partition_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART' group by partition_name;

PARTITION_NAME                 SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
XXX_TRANSACTION_PART_ONE                     .433959961
XXX_TRANSACTION_PART_THREE                     .6796875
XXX_TRANSACTION_PART_FO                          2.1875
XXX_TRANSACTION_PART_FA                      2.45397949
XXX_TRANSACTION_PART_TWO                     .303161621

Elapsed: 00:00:00.02

So for this option was save from 2.4G to .30G, so i believe its compressed down to 12%?


Now lets see if i move this compression i.e. Compress for Query high to Compress for Archive high, how much further i can release space?

16:05:13 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_TWO compress for archive high parallel 12 update indexes;

Table altered.

Elapsed: 00:00:44.22

16:06:27 SQL> select partition_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART' group by partition_name;

PARTITION_NAME                 SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
XXX_TRANSACTION_PART_ONE                     .433959961
XXX_TRANSACTION_PART_THREE                     .6796875
XXX_TRANSACTION_PART_FO                          2.1875
XXX_TRANSACTION_PART_FA                      2.45397949
XXX_TRANSACTION_PART_TWO                     .238586426

Elapsed: 00:00:00.02

So its .2G again, Remembr initally size was 2.4G and final size from Compress for Archive is only .2GB which is 10%

Now let check for Compress for Archive LOW:

16:06:32 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_FA compress for archive low parallel 12 update indexes;

Table altered.

Elapsed: 00:00:22.52
16:07:22 SQL> select partition_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART' group by partition_name;

PARTITION_NAME                 SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
XXX_TRANSACTION_PART_ONE                     .433959961
XXX_TRANSACTION_PART_THREE                     .6796875
XXX_TRANSACTION_PART_FO                          2.1875
XXX_TRANSACTION_PART_FA                      .289978027
XXX_TRANSACTION_PART_TWO                     .238586426

Elapsed: 00:00:00.02

hm, we got .28G out of 2.4 which is more the 10% right? But not exact 10% which we saw with archive high.


We now check Compress for Query low, which will offer the lowest compression ration among others:

16:07:29 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_FO compress for query low parallel 12 update indexes;

Table altered.

Elapsed: 00:00:11.06
16:08:03 SQL> select partition_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART' group by partition_name;

PARTITION_NAME                 SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
XXX_TRANSACTION_PART_ONE                     .433959961
XXX_TRANSACTION_PART_THREE                     .6796875
XXX_TRANSACTION_PART_FO                       .42767334
XXX_TRANSACTION_PART_FA                      .289978027
XXX_TRANSACTION_PART_TWO                     .238586426

So we see around 20%?


Elapsed: 00:00:00.02

Lets check the data dictionary to see the compression type used

16:08:06 SQL> select partition_name,compression,compress_for from dba_tab_partitions where table_name='XXX_TRANSACTION_PART';

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
XXX_PART_MAX                  DISABLED
XXX_TRANSACTION_PART_FA       ENABLED  ARCHIVE LOW
XXX_TRANSACTION_PART_FO       ENABLED  QUERY LOW
XXX_TRANSACTION_PART_ONE      ENABLED  ARCHIVE HIGH
XXX_TRANSACTION_PART_THREE    DISABLED
XXX_TRANSACTION_PART_TWO      ENABLED  ARCHIVE HIGH

6 rows selected.

Elapsed: 00:00:00.08

How much we save total data after above compression?

16:08:38 SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART';

SUM(BYTES/1024/1024/1024)
-------------------------
               2.06988525

Elapsed: 00:00:00.02

How about the Index and its partitons?

16:09:19 SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PI';

SUM(BYTES/1024/1024/1024)
-------------------------
                3.5333252

Elapsed: 00:00:00.09

Size remain same :)

Now i'm going to un compress :)

16:11:11 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_FO nocompress parallel 12 update indexes;

Table altered.

Elapsed: 00:00:17.64
16:12:04 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_FA nocompress parallel 12 update indexes;

Table altered.

Elapsed: 00:00:12.55
16:12:29 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_ONE nocompress parallel 12 update indexes;

Table altered.

Elapsed: 00:00:20.78
16:13:02 SQL> alter table bob_uat_ods.XXX_TRANSACTION_PART move partition XXX_TRANSACTION_PART_TWO nocompress parallel 12 update indexes;

Table altered.

Elapsed: 00:00:13.59
16:13:29 SQL> select partition_name,compression,compress_for from dba_tab_partitions where table_name='XXX_TRANSACTION_PART';

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
XXX_PART_MAX                  DISABLED
XXX_TRANSACTION_PART_FA       DISABLED
XXX_TRANSACTION_PART_FO       DISABLED
XXX_TRANSACTION_PART_ONE      DISABLED
XXX_TRANSACTION_PART_THREE    DISABLED
XXX_TRANSACTION_PART_TWO      DISABLED

6 rows selected.

Elapsed: 00:00:00.08

Here we go, size is increased from 2.06GB to aorund 12G

16:13:33 SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PART';

SUM(BYTES/1024/1024/1024)
-------------------------
               11.8543701

Elapsed: 00:00:00.02

Again size remein same for index

16:13:37 SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name='XXX_TRANSACTION_PI';

SUM(BYTES/1024/1024/1024)
-------------------------
               3.53338623

Elapsed: 00:00:00.08


Elapsed: 00:00:00.00
16:14:34 SQL> select partition_name,compression from dba_ind_partitions where index_name='XXX_TRANSACTION_PI';

PARTITION_NAME                 COMPRESS
------------------------------ --------
XXX_PART_MAX                  DISABLED
XXX_TRANSACTION_PART_FA       DISABLED
XXX_TRANSACTION_PART_FO       DISABLED
XXX_TRANSACTION_PART_ONE      DISABLED
XXX_TRANSACTION_PART_THREE    DISABLED
XXX_TRANSACTION_PART_TWO      DISABLED

6 rows selected.

Conclusion:- We have seen already how much data can be saved and its depends upon the type we use to compress the data. However there are few glitches when implementing HCC on table which has non partitioned primary key setup. Which is shared in my next blog.