Not just a free lunch

When I first learned to drive, all our cars were automatics – and so that’s all I learned to drive. This never posed an issue to me until I married Rosalie who owns a manual. She tried to teach me, and I managed – kind of. It wasn’t until I had a chat with my uncle-in-law, who’s a mechanic, that it “clicked” with me. This is because he explained to me basically how the gear-change system works, which meant I now had a picture in my head of what was going on “under the hood” when I pushed the clutch in – it didn’t take long before I was smoothly changing gears and not making my passengers lurch forward and back like someone on a bucking bull.

I think that’s the way I learn things – I need to have a mental model of how a device is working internally before I can really use it effectively. To be useful the model doesn’t have to be greatly detailed or even perfectly accurate – it just has to be good enough that I can predict or guess what effect different actions will have on the equipment.

Today I enjoyed Richard Foote’s talk debunking a raft of common index-related myths today, followed by Penny Cookson sharing about bind peeking, and Connor McDonald giving some creative demos of RAC.

There was some material covered in part from recent articles on Richard’s blog but he explained clearly the mechanics of Oracle’s B-Tree and bitmap indexes, including how index statistics affect query plans. One highlight for me was the clear explanation of how the clustering factor (CF) is calculated, what it means and how to use it to diagnose why an index is not being used. I think I understand the CF much better than I did before because Richard explained it in a way similar to the way my uncle-in-law explained the clutch in a car – i.e. he explained how Oracle calculates it and uses it internally. He could have just said “a good CF is one that is close to the number of blocks, and a bad CF is one that is close to the number of rows”, but instead he explained how it works, which means this rule-of-thumb becomes more self-evident, and a whole lot more meaningful and useful.

Other highlights:

the mere existence of an index can change query plans dramatically, even if the index is not actually used by the query;

adding a ‘ ‘ (a string containing just a space) as the second column in an index on a nullable column means that all the nulls will be stored in the index; and

Penny gave a quick overview of the problems of bind variable peeking, including a good explanation of why many 8i-to-9i upgrade projects experienced initial performance problems. There were some excellent tips for those of us not yet blessed with 11g on how to resolve (or at least work around) the problem of sql plans lurking in the shared pool, intermittently messing with query performance.

When we returned from afternoon tea for Connor’s talk, confronted with a table ominously equipped with ropes, children’s toys, toilet paper and lollies, we were treated to a talk which, by Connor’s standards, was a raving endorsement of RAC – by which I mean he did allow that it could very well be of benefit at some Oracle shops. Certainly the FUD surrounding RAC seems to have been cleared a bit.