Perceived Performance Problems

Our software team often has difficult problems so solve. We maintain a system with a lot of users and a ton of data. Recently a certain functionality in our application has gotten terribly slow. The users don't even try to perform those functions any more. It is a difficult problem because we cannot make it happen in a development environment. We tried to add some logging but it did not shine any more light into the situation. The last resort was to allow a developer to run the application in a production environment. But it seems to take an act of congress to authorize this to happen.

Some consultants came up with a plan. They would fly out to the user's site. And they would see what the user was experiencing. Then at the same time an Oracle performance consultant would run some tools on the back end database to monitor what was going on. This would identify the problem, or at least the problem area, if in fact the problem exists on the database side. We make a couple calls to some stored procedures in a PL/SQL package. One of these calls could be hanging. I really hope this is not some weird hang up on the workstation. Because then this consultant test will give us no new information (other than they could not locate the problem).

Saved by Documentation

This year my team was tasked with making some complex changes in our system. These changes needed to be made deep in our PL/SQL packages that run as a part of our nightly process. Since I am the one who has been around the longest, and I am also one of the few who actually know how our nightly process works, I got assigned this task.

Our requirements team documented the needed changes as best they could. And I questioned them mercilessly until they got all the details from the customer. Then I went off and coded up a number of changes to meet the specification as I understood it. Unfortunately I made some incorrect assumptions about the values set by some other parts in the system. As soon as this code hit production, the trouble tickets started coming in.

As this was my code, I jumped in and determined what was wrong and coded up some PL/SQL code changes. Our process requires us to conduct unit testing. So I doctored up some data to cover all the test cases. There was only one problem. The code I implemented is only supposed to be run on Wednesdays. And today is a Thursday. No problem. I just modified the code to run every day while I did my unit tests.

Another part of our process is to document all changes so that they can be peer reviewed. Often times this seems like unnecessary overhead. But today it saved the day. As I was generating the documentation, highlighting the portions that I changed, I noticed that the code was written to run every day. Oops. Luckily I caught it before peer review, and more importantly, before we shipped this code to Production. If that had gone through there would have been all kinds of fireworks. We do have other safeguards such as independent testings. But I am not sure they would have caught this given the rush on the fix.

I can safely say that I was saved today by our standard development process. Maybe I also need to look into other ways to test "Wednesday only" functionality without hacking up the code. What do you think?

Development Versus Run Time

Our customer had some high priority problems. The original developer working on the problem was not making enough progress. So the problem got reassigned to me. I had a late breakthrough on the problem. Apparently one of our application's metadata tables got out of sync. So I needed to write a script to fix the data. The challenge was that it was very late when I made the discovery. And the customer wanted the fix now.

Here are some more details on the problem. We have a main table with read-only data. It has a lot of records (maybe 30 or 40 millions records and growing). The application makes use of a second database table containing meta data with around 10,000 records. Unfortunately there were multiple problems with the meta data. Some of the records did not belong in the table. And other records were missing.

Given such small amount of time to write a fix, I just hacked out a brute force method. I removed all the meta data records. Then I went through the 40 million records in the source data and reconstructed the meta data table. I did some quick performance tests and they looked ok. So I am planning to ship the fix as is. The main driver of this design was the limited development time. Had I more time to work on the script, I could have built in some smarts which only added and removed a small amount of records. However this would have taken much longer to code and test.

This is usually the trade off in software maintenance. I hope the script runs fast enough when it gets deployed to Production.

Fixing a lot of data

We have some PL/SQL code which computes a lot of values on a daily bases. Right after the SQL*load, the PL/SQL goes to work and performs the computations. Then the client applications use these computed values on the display screens. Nothing special here.

I got a trouble ticket stating a new computed value was not being set. After checking the production data, I found that this was true. Found out that the wrong version of the package was compiled into the database. So I spoke with the team who controls the PL/SQL package install. Sure enough the instructions were correct in thier release notes. But the wrong version was compiled in. The immediate problem could be fixed by compiling the correct version of the code. But the total fix required going back and recomputing the values for all the data loaded so far. We got the computation wrong for about 15 million records.

My first cut of a script to fix the existing data was to run a cursor through the 15 million records. Then I would execute a bunch of logic to figure out which records needed updating. Then I would perform the update for each record. After completing version 1, I worried that this script might take a long time to run. So I spent some time looking at the Production data. And I was able to determine that I could put some conditions in my cursor that would bring the total record count down to about 40 thousand. Now I was on the right path.

Unfortunately my first set of unit tests on random data showed I had some bugs. My code that retrieved the data to determine the computations made some wrong assumptions. So I was trying to SELECT some data that had no records. In my early days as a programmer, I would just first SELECT the COUNT, then SELECT the data if the COUNT was greater than 0. But this is doing double work. So instead I coded a CURSOR which opened and fetched. If I got any data from the fetch I did my computations. No need for a separate COUNT. I checked for the existence of the data, and retrieved all in one step. This can speed things up when you are dealing with a lot of data.

40 thousand records is not huge. But it is big enough that you want to consider performance enhancing features. The only drawback to my work was that it took up a good deal of development time. Now is the busy season for my customer. So I had to wrap up this task and move on to the next problem to solve. I am glad that I forced myself to spend a little extra time to get the solution right. In fact while looking at the Production code to get some ideas for acceleration, I noticed some other related problems that I solved in the same script. I'd say I earned my pay today.

Tracking Weird Problems

I got word that a bug in our software was becoming high priority. Because of this problem, our users had to do a lot of extra work. This is never a good thing. And it was especially troubling since the beginning of the year is the busiest time for our client.

The users were so busy that at first I could not get any information about the problem. So I just started working on other issues. But then some important customers were saying these problems were really holding them back. So I tracked down some users and got some info from them.

Apparently some nightly jobs were not inserting records as expected. At first glance, it looked like records were being inserted. But talking as I talked with a few users, I found that not all expected records were being populated. I had a hard time tracing the actual code for this nightly job. Last year somebody made some changes and did not check them into source control. Shame on them.

I had to go back to our delivery of the changes for the code last year to get a hold of the source. Luckily I found one pattern for the missing data. An error code was being stored in a look up table. This was the start of a break through. The only problem was that the source code showed that the problems were supposed to be logged each time the error code was set. I could find no evidence of such logs in production.

Next step was to try to replicate the problem. This was harder than it seemed. The nightly job was something we were not set up to test. Somebody once had an idea that we should have a test bed. But it never got further than an idea. I had a DBA help out and try to start creating users to match production. No luck. I got bold and ran a few tests in production. This also proved to be a dead end.

By now I had to resort to an old fashioned technique. I went to trace the code and results by hand. Went through an example of some data which got an error code set. Kept building up the huge SQL statement that actually inserts the data. Then I got an Oracle error. Apparently the users had entered some SQL into the system that did GROUP BY and ORDER BY operations. The code tries to append WHERE clause data at the end. But this cannot come after the GROUP BY or ORDER BY in a SQL statement. I got the users to clean up their SQL.

In retrospect, this ordeal made me realize a few things. We need an adequate test bed. We need production to log errors diligently (still working on this). And finally the software should not allow users to enter data that will result in middle of the night errors. Time to implement some validation.

Basic Failure

I knew something was wrong when a couple different developers on the project asked me if I was good at SQL. We have full time DBAs. We have consultants from Oracle. But I still got the call.

Apparently a script was not working correctly in development. It worked fine in Production. But a developer needed to do some tests in development and things were going weird. There was a column IDENTIFIER in a table defined as VARCHAR2(5). And they tried to run a query like this:

SELECT COUNT(*)
FROM table
WHERE identifier='00350';

The problem was that the count was returning 0. However there were many records that had the specific identified the WHERE clause was comparing. This was just blowing the mind of the developer. Surprisingly when he removed the single quotes from the WHERE clause, the count came back correctly.

We tried a number of experiments to determine the source of the problem. For example, we inserted a new row with the same identified. Then the count returned 1 (not 51 as we expected). However updating all 51 records setting the identifier to the quoted value did not change the query results. Somebody thought maybe this was due to a bad index on the column. But this column was not a part of any index. We also tried creating another database table with a similarly defined column, but we could not replicate the problem with this other table.

My conclusion was that something went wrong when these 50 records were put into the table. I could not explain why. We loaded the data doing a sql*load like we always do. For now to get the fix out the developer changed the source code to not use quotes. This is just a hack until we can get a grip on this perplexing issue. Any thoughts?

Fast to Slow

I work on a complex system. We have huge amount of PL/SQL code running at night to do all sorts of tasks. One such task is performing number crunching and storing results in meta data tables. The applications consult these tables during the day to get information quickly.

Our database is a very large one. And the data keeps growing. So the number crunching job kept getting longer and longer. I asked for help from the performance engineering team at our company. Their first idea was a divide and conquer method. So I wrote some korn shell scripts that run 10 jobs in parallel, crunching portions of the data at the same time. There was not a 10 for 1 speed improvement. But it was close. We had a very happy system administration staff working at night because now our jobs finished quickly.

When we started up the system the following year, the number crunching routine slowed back down to old levels. Our chief DBA assumed that we somehow deployed the old code. I had him check the Production korn shell and PL/SQL packages. The correct code was there. So I called in the performance engineering team. They monitored the job the next night. The job finished in record time. Performance engineering said perhaps the gathering of statistics first was the key. Whatever the reason, I am glad we are back to quick speeds.