Oracle Performance and Engineering

Much of the field of "Performance Tuning" or "Optimization" translates to "fire fighting" in today's terms (sadly!). As it happens when faced with such situations, different random permutations of options are tried to solve the issue. In chaos, one thing may work. Not much later that becomes a panacea. Whereas something really different, or may be something not-so-directly affected by the change may have addressed the issue, the legend is already born.

There goes the origin of myths like -

(1) You should have 20 Rollback segments
(2) Rebuild Indexes every Sunday at 10AM (lots of overtime for the consultant DBA :D)
(3) Indexes, and tables should be contained within minimum number of extents, if not one.
(4) Indexes and tables should be kept on separate tablespaces, since that kinda allow database to read tables and indexes in "parallel".
(5) EXISTS is better than IN
(6) UNION is better than OR
(7) Never OUTER JOIN!
(8) Response time is a linear function of data volume (wow!)
(9) When"hit" ratios are 99.99% or so, go home...and so on...

Believe me, I could go on and on.

Tom Kyte and some others have been doing great work in debunking the myths everyday. But they just keep coming back. Tom, for example, doesn't believe in "Rules of Thumb" (appropriately abbreviated as "ROT"). I like the fact that he actually asks "so how much work is really done" rather than, "let's eliminate all the full-table scans because you know, full-table scans are ugly". Well, like Ogres full-table scans also come in layers.

Just today I was going through some SQL best-practice guide

The problem with these best practice authors is they generalize too much. Every system is unique. You cannot just extrapolate stuff from one and apply on another. Well, you can. But then let's not claim that's a "solution". Let's say that was a "try worth making".

So in this document I see some interesting stuff. It says - "Use EXISTS / NOT EXISTS instead of IN / NOT IN". Really? This is wrong. Completely wrong.

Not only it's a lie, but sometimes they bring back different data as well.

And this example of generalizing a particular "workaround" as a "cure" was from the content page! More on that paper soon.