Kevin Kempf's Blog

May 11, 2010

Advanced Compression and Interface Tables

Filed under: 11g, 11i, advanced compression — kkempf @ 2:57 pm

Advanced Compression – More Stories from the Front Lines

We’re several months into advanced compression, after the Dataguard bug/debacle, and we’re really quite happy with the product.  Practically speaking, the ERP runs from the buffer cache, and thus our I/O is down to a trickle.  As a matter of course, I began to compress tables, in mass, during the evening in an effort to just “get everything compressed”.  Not that the gain is so good (most of the big tables are done) but in the interest of standardization.  I don’t want to have to go look and see “is it compressed?”.  Recently, however, I arrived one morning to a standard email report I have delivered which told of dozens and dozens of lines stuck in the INV.MTL_TRANSACTIONS_INTERFACE table.  For those not familiar with discrete manufacturing, this is the table where material moves and completions are processed among other things (due at least in part to Highjump, a bolt on application which allows workers to use wireless hand held terminals to do their job).  In other words, in a manufacturing environment, it’s a busy place and it needs to work correctly.

Hello, ORA-00600

The alert log doesn’t lie.  This entry corresponded to the failure times of the material transactions:

Errors in file /u01/appprod/oracle/proddb/diag/rdbms/prod/PROD/trace/PROD_ora_8556.trc  (incident=9989):
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/appprod/oracle/proddb/diag/rdbms/prod/PROD/incident/incdir_9989/PROD_ora_8556_i9989.trc

The trace file revealed the RDBMS file# and block#; running that to ground confirmed that it was the INV.MTL_TRANSACTIONS_INTERFACE table.   Searching Metalink/MOS on the term KDSGRP1-1 (and changing the default search so it included bugs) , I found bug 9072813.  This was for an HP environment, but was such a similar hit that I pursued the “base bug” 8771916 which, although you couldn’t see the bug, had a patch corresponding to it.  A very, very recent patch, entitled “8771916: ORA-00600 [KDSGRP1] WHEN DOING AN UPDATE”.

Summing it all up

The first thing I did was to uncompress the MTL_TRANSACTIONS_INTERFACE table.  It went from 16 blocks back to 800, and .13 Mb back to 6.25 Mb.  That fixed the issue, but there’s more to the story.  The bug in question appears to pop when there’s massive write contention to a table (read: block).  I had just reduced the number of blocks in this table 50 fold; that’s bound to cause more contention.  In the end my fix was to also apply patch 8771916 to my production environment, knowing there could be other tables where this could occur.  If you’re running Advanced Compression, I’d urge you to at least look into the bug/patches.  If you’re running 11i and compression, I’d definitely avoid compressing interface tables.

Advertisements

Create a free website or blog at WordPress.com.