Kevin Kempf's Blog

July 22, 2009

Advanced Compression … not ready for prime time?

Filed under: 11g, advanced compression, Oracle — Tags: — kkempf @ 7:50 pm

First, a little background. We have a highly customized bolt-on application in 11i for automated data collection (barcode scanning) and some label generation which we uniformly despise as an IT group. Functionally, it does the job well enough, and makes the job on the floor more accurate, faster, and in truth probably a bit easier for the manufacturing workload. We despise it from an IT perspective because it’s buggy, runs on a Windows (read: inherently unreliable) application server, and appears to be built on some combination of DOS, VB, and dot net framework. It requires 2 additional Oracle databases to run (the ERP, somehow, manages to run on only one…) and if I had to guess has caused 95+% of our unplanned downtime in the last 2 years. Our favorite part is that it has a sort of ad-hoc query component which can cause some of the worst explain plans I’ve ever seen. It was no surprise, then, that today it caused me more grief.

EM started showing high “concurrency” contention that tied to an object in our custom schema which clearly made this bolt-on the culprit. More technically, this was buffer busy waits, and I couldn’t get a good grasp on what had changed. As per standard operating procedure, I restarted the application server (which often fixes problems since it’s so unstable) but this failed to shake the issue. I then bounced the Windows application server it runs on, but this, too, to no avail. It was churning through DML transactions with many blocking sessions and buffer waits.

When I finally got a look at the alert log, I see this entry (note: this is not a typo, it really says “Compressionion“):

ORA-00600: internal error code, arguments: [OLTP Compressionion Block Check], [5], [], [], [], [], [], [], [], [], [], []

It spit out a trace file which was effectively unreadable to me (hex and block dumps, no human readable text), and tkprof showed nothing. On a hunch, I uncompressed the table at the center of this issue. For now, this seemed to have resolved the issue. I don’t think I’d describe the nature of the updates to this table as OLTP, but, to be fair, it is a reasonably volatile table which was compressed, and I thought this may be the issue. Despite myself, I opened an SR with the alert log, and trace file, just to see what Oracle thought. No word yet, but when I searched Metalink/My Oracle Support for the term “OLTP Compressionion Block Check” or, what I thought was the correct error, “OLTP Compression Block Check” I had 0 hits. None. Guess we’re on the bleeding edge of Advanced Compression. Seriously, what the heck is a Compressionion? It’s almost laughable, are we so far on the cutting edge even the error messages/code blocks haven’t been proofread?

Advertisements

Create a free website or blog at WordPress.com.