Oracle Scratchpad

June 3, 2010


Filed under: Infrastructure — Jonathan Lewis @ 7:34 pm BST Jun 3,2010

I mentioned some time ago the presentation I did at Open World 2009 and Miracle Open World called “The Beginner’s Guide to becoming an Expert”. Over the last few months I’ve had a few people email me asking me if I’m going to post the presentation on my blog. The answer is no – because there isn’t a presentation.

Well, that’s not quite accurate. In the talk I did for Miracle Open World there was an opening slide which was a picture of the main quad of my old (400 years this year[1]) college at Oxford and I think there was one other slide, but I can’t remember what it was.

The rest of the talk was essentially discussion (and interrogation of the audience) about how you can learn what constitutes a reasonable amount of work for a given task if you set up lots of little test cases and look carefully at the results.

The example I had was a simple ‘update 1000 rows in a table’: how much redo and undo should you see appearing in v$mystat for a task like that ?

We went through lots of possibilities in the hour we had for the presentation.

  • start with no indexes on the table,
  • then try with an index on the column you update
  • then index a few other columns
  • what if you force Oracle to do a tablescan scan to do the update
  • what if you force the use of an index that’s being updated
  • what if you force the use of an index that’s not being updated
  • what if some indexes are bitmap indexes
  • what if you update “where rownum < N”
  • what’s the difference between 9i and 10g

If you can’t answer most of these questions, how do you recognise that the amount of undo and redo your database is generating is “wrong” and giving you a clue that something inefficient is happening.

If you want to be able to answer these questions then it shouldn’t take more than four or five hours to set up a suitable test case and produce some results. Of course, as you start testing, you might notice some oddities and want to introduce a few more ideas and special cases – make sure you take notes of what you’ve done.

[1] My son has just pointed out that this is still 50 years younger than the school ( = high school, if you’re American) that he’s attending.


  1. Treat Oracle as a science and not arts.Its all about connecting the dots….isn’t it Jonathan??

    Comment by Illiyaz Mohammad — June 13, 2010 @ 4:33 am BST Jun 13,2010 | Reply

  2. […] few days ago I was rehearsing a presentation about how to investigate how Oracle works, and came across something surprising. Here’s a simple bit of […]

    Pingback by Audit Ouch! « Oracle Scratchpad — May 27, 2011 @ 5:39 pm BST May 27,2011 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: