The Rule of 5

During my 2006 Hotsos presentation I mentioned 2 “rules of 5″ that I like to use — I didn’t come up with them myself, but I’m pleasantly surprised when I find evidence to support them.  Of course, the human brain always finds evidence to support it’s own prejudiced hypotheses (for an excellent read that demonstrates this concept, try Focault’s Pendulum by Umberto Eco).  Anyway, the 2 rules of 5 are:

  1. Most people have 5 times as much hardware as they need (Tom Kyte)
  2. A useful tuning goal for SQL is 5 LIOs per row per row source (Cary Millsap)

Of course, you need to know what LIOs are — a depressingly larger and larger number of DBAs I meet don’t have the foggiest notion of them.

I point you at an excellent blog post by Shakir Sadikali at the Pythian Group which shows off a ten-node RAC cluster brought to its knees by unindexed foreign keys (doh!).  Fixing that and other tuning operations has allowed them to reduce the cluster down from 10 nodes to 2 nodes (or, 1/5th their original hardware).  Score one for #1!

BTW, most people argue #2 by talking to me about aggregates.  My standard response is that any aggregate that is queried heavily is an opportunity for derivation, pre-calculation or optimization.

4 Responses to “The Rule of 5”

  1. Chen Shapira Says:

    Interesting ideas. I’ll keep my eyes peeled for more anecdotal evidence to support these rules.

    Its doubly amusing because both Tom Kyte and Cary Millisap have reputation for never giving “rules of thumb” :-)

  2. Dominic Brooks Says:

    > you need to know what LIOs are ó a depressingly larger and larger number of DBAs I meet donít have the foggiest notion of them

    I find that more depressing/amusing/hopeful-for-our-long-term-careers than the fact mentioned in your previous article that more and more don’t know SQL very well.

  3. Marco Gralike Says:

    Logical IO – Check – I am still in (phuff, that was close)
    ;-)

  4. Log Buffer #88: a Carnival of the Vanities for DBAs Says:

    [...] Oracle Musings, Dominic Delmolino posits The Rule of 5. He writes, “During my 2006 Hotsos presentation I mentioned 2 ‘rules of 5′ that I [...]

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder