A common SQL tuning methodology is to:

  1. Verify that the execution path is the optimal for the query. If not, either use the tuning directives (such as hints on Oracle) or identify why the native optimizer failed to pick the optimal path.
  2. If the query is still slow, then look at adding indexes.
  3. If the query is still slow, then you know you are going to have to look at the architecture.

Ask yourself:

DB Optimizer's SQL Tuner can help with steps 1 and 2. Step 3 must be done by a developer or DBA, but knowing that steps 1 and 2 are already validated can indicate to management that step 3 is necessary and therefore allocate sufficient resources for step 3.

How do we know if the native database optimizer chose the optimal path? How long would it take to check this by hand?

DB Optimizer's SQL Tuner is a solid fast sanity test to verify the plan chosen by the native database SQL optimizer. Tuner quickly generates as many alternative paths as possible and allows the user to execute them to see if there are more efficient execution paths. DB Optimizer's SQL Tuner is successful at tuning queries that have a sub-optimal execution path.

A query has a sub-optimal execution path when the database optimizer has miscalculated the cost of the various possible access paths and mistakenly chosen a bad path. The access path calculations are sometimes miscalculated because of the following reasons:

DB Optimizer's SQL tuner takes a query and tries to produce as many execution paths as possible. These alternative execution paths can then be run to see if there is a faster or less resource expensive execution path. The execution of each alternative case is timed and if the execution exceeds 1.5 X the original case then its execution is stopped and we move on to the next case. This avoids wasting time and resources on execution plans that are clearly sub-optimal.


 |    |  |   |   |