So whats there in "SQL Tuning", apart from simply staring at the query for half a day?

here is my first question from one of my collegues..(thanks much for getting me started)

"
General and basic idea about how to do tuning, what are the methods available, when to do full table scan ?,
when to use RULE hint, what are the things to keep in mind when working with high volume data.
How often we should do "analyze". Writing efficient SQL, like when to use where clauses, what not to use, etc.....
"

------------------------


General & basic idea -

• dont go by time, look at actual work done (less is better)
• Look at waits (less waits = more scalable)
• dont start with a mindset - "My query should be using an Index..because Indexes are always good"

Methods available to tune -

• Explain plan shows what path was chosen by Optimizer to execute the sql
• Autotrace - shows the actual workload (how many blocks read, Physical IOs required, Sorts done etc)
• SQL_TRACE - actual process in database is traced and all performance metrics are dumped into a trace file (in database server)

When to use Hints -
Preferred answer : never (in an ideal Oracle world)
Practical answer : when you know optimizer is not generating the right plan for one of the reasons below

1. statistics insufficient to tell the optimizer the correct data distribution
2. when you have a better idea (functionality wise) on the data which optimizer would never figure out from stats
3. incorrect init parameters set by DBA (which makes the optimizer to always lean towards unbalanced execution plan)
4. quick fix - when you know how the optimizer should work..but dont have a clue or enough time to investigate why its not
doing so by default
5. After upgrades,if any sql is not working the optimal way - possibly due to bugs

It would get too long to get into each one of the sub-item listed above and explain what that means..(too much theory is always boring to me)..so lets leave that aside for a while and get into some real ACTION..

Hang on with your seat belts fastened..

More in next

PS: I know, not everything in the question (RULE hint, analyze etc) was answered..we will cover it in the later posts to come