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**) 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.
** And my son has just pointed out that this is still 50 years younger than the school (viz: high school, if you’re American) that he’s attending.