Monday, July 15, 2013

HCC Advisor

You can test the Exadata HCC compression on your production non-Exadata server (for example, on your HP Superdome or IBM 795).
It is free. It is safe. It is quick. It is easy.

Pre-Requisites: you have to choose the appropriate OWNER.TABLE and have enough space in the TABLESPACE. And decide,please, which one of type compression you will test
- Query Low
- Query High
- Archive Low
- Archive High
Take the dbms_compression.get_compression_ratio function for it, it has the name of "HCC Advisor"

Look at the example :


set serveroutput on
 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);
 scratchtbsname   => upper('&Tablespace'),  -- Tablespace Name   
 ownname          => upper('&UserName'),    -- USER NAME
 tabname          => upper('&TableName'),   -- TABLE NAME
 partname         => NULL,          
 comptype         => dbms_compression.comp_for_query_high, --compression type 
 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);
 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));

Estimated Compression Ratio: 13.6
Blocks used by compressed sample: 1041
Blocks used by uncompressed sample: 14251


Common Errors:
ORA-20000: Compression Advisor scratch tablespace cannot be UNIFORM tablespace
ORA-20000: Compression Advisor must have at least 1000000 rows in this


No comments:

Post a Comment

cellcli -e list diskmap

The interesting LIST DISKMAP command in the storage cells links all views to hard disks: PCI bus address as Name 252:5, OS name (/dev/sdh,...