General Video on SQL Tuning
SQL Tuning from Kyle Hailey on Vimeo.
Video on Visual SQL Tuning (VST)
Tuner has three parts
1. Query rewrites, quick fixes and hint injection to find better execution plans
2. Index analysis to see if there are missing indexes or indexes not used that should be
3. Visual SQL Tuning (VST) diagram to quickly identify relationships, bottlenecks in the query and schema flaws
Tuning SQL - methodology
A) Verify that the execution path is the optimal for the query
If we find a better execution plan then the database by default is picking the wrong execution plan.
We can force the use of the better execution plan through hints, rewrites or transformations.
Identify why the native optimizer failed to pick the optimal path such as
- Lack of correct statistics on tables
- Skewed column data which can be corrected with histograms
- Correlated columns which may or may not be addressable depending on your database and version
B) Identify whether the correct indexes exist and are being used
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
The VST diagrams save hours of work by diagramming and visually displaying the relevant statistics and relationships required for analysis of the query.
DB Optimizer's SQL tuner
- quickly generates as many alternative paths as possible with hints, re-writes and transformations and allows the user to execute them to see if there are more efficient execution paths.
- identifies missing indexes and existing indexes that are not used in the query but could be.
- tuner graphical diagrams the query allowing quick visual identification of schema flaws and bottlenecks in the query.
How long would it take to check this by hand? Often hours and sometimes days for complex queries. DB Optimizers SQL tuner does it all in minutes.
Query Plan Transformation
The SQL tuner will take a query and add database optimizer directives (hints), rewrite the query and transform the query (quick fixes) 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.
The layout of SQL Analysis shows the SQL Text and SQL Diagram on the top and the indexes on the tables below.
(green = used by optimizer, blue = not used but usable, orange = recommended to create, grey = not usable in the query as written)
Indexes on the table are listed below the fields and color coded
Green=used in the query
Blue = usable but not used by optimizer in this case
Orange = suggest to create
Grey = exist on table but not usable in this query as it is written
ct.transaction_status = c.client_marital_status AND
c.client_first_name = 'Brad'
There is no index on CLIENT.CLIENT_FIRST_NAME and there are 5600 records in CLIENT, so DB Optimizer proposes creating and index:
The orange indicates that this is a missing index that we recommend to improve performance. Clicking on that index will give infomation on the rational to the right:
Visual SQL Tuning (VST)
DB Optimizer 2.0 introduced a new third tab* in the SQL Tuner, the ANALYSIS tab.
The ANALYSIS tab is available cross platform (SQL Server, Oracle, Sybase, DB2)
*In addition to the two pre-existing tabs, the INPUT tab (previously called Overview) and
OVERVIEW tab (previously called Generated cases)
The Analysis tab has 4 important components
1. Statement selector (if there are multiple statements in the tuning set)
2. Statement text for selected statement
3. Graphical diagram of the SQL statement
4. Index analysis of the SQL statement
Each section (text, diagram ,index analysis) can be resized or expanded to take up the whole page.
The graphical diagram is the newest and most exciting addition to DB Optimizer in 2.0.
DB Optimizer 2.0 will parse a SQL query and analyze the indexes and constraints on the tables in the query and display the query graphically such that
- Tables will be represented as nodes with the table name, table alias and optionally the schema
- Joins will be represented with connecting lines between nodes
- 1 to 1 join relationships will be graphed horizontally using blue lines
- 1 to many joins will be graphed with the many table above the 1 table.
- Many to many joins will be connected by a red line and the relative location will be not be restricted
- Nodes can be repositioned by the user (unrestricted), just click and drag
- The user will be able to zoom in and out on the diagram
- The diagram will have a compact mode displaying only table/view names and a detail mode showing indexes and fields
- Join connectors will have fly over help displaying the join text from the query
- Clicking on a join will highlight the fields in that join
- Clicking on an index will highlight the fields in that index
Detail verses Compact Mode
By default the diagram is displayed in compact mode just showing the table names and connectors, for example on the following page
The Visual SQL Tuning diagram compact mode, the default, looks like
but by hitting the expand all
icon, then the details of the tables will be shown including fields of the table used in the "where" clause and all the indexes on the table whether used or not:
Any particular table can be expanded or compacted by double clicking on the table/view name. Only fields that are used in the where clause are displayed in the detailed mode, but all the fields in the table can be seen by hovering the mouse over the table and flyover help will be displayed showing all the fields in the table.
Join Information Highlighted
When clicking on the connector line the fields in the join will be highlighted and the actual join details will be displayed in popup text:
Index Information Highlighted
clicking on an index will highlight the field(s) in that index
Understanding the Diagram
The diagram has a specific layout. There are 3 types of join connectors:
If we joined two tables on their primary key (this doesn't make much sense in this case):
Then graphically these would be laid out side by side with a one to one connector:
SELECT COUNT (*)
WHERE investment_type_id = office_location_id;
Here is an example of a query that consists of only many-to-one joins, which is much more typical:
ct.client_id = c.client_id AND
ct.investment_id = i.investment_id AND
i.investment_type_id = it.investment_type_id and
For example this query is missing join criteria on the table INVESTMENTS:
SUM (B.BROKER_COMMISSION) TOTAL_COMMISSIONS
A.BROKER_ID = B.BROKER_ID AND
A.OFFICE_LOCATION_ID = C.OFFICE_LOCATION_ID
And graphically this looks like:
We can see that INVESTMENTS is highlighted in red with no connectors to indicate that it is joined in via a Cartesian join.
Suggestions possibile missing join condtions will be suggested in the Overview Tab
under Generated cases under transformations:
If there is no unique index or unique constraint at either end of a join then we can only assume that in some or all cases the join is many-to-many (there is no constraints preventing a many to many). For example take this query
from client_transaction ct,
There is no unique index on either of the fields being joined so for all the optimizer knows this is a many to many join and is displayed graphically as:
Now if one of the fields is unique then the index should definitely be declared unique to help the optimizer
Implied Cartesian Join
If there are different details for a master without other criteria then it creates a Cartesian type join:
select * from
where b.manager_id=c.client_id and
The result set of BROKER to CLIENT will be multiplied by the result set of INVESTMENT to CLIENT.
If there are views in the Visual SQL Tuning diagram, the can be expanded by right clicking on the view header and choosing "expand view":
Default layout from query join table CLIENT to view TRANSACTIONS:
Right click on the diagram and choose “Expand View”
Now we see the objects in the view:
We can further expand the sub-view within the original view:
Here is an example of view expansion along with the explain plan to the left.
Notice in the view expansion we also list the indexes on all the underlying tables in the views and sub views and which of those
indexes is used in the default execution plan.
If we want to see the SQL statement that defines the view, then in detail mode, pass the mouse cursor over the view:
Analysis DB Optimizer 2.5
In DB Optimizer the diagram is context sensitive so if I click on a table in the diagram the text will be highlighted
DB Optimizer 2.5 introduced statistics to the VST diagram:
- blue numbers = percent of the table returned after the predicate filters have been applied
- numbers on join lines = two table join sizes
- green numbers = rows in table
The idea is to start at the most selective filter and then join into keep the running row set to the smallest size. In the above diagram we'd want start our query at CLIENT_TRANSACTION because it's the only table with a Filter which returns one row. From there it's down hill. The place we wouldn't want to start is with the join on INVESTMENT_TYPE and INVESTMENT because that join returns 415 rows of which we will then throw out all but 1 wasting the work of that join.
Start at A, the most selective filter
Join to C, the smallest running row set size
Join to G, the smallest row set size
Join last to D
This path was almost 3x as fast as the default path chosen by the optimizer. I just looked at the diagram , order the tables in that fashion and used the /*+ ORDERED */ hint
Interesting items in the diagram
- only fields used in the where clause are shown by default
- clicking on a link shows the fields used in the join (above I've clicked on two links higlight two joins)
- Fields with an "F" have a filter on them
There many other interactive features in the diagram.
The text for this query was
select distinct * from foo.a, foo.c, foo.d, foo.g
WHERE a.planted_date = to_date('02/10/2008','dd/mm/yyyy')
AND a.planted_date = c.planted_date
AND a.zuchinis = c.zuchinis
AND a.brocoli = c.brocoli
AND a.planted_date = d.planted_date
AND a.harvest_size = d.harvest_size
AND c.oranges = d.oranges
AND (d.lemons = 0 OR d.lemons IS NULL)
AND a.planted_date = g.planted_date
AND a.harvest_size = g.harvest_size
AND c.oranges = g.oranges
AND (g.lemons = 0 OR g.lemons IS NULL)
ORDER BY a.zuchinis, a.brocoli;
In the above diagram the #s in red are the two table join set sizes and the numbers in green are the table sizes. I can see immediately that the query should return 0 rows because the join (rentalitem,movierental) returns 0 rows. I can also see that the join (mr,movierental) is a horribly inefficient join it returns over 1 million rows for a join between 2018 rows and 2018 rows.
The VST diagram tells me immediately what's happening in the query and now with the statistics I can immediately see where there are efficient joins and inefficient joins.
The text for this query, which has none of this information is:
LENGTH (cs.lastname) = 5 AND
cs.zip > 75062 AND
cs.phone BETWEEN 9625569900 AND 9999569900 AND
ROUND (ri.rentalid) > 10 AND
TRUNC (ri.itemnumber) > 1 AND
mr.totalcharge > (SELECT AVG (totalcharge)
FROM MOVIES.movierental) AND
mr.CUSTOMERID = cs.CUSTOMERID AND
ri.RENTALID = mr.RENTALID
Big thanks to DB Optimizer's development team for this awesome work.
Other Features for Oracle
Explain Plans with Extended Row Source Statistics
Tuning by Cardinality Feedback