11i Compression – A First Look

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.

RMAN and Advanced Compression

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