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.

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