Tuner has three parts
1. Query rewrites and quick fixes
2. Generating alternative execution plans via optimizer directives
3. Analysis of Query showing
- Indexes used, not used, missing(suggested to create)
- Graphic display of query
The SQL tuner will take a query and add database optimizer directives to change the execution path of the query. A list of all the unique execution paths will be generated with all duplicates eliminated from the list. The final list of alternative paths can be executed. Any path that takes more that 150% of the base case will be canceled because we are only interested on paths that could be faster than the base case so no need to waste time and resources continuing to run cases that are slower than the original. After the cases have been executed they can be sorted in order of elapsed time. If a better path is found then those optimizer directives can be included in the original query to achieve optimal response time.
Tuning SQL - methodology
A) 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
B) If the query is still slow then look at adding indexes C) If the query is still slow, then you know you are going to have to look a the architecture
- what information is the query trying to get
- is this information necessary
- are there alternative ways to get this information
DB Optimizer's SQL Tuner can help with A and B. Step C will have to be done by a developer or DBA but knowing that step A and B have already been validated can indicate to management that step C is necessary rand there for allocate sufficient resources for step C.
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 suboptimal execution path.
A query has a sub-optimal execution path when the database optimizer has miscalculate the cost of the various possible access paths and mistakenly choose a bad path. The access path calculations can be miscalculated because
- 1. The table/index statistics are missing or wrong (ie the number or rows is missing or way off, for example)
- 2. The data is skewed, ie the number of orders with an open status is usually low compared to all the ones that have a closed status because the work is complete (ie orders get filled every day, but only a few are open and needing to be processed). Looking for open orders should probably use and index and return fewer rows than looking for closed orders which should probably just do a full table scan.
- 3. The predicates used are correlated. The optimizer treats two predicate filter on a table as more selective than just one, but this is not always the case like how many Swedes speak Swedish which is basically returns the same number of results as just asking for Swedes alone. Another example is how many Swedes speak Swahili which is probably way more selective than the optimizer would guess.
- 4. A bug in the optimizer
DB Optimizer's SQL tuner will take a query and try 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 it's execution is stopped and we move on to the next case. This avoids wasting time and resources on execution plans that are clearly suboptimal.