Isn’t that always the way with Oracle tuning? The really dramatic improvements are often the easiest to find and implement. Tuning tasks that are highly restrictive, complex and only offer a small scope for performance gains may be the most challenging but the least appreciated. Finding the “low hanging fruit” (beneficial, cheap and low risk) is justifiably what customers want first.
Here are two contrasting examples from my experience:
An Easy Win
We had just taken over support of a customer’s Oracle database from a competitor. It was a terabyte database on Oracle 8i and Windows (32-bit). When doing the initial audit, I was shocked to learn that their monthly billing process ran for two weeks solid! This meant no outages, slow performance for OLTP users, and if something went wrong, they’d only just have time to run it again before the next billing process needed to start.
It was obvious to me that before we could start making other improvements highlighted in our audit, we’d need to get the billing process down to a reasonable length of time. Sure, the platform was hardly appropriate for the amount of data and processing, but two weeks to process one batch job each month was ridiculous.
It took a few minutes to spot two inefficient SQL statements and to recommend a couple of indexes that ended up reducing the monthly billing run to less than a day. The only problem was that the companies being billed were surprised to get their bills earlier. 😉
This kind of miracle tuning is welcomed by the customer and looks great on a CV, but it was embarrassingly easy.
A Hard Win
In complete contrast to the previous example, this one took a lot of work for small but necessary gains. A different customer had a legacy monthly billing process that was very poorly designed, and was taking longer each month as the data grew. The core engine could not be altered; only tweaks were possible. The application was due to be replaced in the new year, but until then, we were tasked with keeping it running within a window of a weekend. (Users could not be in the system during this monthly billing process, so it had to complete within a weekend).
This application had already been through iterations of tuning, so there were no easy and quick wins left. It took a lot of analysis and work to save 10% here and 5% there, just to hold a steady run time each month. We tuned multiple layers: the hardware, storage, OS, Oracle parameters, data density, SQL, indexes and finally the PL/SQL packages.
Although the results were appreciated by the customer, and provided me with job satisfaction, stopping an application from slowing down doesn’t grab attention in the same way as the easy win described above.
How All this Relates to the HP Database Services Blog
Big and easy wins are often not so technically interesting or challenging, and are usually well covered in books and forums and other blogs. Therefore, it is tempting to just write about more difficult or rare performance problems. However, “low hanging fruit” are commonly sought after, so I’ll try to keep this in mind for future posts.
Here’s a start in that theme.