During my present Exadata course in Munich, we have discussed Hybrid Columnar Compression (HCC) and how to estimate the size of tables after compression. In addition to my previous posting that introduced HCC, I have therefore prepared this little script that calls DBMS_COMPRESSION. Typically, the documentation also gives an example, but not in this case. Also my brief research in the web did not retrieve a (simple) example.
Please notice that you can use that script already on a non-Exadata Oracle Database (from 11.2 on) also to get an estimation about HCC, not only about BASIC and OLTP compression. The estimation is quite good but takes much space – about as much as the tables you estimate are in size. Therefore, it may be advisable to use a scratch tablespace only for that purpose and drop it afterwards.
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_query_high,
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));
end;
/
Addendum: See Jonathan Lewis’ nice cheat to keep the HCC compressed table that gets created during the evaluation under the covers – even if you are NOT running DBMS_COMPRESSION on Exadata!
Second addendum: This posting was published in the current Oracle University Newsletter.
#1 by Bhavik Desai on September 13, 2011 - 06:50
Thanks for the article Uwe..
I used this pl/sql block for one of my non-compressed OBIA object on exadata platform which is sized at 67 GB (partitioned table)
SQL> SELECT TABLE_NAME,PARTITIONED,COMPRESSION,COMPRESS_FOR,blocks,num_rows,sample_size FROM DBA_TABLES WHERE TABLE_NAME=’W_AR_XACT_F’ AND OWNER=’OBIA_PRD’;
TABLE_NAME PAR COMPRESS COMPRESS_FOR BLOCKS NUM_ROWS SAMPLE_SIZE
—————————— — ——– ———— ———- ———- ———–
W_AR_XACT_F YES 8713869 623831659 623831659
It ran for almost 45 min and gave me below output.
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Estimated Compression Ratio: 12.6
Blocks used by compressed sample: 6345
Blocks used by uncompressed sample: 80395
PL/SQL procedure successfully completed.
Can you help me understand what exactly it means ?
#2 by Uwe Hesse on September 13, 2011 - 08:54
It means that with HCC (compress for query high, if you used the exact same call like in the posting), your table will be about 12 times smaller in size – about 6 GB then. That ratio was derived by the number of blocks in the sample (80395) divided by the number of blocks, the sampled table took after compression (6345). This is a typical ratio, by the way.
#3 by baskardba on May 8, 2012 - 11:16
====================================================
1. Compression Ratio :20.1
2. Block Count :3696
3. Compression Type :”Compress For Archive High”
4. Blk Count Compressed :3696
5. Blk Count Un-compressed:74334
6. Row Count Compressed :271
4. Row Count Un-Compressed:157
====================================================
Table size is 35gb. The above compression Ration of 20.1 means the table will become 20 times smaller in size when compressed. Is that right ? from 35gb to around 3gb?
#4 by Uwe Hesse on May 8, 2012 - 11:52
Yes that is true. An impressive ratio but not so unlikely when your table has much redundancy in it like many similar strings – and Archive High is the option that compresses the strongest.