Ordinary Oracle Joe

Just an ordinary DBA's thoughts

Archive for July, 2009

The eureka moment

Posted by oakesgr on July 29, 2009

I think most people have had a point in their careers or lives when things changed significantly, this was mine…

I’ve worked with Oracle now for about 13 years, although not always as a DBA. When I look over the last few years I tend to classify them as BL and AL – Before Lewis and After Lewis. The Lewis in question is of course Jonathan.

About 7 years ago (ish) I was working as the sole DBA in a small software house in Harlow. We specialised in fraud detection software for telecomms corporations and had some exceptionally large clients around the world.

The solution we had relied on the ability to process millions of records in batches every two hours, whilst also providing users with real time, flexible reporting. It required some pretty extreme IO performance, but in the most part worked well. One particular issue we had that remained unsolved was that performance would gradually degrade over time. Despite a lot of speculation and some not too scientific testing of hunches, this problem refused to go away. The decision was made to ask a consultant to come in and help out.

Luckily, the consultant we asked for was Jonathan Lewis.

Back in those days I’d been on a couple of DBA courses, I’d been DBAing for a couple of years and thought I was reasonably competent. I didn’t rely on GUIs, I knew a lot of syntax and felt confident I could do most things. Then I saw how Jonathan worked.

Jonathan arrived at around 8:30 and was given a one hour presentation by the development manager on how the application worked. At one point in the presentation he raised his hand and asked a question, something along the lines of… ‘Will the data always be ordered by field x when queried?’. To which the answer was ‘Yes’. He paused for about 3 seconds then announced that he could easily reduce the amount of IO for this step by around 60%. (I can’t remember exact figures).

One hour later and I’m sat with Jonathan whilst we tested his statement. To my utter amazement, the stats showed that the operation was now using almost exactly 60% less. That to me was the Eureka moment. I wasn’t so much blown away by his ability to grasp the situation so quickly and propose the solution, but much more so by his ability to announce almost the exact savings that would be made.

Tuning had just turned from a dark art into a form of maths.

The next two days spent watching Jonathan work, opened my eyes to the empirical method of measuring, testing and proving a solution. It also taught me that the people at the top of their game could be extremely polite and helpful. At the end of his stay not only did he explain everything he’d done and how he’d done it, he also volunteered to leave behind his Swiss army knife of performance tuning / measuring scripts.

Anyway, since that time I’ve always tried to approach DBA tasks related to performance with that experience in mind. So thanks to Jonathan for putting me on the right track!

Posted in General chat | Tagged: , | 10 Comments »