Recent site activity

4. Tune

Contents

  1. 1 Query Plan Transformation
  2. 2 Index Analysis
    1. 2.1 Missing Index
  3. 3 Visual SQL Tuning (VST)
  4. 4 Graphical Diagram                                                             
    1. 4.1 Detail verses Compact Mode
    2. 4.2 Join Information Highlighted
    3. 4.3 Understanding the Diagram
      1. 4.3.1 One-to-One                                                                                                     
      2. 4.3.2 One-to-Many                                                                                                              
      3. 4.3.3 Cartesian Join                                                                                                                                    
      4. 4.3.4 Many-to-Many                                                                                                                                    
      5. 4.3.5 Implied Cartesian Join                                                                                                                                   
    4. 4.4 View Expansion                                                                                
  5. 5 Analysis DB Optimizer 2.5    
  6. 6 DB  Optimizer 2.5 introduced statistics to the VST diagram:                                                           
  7. 7 Other Features for Oracle
    1. 7.1 Table Statistics
    2. 7.2 Column Statistics
    3. 7.3 Outlines
    4. 7.4 Explain Plans with Extended Row Source Statistics
      1. 7.4.1 Tuning by Cardinality Feedback

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.
          or 
          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.
          For a good description of the power of visual SQL Tuning, see Jonathan Lewis' article Designing Efficient SQL: A Visual Approach

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.


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


SELECT *
FROM
    client_transaction ct,
    client c
WHERE
    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)

Contents

  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                                                                                                                                   
      1. 1.4  View Expansion                                                                                
    4. Analysis DB Optimizer 2.5                                                                      

    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.

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

                              


                                   

    One-to-One                                                                                                     


    If  we joined two tables on their primary key (this doesn't make much sense in this case):

    SELECT COUNT (*)
    FROM
        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:

          
                  

    One-to-Many                                                                                                              


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

    SELECT
        ct.action,
        c.client_id,
        i.investment_unit,
        it.investment_type_name
    FROM
        client_transaction ct,
        client c,
        investment_type it,
        investment i
    WHERE
        ct.client_id = c.client_id AND
        ct.investment_id = i.investment_id AND
        i.investment_type_id = it.investment_type_id and
        client_transaction_id=1


    Cartesian Join                                                                                                                                    


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

    SELECT
        A.BROKER_ID BROKER_ID,
        A.BROKER_LAST_NAME BROKER_LAST_NAME,
        A.BROKER_FIRST_NAME BROKER_FIRST_NAME,
        A.YEARS_WITH_FIRM YEARS_WITH_FIRM,
        C.OFFICE_NAME OFFICE_NAME,
        SUM (B.BROKER_COMMISSION) TOTAL_COMMISSIONS
    FROM
        BROKER A,
        CLIENT_TRANSACTION B,
        OFFICE_LOCATION C,
        INVESTMENT I
    WHERE
        A.BROKER_ID = B.BROKER_ID AND
        A.OFFICE_LOCATION_ID = C.OFFICE_LOCATION_ID 
    GROUP BY
        A.BROKER_ID,
        A.BROKER_LAST_NAME,
        A.BROKER_FIRST_NAME,
        A.YEARS_WITH_FIRM,
        C.OFFICE_NAME;

    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:



    Many-to-Many                                                                                                                                    


    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
          i.investment_type_id=c.client_id;

                        


    The result set of  BROKER to CLIENT will be multiplied by the result set of INVESTMENT to CLIENT.

    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.


    View Definition

    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.


    Example
    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:
    SELECT
    cs.customerid,
    cs.firstname,
    cs.lastname,
    mr.rentalid,
    mr.duedate,
    mr.totalcharge,
    ri.itemnumber
    FROM
    MOVIES.customer cs,
    MOVIES.movierental mr,
    MOVIES.rentalitem ri
    WHERE
    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
    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


    Table Statistics


    Column Statistics


    Outlines


    Explain Plans with Extended Row Source Statistics

    Tuning by Cardinality Feedback

    See example on TCF SQL SCRIPT


    Addendum

         (old page     Graphic display of query )