4. Tune‎ > ‎

Visual SQL Tuning (VST)


  1. 1 Graphical Diagram                                                             
    1. 1.1 Detail verses Compact Mode
    2. 1.2 Join Information Highlighted
    3. 1.3 Understanding the Diagram
      1. 1.3.1 One-to-One                                                                                                     
      2. 1.3.2 One-to-Many                                                                                                              
      3. 1.3.3 Cartesian Join                                                                                                                                    
      4. 1.3.4 Many-to-Many                                                                                                                                    
      5. 1.3.5 Implied Cartesian Join                                                                                                                                   
    4. 1.4 Index Analysis                                                                              
      1. 1.4.1 Missing Index
    5. 1.5 View Expansion                                                                                
  2. 2 Analysis DB Optimizer 2.5                                                                      

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.

Graphical Diagram                                                             

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 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):

    investment_type it,
    office_location ol
WHERE investment_type_id = office_location_id;

Then graphically these would be laid out side by side  with a one to one connector:



Here is an example of a query that consists of only many-to-one joins, which is much more typical:

    client_transaction ct,
    client c,
    investment_type it,
    investment i
    ct.client_id = c.client_id AND
    ct.investment_id = i.investment_id AND
    i.investment_type_id = it.investment_type_id and

Cartesian Join                                                                                                                                    

For example this query is missing join criteria on the table INVESTMENTS:


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 

select *
from client_transaction ct,
     client c
where ct.transaction_status=c.client_marital_status;

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
investment i,
broker b,
client c
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.

Index Analysis                                                                              

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

Missing Index

    client_transaction ct,
    client c
    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:

View Expansion                                                                                

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.

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.pears = 'D'
AND a.green_beans = '1'
AND a.planted_date = c.planted_date
AND a.pears = c.pears
AND a.zuchinis = c.zuchinis
AND a.brocoli = c.brocoli
AND a.planted_date = d.planted_date
AND a.pears = d.pears
AND a.harvest_size = d.harvest_size
AND c.oranges = d.oranges
AND c.apples = d.apples
AND (d.lemons = 0 OR d.lemons IS NULL)
AND a.planted_date = g.planted_date
AND a.pears = g.pears
AND a.harvest_size = g.harvest_size
AND c.oranges = g.oranges
AND c.apples = g.apples
AND (g.lemons = 0 OR g.lemons IS NULL)
and a.zuchinis='0236'
ORDER BY a.zuchinis, a.brocoli;

Another simpler example:

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:
MOVIES.customer cs,
MOVIES.movierental mr,
MOVIES.rentalitem ri
LENGTH (cs.lastname) = 5 AND
cs.zip > 75062 AND
1 <>
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

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.