The Usual Suspects

We had a big database job that runs slowly at night. Sometimes it needs to be killed to keep the show running. My boss wanted me to work on this problem. He had a lot of ideas. So did I. My plan was to isolate what is slow, then figure out how to make it fast. I got overruled by the boss.

My first task was to document what this big database job does. Done. I got a document that explains in English what the purpose of each part of the job is. Then I was told to find out how each of the parts is related.

I did a bunch of analysis. I determined what pieces depend on which other ones. After some deep analysis, I figured out what are the inputs and outputs for each of the jobs. This was hard because the package that runs the job is 7k lines of code. That package makes use of a lot of other helper packages that are a lot more than 7k lines.

Now my new tasking is to figure out if we can roll any of the many cursors up into a small number of cursors. I have have to determine whether some database indexes could make the job run faster. I have the option to isolate some SQL and pass it off to some DBAs for analysis and tuning.

Who knows where all this is going to end up. We might get some performance gains. Then again, I might be just running around in circles. I would love to be given the mission to just make this thing fast. But somebody else higher up on the food chain wants to drive the task with their ideas. Frustrating. Let's hope this does not turn out to be a fail. My time is money - literally.

Record Types

Today is the last day for a developer on our project. He moved his family away, and has a new job far away. The company asked him to fix one troubling bug before he left. He was going crazy and asked me for some help. He set up some debugging in a PL/SQL package where he dumped out some contents to a log file. But they were just not making sense.

I took a look at his log. One of the variables was zero. He insisted that the source column for that variable was non-zero in the database. Then he started randomly asking whether this or that was the cause of the problem. I told him we should not guess, and inspect the place where the variable is initialized.

I found a nice routine which loaded the data from a table. It puts it into an object which is a custom record type. We select a bunch of fields into this object. The order of the select clause is supposed to match the order of records in the field. So I checked the one field in question. Oh oh. It was out of alignment.

Somebody added a new field at the end of the record. However the put the query for this new field in the middle of the SQL statement. Bamn. That's the problem. This was problably not a great design. We should make the select more explicit to put the value into fields of the record type. Maybe an idead for a redesign.

Package Analysis

We got this big PL/SQL package that contains code which runs every night. The thing does a lot of stuff such as probem detection/correction, business rule implementation, and metadata regeneration. The code has been growing over the years. This past year the job start running too long. We tried some hacks but did not get very far. We are still attempting some hacks to get the darn thing to complete on time.

Luckily I got assigned the task to analyze this monster. I started by documenting what each part of the massive job does. It was a reverse requirements analysis task. Then I started to catalog which tables each of the procedures used, and which tables got modified by the procedures. I am starting to come up with an understanding of what procedures depend on what other ones. Isn't there some software that can do this automatically for me? If not, might be time to write some.