Kevin Kempf's Blog

February 22, 2010

The incredible shrinking database

Filed under: 11g, 11i, advanced compression — kkempf @ 8:56 am

It’s been a week since I applied RDBMS patch 8277580, and compressed the one table which was causing data corruption on the standby database (INV.CST_ITEM_COST_DETAILS).   I’ve been watching very closely, and the behavior I saw last spring has not come up again.  So I’ll give credit where credit is due and say the fix Oracle provides in the patch works as advertised, although be it a year late.

Yesterday during our quiet period (we’re a 24/7 shop, but on Sunday afternoons there are only a half dozen folks logged into the ERP), I began compressing more tables.

My general approach is pretty simple:

1. Determine what tables are consuming the most space in the buffer cache

2. Compress those tables & rebuild their indexes

  • alter table X compress for all operations move;
  • alter index X rebuild;

3. Repeat

There were 2 exceptions to this; first, I had to compress BOM.CST_ITEM_COST_DETAILS first because I needed to confirm that the bug fix worked.  Second, I got the notion that I should take my largest non-sys/system table (INV.MTL_CYCLE_COUNT_ITEMS) and compress it, to see how it went.   The results are pretty convincing:

OWNER TABLE_NAME PRE_COMPRESSION_MB POST_COMPRESSION_MB Disk Savings MB % In Buffer Cache Buffer Cache Savings MB
INV MTL_CYCLE_COUNT_ITEMS 18833.75 3082.25 15751.5 0.01 157.52
INV MTL_MATERIAL_TRANSACTIONS 2859.13 925.5 1933.63 1 1933.63
INV MTL_TRANSACTION_ACCOUNTS 2002.63 625.38 1377.25 0.97 1335.93
BOM CST_ITEM_COST_DETAILS 1717.38 429.88 1287.5 0.47 605.13
INV MTL_TRANSACTION_LOT_NUMBERS 1209.5 443.63 765.87 0.98 750.55
WIP WIP_TRANSACTIONS 904.63 377.75 526.88 0.99 521.61
AR RA_CUSTOMER_TRX_LINES_ALL 312.13 118 194.13 0.99 192.19
WSH WSH_DELIVERY_DETAILS 330 167.5 162.5 1 162.5
PO RCV_TRANSACTIONS 211.38 62.88 148.5 0.98 145.53
AR RA_CUSTOMER_TRX_ALL 166 37.63 128.37 0.99 127.09
WIP WIP_OPERATIONS 236.88 126.75 110.13 0.96 105.72
APPLSYS WF_ITEM_ACTIVITY_STATUSES 123.5 33.88 89.62 0.94 84.24
WIP WIP_REQUIREMENT_OPERATIONS 165.38 83.38 82 0.99 81.18
INV MTL_SYSTEM_ITEMS_B 382.5 384.5 -2 1 -2
ONT OE_ORDER_LINES_ALL 314.88 320.5 -5.62 0.99 -5.56
SUM 22550.26 6195.26

In short, after compressing just 15 tables I’ve saved 22.5 GB on disk and 6GB in my buffer cache.   A hidden bonus is the index space savings, but I don’t have numbers on that (yet).   It’s too early to make any generalizations about the server and it’s workload, as the cache needs to “right” itself after a weekend.

Advertisements

Blog at WordPress.com.