DB Optimizer 2.0 introduces a new third tab* in the SQL Tuner, the ANALYSIS tab.
The ANALYSIS tab is available cross platform
*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
will be represented as nodes with the table name, table alias and optionally the schema
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
can be repositioned by the user (unrestricted), just click and drag
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 looks like
but by hitting the expand all
icon, then the details of the tables will be shown including fields of the table and indexes on the table:
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.
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:
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”
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.
Analysis DB Optimizer 2.5
In DB Optimizer 2.5. we have now added
- 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
In DB Optimizer the diagram is context sensitive so if I click on a table in the diagram the text will be highlighted
Big thanks to DB Optimizer's development team for this awesome work.