Ordinary Oracle Joe

Just an ordinary DBA's thoughts

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!

About these ads

10 Responses to “The eureka moment”

  1. Doug Burns said

    Welcome to the wonderful world of blogging.

    If you actually ever get around to posting anything technical, do let me know ;-)

    (Relax folks, we know each other …)

  2. […] – if you don’t want to read a description of how truly amazing I am, don’t follow this link. Leave a […]

  3. mwidlake said

    Welcome to a very enjoyable (but time-consuming) pastime :-)
    Keep you blogs short, I keep getting told off as mine are too long. Doug can’t read them to the end without a break…

    • oakesgr said

      Thanks Martin, point noted on the short blogs. I’m trying to do something around baselines and thresholds but it’s turning into a monster – time to cut it into chunks!

  4. Todor Botev said

    Before Lewis and After Lewis – your phrase says it very nicely!

    I haven’t had the chance to work side by side with Jonathan like you. So for me it was a slower process, not an eureka moment. It was a continuous reading and re-reading of the Jonathan’s writings – books and blog posts – which have put me on a very different track.

  5. RJ said

    Welcome to blogging Graham … looking forward to more posts in the future.

    Raj

  6. oraculix said

    Graham,
    your article made me ponder for a few seconds if there was a point in time like yours for me. And yes, there was: I could divide my time into “before Method R” and “after Method R”. Being almost put on the right tracks by articles of JL, tkyte et al., the book “Optimizing Oracle Performance” finally gave me the last kick to a scientific approach in managing Oracle performance.
    So, thanks for making me stop and wonder and happy blogging!
    Uwe

    • oakesgr said

      Thanks Uwe. Method-R was another huge step forwards for me too. Cary Millsap’s (and let’s not forget Jeff Holt) is one of the few books I keep at work on my desk.

      Cheers
      Graham

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: