Oracle Musings

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.