How to use compression advisor on Exadata?

Compression advisor is used to estimate compression ratio of a particular table by executing compression advisor utility.

Benefits:
It can be used to estimate compression ratio of object/table , based on that we can go for the best possible compression method i.e. OLTP, Query Low, Query High, Archive Low and Archive High.


It also helps us to decide whether we will get optimal benefit of compression or not.

We can come to know how much storage free space we would get if we compress the tables which helps us to do better capacity planning for future requirement.

Following are the steps which need to performed to get the compression ratio of any object.



1. First we need to download compression utility which is available on following link,  based on Oracle software version on which you want to get estimation of compression ratio

Compression Advisor Package


2. Move it to Exadata database server

3. Uncompressed compression advisor which we have downloaded from above url in zip format

$unzip compression-advisor.zip

It will create three files.

dbmscomp.sql
prvtcomp.plb
readme.txt


4. Login to the database where compression advisor need to be execute.

5. Execute dbmscomp.sql and prtcomp.plb on that database to create compression advisor packages.

SQL> @dbmscomp.sql

Package created.

Synonym created.

Grant succeeded.

No errors.


SQL> @prvtcomp.plb

Package body created.

Synonym created.

Grant succeeded.

No errors.

6. Execute compression advisor procedure

Here we have two methods to execute compression advisor.

Method : 1

This one is used to get ratio by provide full table data that means it will get compression ration based on analysis of whole table data.

SQL> set serveroutput on
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('COMPRATIO', 'COMPTEST', 'COMPTESTTBL', NULL, DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH, 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 = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/

Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
Block count compressed = 5175
Block count uncompressed = 28373
Row count per block compressed = 193
Row count per block uncompressed = 35
Compression type = "Compress For Query High"
Compression ratio = 5.48270531400966183574879227053140096618 to 1
Compression ratio org= 5

PL/SQL procedure successfully completed.


Compression type (DBMS_COMPRESSION.COMP_FOR_OLTP) you can mention any of the compression method like OLTP, QUERY_LOW, QUERY_HIGH, ARCHIVE_LOW, ARCHIVE_HIGH which you want to test.

Method -2

In this method you can provide number of rows or you can say provide sample of table data in form of rows of that table to estimate the compression ratio so it will provide compression estimate based on the number of rows given. 

It can be used to reduce time to get estimate as well it will not use whole table to get compression ratio.

SQL>set serveroutput on
declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('&ScratchTBS'),
ownname => user,
tabname => upper('&TableName'),
partname => NULL,
comptype => dbms_compression.comp_for_oltp,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str, subset_numrows=>&num_rows );
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/

Enter value for scratchtbs: COMPTEST
old  10: scratchtbsname => upper('&ScratchTBS'),
new  10: scratchtbsname => upper('COMPTEST'),
Enter value for tablename: COMPTESTTBL
old  12: tabname => upper('&TableName'),
new  12: tabname => upper('COMPTESTTBL'),
Enter value for num_rows: 20000000
old  20: comptype_str => v_comptype_str, subset_numrows=>&num_rows );
new  20: comptype_str => v_comptype_str, subset_numrows=>20000000 );
Estimated Compression Ratio: 2.2
Blocks used by compressed sample: 4752795
Blocks used by uncompressed sample: 11037198


scratchtbs is the scratch tablespace name where compression adviser take your data and perform the compression.

We have done compression for OLTP, same you can perform for other type of compression by changing below parameter.

OLTP - comptype => dbms_compression.comp_for_oltp
Query Low - comptype => dbms_compression.comp_for_query_low
Query High - comptype => dbms_compression.comp_for_query_high
Archive Low - comptype => dbms_compression.comp_for_archive_low
Archive High - comptype => dbms_compression.comp_for_archive_high


We have executed compression adviser for each compression method , below is the compression ratio which we got for a table size of 150 GB.


Num_rows which we have given was 20000000 to get compression ratio sample and total number of rows were 711820137 of a table.


List of reference commands for HCC implementation.


Create table with EHCC

CREATE TABLE ... COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH];

Convert Table to compression

ALTER TABLE table_name MOVE COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];

Convert Table partition to compression

ALTER TABLE table_name MOVE PARATITION partition_name COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];

Uncompress Table

ALTER TABLE table_name MOVE [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>];

Uncompress Partition


ALTER TABLE table_name MOVE PARTITION partition_name [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>]; 

No comments:

Post a Comment