It started simply.

Hi

I get this problem when I push this button.

Ten words; a cordial greeting, an issue, and the steps to reproduce, even an attached screenshot! Helen was the ideal client: quick, and to the point. Not meandering and filling up whitespace to get to a particular wordcount, wasting the reader's time with boring, extraneous detail.

The screenshot she'd attached showed an error unfamiliar to Joe B.:

ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small
ORA-06512: at our_form_key_commit_trigger, line 10.

Unfortunately, Joe (as we'll call him) is a web guy, responsible for fixing web issues in his little web world. While the error message may as well have been in Klingon ('ORA-01555: arlogh Qoylu'pu'? Hab SoSlI' Quch! "_SYSSMU6$" qoSlIj DatIvjaj 10), he was familiar with the module that was raising the error and at least wanted to investigate a little before handing it off to the Oracle guys. It was a part of a weekly job that crunched numbers for several reports. And according to their records, the job hadn't failed, so this error shouldn't have existed.

Reporting Or Whatever

There were dozens of little reports that could only make sense to management. Of course the main ones were obvious ("Project Balances"), but others either had confusing names ("Amortization by Defenestrated Capital") or were completely irrelevant ("Salary Change by Middle Initial over Year"). The report that Helen was trying to run was one of the more bizarre, how-could-anyone-possibly-care-about-this-report ones.

A search for "ORA-01555" retrieved a dizzying array of results, each with a dizzying array of steps to take to remedy it. Everyone's favorite well-dressed, redneck Oracle DBA suggests that you "shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation." I'd like to add that you may want to consider cross-triaging the logarithmic carburetor for optimal duractance. Joe was happy that at least some of the tips were more in his language, giving him a vague grasp of what the issue was. The stored procedure was simply too big, too complex, and dealing with too much data; the poor rollback segment was collapsing under the sheer volume of data.

Worse, the procedure in question's level of complexity was legendary. Not that Joe ever ventured into its definition; but it was widely whispered that one had better be pretty goddamned confident in their knowledge of PL/SQL, calculus, and jujitsu to even invoke it, let alone look at its code. And even if Joe knew how to fix it, he wasn't allowed to – this was strictly for the DBAs.

The DBA Response

Moments after opening a ticket, an MSN window popped up. It was Henrik, a DBA.

Henrik: checked everything and it's fine, typical sizes for segment and stuff
Joe: Then what can it be?
Henrik: proc it's calling must be a devastating one or something :] have her try again when there's less traffic or whatever
Joe: OK, sounds good.

Joe responded via email, notifying her that the job hadn't failed, but that it could be an issue with traffic, and asked her to try again later. At 6:00 PM, a response:

Hi

Same thing this time.

Damn.

A Devastating Procedure

It was already well past 6:00 and Joe just wanted to go home, yet this task kept him at his computer. Henrik had already left for the day, and Joe figured what's the harm in just taking a quick peek behind the curtain?.

Prepared for the worst, he opened the proc in Toad. His jaw dropped and eyes widened as though he'd seen a ghost. All of the warnings and legend surrounding the procedure could never have prepared him for this.

PROCEDURE statistics_and_stuff ( w_date IN DATE  ) IS
BEGIN
	NULL;
END;

Complicated for the Wrong Reasons

It was the simplest possible procedure, yet the most complex possible procedure. Every question that popped into Joe's head was answered by another question. Why? Who? Is it supposed to be like this? Should I restore from backup? How long has it been like this? He thought back to his MSN conversation with Henrik. The procedure's name, "statistics_and_stuff," followed the convention of every single one of Henrik's sentences.

Henrik said he wasn't responsible for the proc being empty or whatever, that it was probably Steve or another DBA or something. Joe considered restoring the proc from an earlier backup, but again, that was outside of what he's allowed to do.

And as far as Joe is aware, statistics_and_stuff still contains neither statistics nor stuff and has remained entirely unchanged (and the issue remains unresolved) to this day.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!