ChannelDB2

During searching for some good case studies about deployment DB2 compression in data warehouse environments, Google find
following document on Oracle website. It is description how compression works in Oracle 11g.

While this document have some minor errors inside (for example Gzip does not use block compression) i was quite surprised by presented Oracle compression ratio claimed. I could not believe it because i don't see massive deployments of Oracle Advanced Compression feature. On other side deployments of DB2 Storage Optimization are very common. My deployment observations also confirms Infoworld magazine. While Oracle 11g won one of 2009 Technology awards, person writing Oracle 11g review has doubts if money should be invested in advanced compression.

Compression test

I decided to make quick compression test to figure compression ratios myself.

Table compression

I took 2 tables with historical click data from e-shop and checked their compression ratio in DB2:

C:\IBM\SQLLIB\BIN>db2 select tabname,compress_attr,pages_saved_percent,rows_sampled from table(admin_get_tab_compress_info_v97('ZCOM',NULL,'ESTIMATE')) where object_type='DATA'

TABNAME
COMPRESS_ATTR PAGES_SAVED_PERCE
NT ROWS_SAMPLED
--------------------------------------------------------------------------------
------------------------------------------------ ------------- -----------------
-- ------------
CLICKS
Y
51 5886396
SEARCHES2
Y
35 93367897

Results are pretty good. On large table with 93M records DB2 compression saved 35% and on smaller table with 5M records 51%. These compression ratios are nothing unusual and are commonly seen in DB2 installations.

For compression testing In Oracle 11.1 i created tablespace with table compression enabled by default:

SQL> create smallfile tablespace comptest datafile 'C:\ORA11G\ORADATA\ORCL\COMPTEST.DBF' SIZE 5G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT COMPRESS FOR ALL OPERATIONS;

and then created second tablespace for storing uncompressed tables. I will get compression ratio by comparing table sizes in compressed and uncompressed tablespace. On Oracle 11.1 there is no comfortable way how to check compression ratio for table because DBMS_COMPRESSION package is new in 11.2.

I created compressed and uncompressed versions of both tables by loading CSV export from DB2. Oracle 11.1 performed loads very slowly compared to older Oracle 10g and DB2. DB2 9.7 was 4x faster and Oracle 10g was 6x faster. It means that Oracle 10 to 11 migration will need significant database and application retuning as usual. Because customers running 10gR2 have quite low interest in moving to newer database release (exactly opposite to DB2 9.7 adoption rate) Oracle company changed Premier (basic) end of support date for 10gR2 to 31-Jul-2011.

After import i checked table sizes (in 8K blocks) for both compressed and uncompressed version:

SQL> select segment_name,blocks from dba_segments where segment_name like 'SEARCHES%' or segment_name like 'CLICKS%';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS
----------
CLICKS2
88064

SEARCHES2
491520

CLICKS
83712

SEARCHES3
532480

Result is 4,9% space saving on CLICKS table and 7,7% savings on SEARCHES2 table. It is worth buying? I agree with most people that Oracle Advanced Compression which costs $11.5k per 2-cores on Intel compatible CPU is for most deployments just waste of money. It might be usable only on very large databases where even small compression translates to significant money saving. It is still better then no compression after all.

Index compression


Both DB2 and Oracle database also feature index compression. Both test tables have one indexed date column. In DB2 index is compressed very well:

db2 => select index_compressed,pct_pages_saved from table(admin_get_index_compress_info('I','ZCOM',null,null,null))

INDEX_COMPRESSED PCT_PAGES_SAVED
---------------- ---------------
Y 59
Y 59

Bylo vybráno 2 záznamů.

Almost 60% index space saved! This is even better then numbers presented in IBM materials related to index compression in DB2 9.7, which claims 50% as maximum possible compression for index.

In Oracle index compression gives good results too:

SQL> select segment_name,blocks from dba_segments where segment_name like 'SEARCHES%I' or segment_name like 'CLICKS%I';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS
----------
CLICKSI
9216

CLICKS2I
16384

SEARCHES3I
253952

SEARCHES2I
147456

44% for CLICKS index and 42% for SEACHES2 index. While these results are not that good as DB2 9.7 compression, they are still acceptable.

Conclusion

Simple test proved that DB2 9.7 compress data better then Oracle and compression ratios are high enough for making investment into DB2 compression feature worthwhile. Oracle did index compression well, but data compression performs poorly.

I did also some testing on query speed (not included in this blog post) on both compressed and uncompressed tables by measuring wall clock time against database stored on SAS drives. Using compression significantly reduced query time, which was expected because less data will need to be read from hard disks. Database compression is one of current trends in data management industry how to deal with ever increasing data volumes and all major database vendors are including some sort of compression. If you are not using compression yet you should start now.

Views: 1856

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service