Kevin Kempf's Blog

May 18, 2009

11i Compression – A First Look

Filed under: 11g, advanced compression, Oracle — Tags: — kkempf @ 2:07 pm

Took the plunge during the maintenance window this month and compressed the top 5 tables in my buffer cache.  Just compressing 5 tables I freed up 20-25% of my buffer cache (Oracle is managing my memory via AMM, and generally it allocates about 4gb to Shared Pool and 20 to the Buffer Cache)

Uncompressed Compressed
Table Block Count % in Cache Block Count % in Cache GB Saved
INV.MTL_MATERIAL_TRANSACTIONS 200563 100 99263 100 0.77
INV.MTL_TRANSACTION_ACCOUNTS 210645 100 67470 100 1.09
BOM.CST_ITEM_COST_DETAILS 256731 99 50159 100 1.58
INV.MTL_TRANSACTION_LOT_NUMBERS 118761 100 48175 100 0.54
WIP.WIP_TRANSACTIONS 102027 100 42543 100 0.45
Sum 4.43

* Calculations assume the uncompressed object was 100% in cache, so may be off slightly, and are based on an 8k block size in case you want to check my math.

First impressions? Everything is running fine, if a little heavier on CPU and lighter on I/O. It’s really hard to quantify this early in the game. Next maintenance window, I’ll probably compress 50 more tables, and ultimately we’ll be really close to a “11i Database in Memory” environment.

Advertisements

April 3, 2009

RMAN and Advanced Compression

Filed under: Oracle, RMAN — Tags: — kkempf @ 11:03 am

Since we’d purchased 11g Advanced Compression, I thought I’d compare the new ZLIB compression algorithm to the existing BZIP2 algorithm in RMAN.  As a reminder, Oracle licensing prevents you from using ZLIB unless you’ve purchased Advanced Compression, and the RDBMS compatible init parm must be set to 11.0.0 or higher.  In a nutshell, ZLIB compresses less, uses less CPU, and is faster, while bzip2 compresses better, CPU is noticeable, and it’s slow.  ZLIB seems like a good middle-ground (between no compression and BZIP2), though I have no idea why Oracle decided they need to charge you to use it.

The RMAN syntax is simple:

configure compression algorithm ‘ZLIB’;

configure compression algorithm ‘BZIP2’;

configure compression algorithm clear;

working with CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

The performance I saw was ZLIB was 15% faster, and 16% larger in output size when I ran a full backup of my EM repository (to disk)

Total Time Input Size Output Size Output Rate
BZIP2 214sec 4.7gb 664.1mb 3.1mb/s
ZLIB 179sec 5.62gb 1.07gb 6.12mb/s

Blog at WordPress.com.