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.

February 16, 2010

IE8, Windows 7, and 11i compatibility

Filed under: 11i, IE8, Windows 7 — kkempf @ 9:41 am

After a maintenance window on Sunday, applying pretty innocuous RDBMS patches, I’m now getting a FRM-91126 invalid value timezlrg#dat for environment variable FORMS60_TZFILE error when I try to log into 11i from home (where I use IE8):

This is a known issue, and occurs because I haven’t yet applied (or regression tested) patch 8888184 “APPS6: MERGE LABEL REQUEST FOR BUGS 8645973 6210790 ON TOP OF 6.0.8.28”.  For a good note on OS/Ebusiness Suite/Browser compatibility check out 285218.1

Say what?

This used to work fine; something obviously changed as a result of restarts/patching though I did very little to apps at all, much less the forms server or 8.0.6 teckstack.  What I found interesting was that the error is intermittent.  We have a handful of Windows 7 machines here in the IT department, and they worked as expected without the patch.  Regardless, I tested the patch in my TEST environment, and the issue went away, so I can at least confirm the patch works.

Incidentally, if you have any Firefox 11i users (Linux people!) you may want to look at 7567782 “FF3 CERT: JPI-VERSION PARAMETER NOT SUPPORTED IN JRE6U10 FOR EBS11i”.  It appears that this lifts the requirement for a static Java version.  You already have this if you’re on ATG_PF_H RUP7.

February 15, 2010

Advanced Compression with 11i, Take 2, Day 1

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

So far, so good

After waiting about a year since my first attempt, the timing was finally right for me to patch the core 11.1.0.7 RDBMS with Patch 8277580: ORA-07445: [__INTEL_NEW_MEMCPY()+44].  This was Oracle’s fix for the data corruption I was seeing on my dataguard physical standby after I compressed BOM.CST_ITEM_COST_DETAILS (no other tables seemed affected in this way).

I had my doubts, but nearly 24 hours later, and several cost rollup runs have elapsed without incident. The raw stats look great:

OWNER TABLE_NAME                     VERSION         PRE_COMPRESSION_MB POST_COMPRESSION_MB COMPRESSION_RATIO
----- ------------------------------ --------------- ------------------ ------------------- -----------------
BOM   CST_ITEM_COST_DETAILS          11.1.0.7.0                 1717.38              429.88               .75

By the way, it’s amazing how much “stuff” touches that particular table in a discrete manufacturing 11i environment.  Can you tell when I recreated the indexes?

Rebuild your indexes!

February 10, 2010

The Endless Cycle of Upgrades

Filed under: Oracle, Support — kkempf @ 9:19 am

Start planning for your next upgrade the day you finish your current one

Lately, I feel like I’m doing an awful lot of Oracle upgrades (or at least planning for them) merely for the sake of upgrading.   In other words, there’s nothing wrong with the current version, but support reasons are beginning to be my driving cause for RDBMS upgrades.  Specifically, I’m talking about 10g to 11g upgrades; let’s take a look at the facts.  Oracle 10gR2 has a GA (General Availability) date of July 2005.   Oracle generously granted a year of free premier support last year, making premier support good through July 2010.   That means that their plan was for 4 years of premier support, and if you assume it takes at least 6 months to get the “latest” RDBMS tested and put in your production environment, you get 3.5 years before your next upgrade.  That simply doesn’t feel like a long time to me, but it seems to be the model for both Oracle Extended and Microsoft Extended:

Oracle Extended Support:

Program releases eligible for Extended Support will receive Software Update License & Support limited to the following:
• Program updates, fixes, security alerts, and critical patch updates
• Tax, legal and regulatory updates
• Upgrade scripts
• Major product and technology releases, which includes general maintenance releases, selected functionality releases, and documentation updates
• Assistance with service requests 24 hours per day, 7 days per week
• Access to My Oracle Support and Relsys Customer Support Portal (24 x 7 web-based customer support systems), including the ability to log service requests online
• Non-technical customer service during normal business hours

Extended Support does not include certification with new third party products/versions


Microsoft Lifecycle Support:

Support provided Mainstream Support phase Extended Support phase
Paid support (per-incident, per hour, and others) X X
Security update support X X
Non-security hotfix support X Requires extended hotfix agreement, purchased within 90 days of mainstream support ending.
No-charge incident support X
Warranty claims X
Design changes and feature requests X
Product-specific information that is available by using the online Microsoft Knowledge Base X X
Product-specific information that is available by using the Support site at Microsoft Help and Support to find answers to technical questions X X

In other words, after our premier/mainstream support is done (4 years for Oracle, 5 for Microsoft) you will probably pay a little more, and we’ll continue to do pretty much everything we used to, except enhancements.

I got hit with a curve ball yesterday morning reading Steven Chan’s blog in regards to Discoverer.  Turns out, AS 10g and therefore Discoverer 10g (which we just got to about a year ago) falls off of premier support in December 2010.  Yet another upgrade which I have to perform, not because there’s any functional/security/design flaw, but because it came up on Oracle’s “list”.  In this instance, Oracle doesn’t even have a certified replacement available yet.  That’s right, Discoverer 11g (a part of Fusion Apps) is not 11i certified.  So basically, we’re under the 1 year mark, and there’s no upgrade path.  Logically, I’d like to think Oracle would just extend premier support for another year for AS10g, but who knows.

I contacted my Applications sales rep about a year ago in regards to the cost of keeping extended support for 11i (we have no compelling business reason to move to R12, nor do we particularly want to incur the cost in the midst of a recession).  The response was that the first and second years cost premier support +20%, and subsequent years were premier support +30% (until November 2012 when Extended Support ends).   I shutter to think of the contract rewrites for this, but perhaps it’s just another line/fee.

February 8, 2010

Congratulations to the team which won the big end-of-season football game which was held in South Florida!

Filed under: Uncategorized — Tags: — kkempf @ 2:14 pm

Not Fully Licensed

This is a brief aside from my normal techno babble.  I won’t be providing a breakdown of my (useless) analysis of the game, but I found it sad that the NFL makes it so hard to use the phrase “S**** B***”.  Thanks to lawyers and property rights held by the NFL, this post title is about all a big company with something to lose might be able to say in print.   It makes me fear for the freedom to post anything but doublespeak online, because of the fear of repercussions.

As a side note, am I the only one tired of “Who Dat” and “Geaux Saints”?    Who Dat sayin’ Geaux Saints?  Oh yeah – the guy with the 4th grade education!  Well that’s what it sounds and reads like to me…

Create a free website or blog at WordPress.com.