On new years eve I got a phone call from a company that had a performance problem. All of a sudden the batch job was no longer finishing and that gave problems because this process was supposed to update their website. So together with the customer we decided to start looking at this on January 1st 2009 (after lunch :)). I arrived at the house of the DBA and we looked at the batch and of course the error didn’t reproduce, so it was decided that I should return home. While driving home the batch hang again. So I instructed the DBA to find out if the batch was hanging in the database or on the client side. Turned out to be the client side. So the next couple of hours I looked at Precompiler bugs (hadn’t done that in a long time), as the batch program was written in Pro*C. While looking at the program, I was reminded of the fact that some of the Precompiler paramete have very strange (as in low) defaults. The following parameters maxopencursors (10), hold_cursor (no), release_cursor(no). These settings will help to increase the amount of soft parsing (if more than 10 cursors have to be open at the same time). Setting hold_cursor to yes and maxopencursors much higher should help to reduce the amount of soft parsing.
Back to the real problem. Because the production system didn’t have a Debugger installed (it was an AIX system), it was very difficult to find out where the process/program was hanging. But then I found the tool that I needed: procstack. With procstack one can dump the stack of a running process like this: procstack <pid>. Once we had that, it took less than 30 minutes to find the problem in the code and produce a fix. The program is now working again🙂