Quick Fix

There had been some high priority customer problems in the morning. I got called into some meetings. The customer had an idea to shut off a type of transaction until we figured out what was wrong. I said that would work. Managers and executives from the customer organization spent a lot of time deliberating over what to do.

At the end of the day, my boss asked me to join another conference call. They wanted the transactions halted. My boss thought we could just put some trigger on the table containing the transaction requests. I was tasked with figuring this out and shipping it immediately.

This seemed easy enough. I wrote an after insert trigger for each row. In the trigger I moved the inserted row to another table. Then I deleted the row from the table on which the trigger fired. Boom. You can't do that. You get an error that the table is mutating, and the trigger cannot operate on that data.

I looked around. The net had some ideas to store the data from the row level trigger in some structure. Then a statement level trigger could gather that data and operate on it. Shoot. I did not have time for this nonsense. But I know a bit about how this data gets inserted. Every row affected happens in its own statement. Therefore there is a one to one correspondance to the row and statement level triggers. Bamm.

I moved everything into the statement level trigger and was off to the races. Polished up my scripts and sent them over to test. The test team took a long time to do their tests. Our database team built a database release. It got sent out to production in the middle of the night. I was up until 3am to make sure things went smoothly. They did. This morning the customer was relieved to find the transactions halted.

Now we need to investigate the root cause of the problem. Somebody else got assigned that task. As for me, I worked only about half a day today. I was up all night. No need putting in any more hours for now.

External Tables

We frequently gets requests from our customer to do mass updates. In the past, I would just take the data and generate a SQL script from it. Seems legit. This worked up to the point where we started getting too much data. Then I started splitting the jobs up into multiple scripts. Things got out of hand when I needed 15 or more scripts to do the job. Each script itself took a long time time run.

External tables came to the rescue. Now I put the data from the customer in a file on the server. Then I create an external table backed by that file. SQL*Loader does the job of reading in that file and exposing it to me like a normal database table. Now I can't do any updates on this table. However I don't need to. I just use it as input to update the database. I loop a cursor through the records, make my updates, and the job is done.

The beauty of this solution is that it is very fast. The script technique took a long time to execute when the scripts were large. The external table technique hardly takes any time at all.

Performance Tuning Triggers

I had to test some fucntionality that essentially hands with large volumes. The first test ran for over 4 hours. That's when I killed it. I was hoping it would eventually finish. No such luck. I took the sample size down by 99%. The thing ran for 30 seconds. Then I bumped up the sample size 10 times. It ran for 10 minutes. That's not linear.

There was mass hysteria on the team about there being many triggers on the table getting updated. I did a performance test, enabling only one of the triggers at a time. Turns out only one trigger causes any performance change. I studied the code of that trigger, and found that it updated a lot of records for each update. Not good.

My rewrite was to disable the trigger during the large update. However I did not want to disable the trigger for all users, just the one doing the update. There seems to be no Oracle support to disable a trigger per session. So I used Murnane's technique to implement a disabled trigger per session. I tested this out with some profiling SQL scripts. Now I am putting this in the application. Let's hope for speedy returns.

Divide and Conquer

My customer is doing an acceptance test of my latest code changes. Things are not working well. Initiially I found that they were getting blocked by one of the business rules in the system. Then I determined they were running the wrong scripts. Finally the code was getting an Oracle error.

The pressure was mounting. I got on a conference call with a bunch of managers. I needed to figure this out fast. The script that was bombing was not huge. But the SQL in it was massive. Lots of joins and unions and large predicates. Ouch. How was I going to figure this out?

Then I decided to try to find a shortcut. The call stack said a function in a PL/SQL package was actually generating the exception. I checked and found we got lucky. There were only two places where the script accessed the package. Bamm. We were trying to construct a date with a value that is sometimes aritificially too large.

Sometimes you can get saved by a little divide and conquery.

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.