Visual Explain for Run SQL Scripts

Published: October 22, 2008

by Skip Marchesani

The Run SQL Scripts function, a.k.a. the SQL Script Center or Script Center, allows the user to execute all or a subset of a script that contains one or more SQL statements and/or batch CL commands. It is part of the Navigator Database function and is an extremely powerful and flexible tool with lots of function that can have a very positive impact on application developer productivity.

One of these functions is Visual Explain, which is used to investigate and improve the performance of SQL statements. Visual Explain creates a query graph or diagram that graphically displays the execution of a SELECT, INSERT, UPDATE, or DELETE SQL statement, and is very useful in helping to understanding the execution costs (performance) of a specific query. Visual Explain will also recommend any indexes that can be created to help improve SQL performance.

To see how Visual Explain can be used, let's look at executing a very simple SELECT statement using Run SQL Scripts. In the example shown in Figure 1, all columns are being selected from the table called NAMES, where the number in the column called LOG is greater than zero, and the results set is ordered by LOG number in ascending sequence. To run Visual Explain on a specific SQL statement, place the cursor on that statement or highlight the statement, and then click on Visual Explain in the toolbar.

Figure 1

When you click on Visual Explain, a drop down menu appears with the options Explain, and Run and Explain. If you choose Explain from the drop down menu, query optimization is performed and the query diagram is displayed along with the optimization information without actually running the query.\r\nIf you choose Run and Explain from the Visual Explain menu, the query is optimized and actually run before the query diagram is displayed along with any results set. The Run and Explain option may take substantially more time than just the Explain option, but the query diagram and associated information will be more accurate.

Initially for this example the Explain option will be selected, which means that optimization will take place, but the query will not be run. After selecting the Explain option, there will be a short wait until the Visual Explain window and query diagram in Figure 2 are displayed.

Figure 2

The left pane of the Visual Explain window shows the query diagram and the nodes in the diagram, and the right pane shows the detailed information for the highlighted node, which in this case is the Final Select node. There is one node displayed for each step in the query execution process. To highlight a node and display its associated information, just click on the node in the diagram. If you look at the last line under Time Information in the information panel, you will see that the estimated query runtime or execution time is 625.679 milliseconds.

If you scroll down to the bottom of the right pane, the last line tells us the query engine used was SQE, as opposed to CQE. This is shown in Figure 3. SQE is the new query engine and optimizer and is the preferred of the two engines. If you look at the query diagram in Figure 3 you will see that the first node is a table scan and the second node is a temporary sorted list. A table scan means that the entire table is being read in arrival sequence starting with row one and ending with the last row in the table. This is a performance scenario that is going to use a maximum of system resources to execute the query. In other words it's not going to perform very well.

Figure 3

Past experience with seeing the nodes Table Scan with a Temporary Sorted List tells me that this query is a good candidate to have its performance improved with the creation of an index. The next step is to click on the dancing feet on the right side of the bottom toolbar (Statistics and Index Advisor in Figure 3) to see what indexes, if any, that Visual Explain is recommending.

Click on the dancing feet, and then click on Index Advisor tab in the resulting Statistics and Index Advisor window as shown in Figure 4.

Figure 4

The Index Advisor is recommending that a Binary Radix Index (as opposed to an EVI or Encoded Vector Index) be created over the NAMES table. If there is more than one index recommendation in this window, select one and click on CREATE in the lower right corner of the Window.

When you click on CREATE, the New Index window is displayed, as shown in Figure 5, along with the detailed attributes of the recommended index. To create the recommended index, enter an index name on the first line--in this example the name is Log_Nbr--and the click OK on the bottom right of the window to create the index.

Figure 5

Once the index is created you are returned to the Index Advisor page to give you the option of creating any additional recommended indexes. In this case there are none, so you can close both the Index Advisor and Visual Explain widows to return to the Run SQL Scripts window.

Referring to the Run SQL Scripts window shown in Figure 1, click on Visual Explain on the toolbar and select Explain to run Visual Explain a second time to see the effect that the newly created index has on query performance.

The first thing you should note in the new Visual Explain window shown in Figure 6 is that the total estimated run time has dropped from 625 milliseconds down to just 64 milliseconds--almost a 10 to 1 improvement in query run time!

Figure 6

Next note that the query diagram has changed and that the first three nodes of the initial query diagram shown in Figure 3 have been replaced by an Index Probe and a Table Probe that tells us that the optimizer choose to use an index the second time, instead of doing the table scan the first time that Visual Explain was run.

When you Click on the Index Probe node to highlight it as shown in Figure 6, the detailed information and name of the index the optimizer choose to use is displayed. The first line under the heading Index Info tells us that the name of the index used is LOG_NBR. This is the index recommended by the Index Advisor and created in Figure 7.

Figure 7

You can verify the actual or real performance improvement by running Visual Explain a third time, and selecting the Run and Explain option.

Skip Marchesani retired from IBM after 30 years and is now a consultant with Custom Systems Corporation. He is also a founding partner of System i Developer and the RPG & DB2 Summit. Skip spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400 and was involved with the development of the AS/400. He was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Skip is recognized as an industry expert on DB2 for i and the author of the book DB2/400: The New AS/400 Database. He specializes in providing customized education for any area of the System i, iSeries, and AS/400; does database design and design reviews; and performs general System i, iSeries, and AS/400 consulting for interested clients. He has been a speaker for user groups, technical conferences, and System i, iSeries, and AS/400 audiences around the world. He is an award-winning COMMON speaker and has received its Distinguished Service Award. Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot