Administration Guide

Analyzing SQL Statements

You can view the access plan for explained SQL statements as a graph and use this information to tune your SQL queries for better performance.

An access plan graph shows details of:

• Tables (and their associated columns) and indexes

• Operators (such as table scans, sorts, and joins)

• Table spaces and functions

Prior to Version 6, you would use a tool called Visual Explain to view the access plans. In Version 6, you can no longer invoke Visual Explain as a separate tool from the command line, however, you can still invoke the visual explain function from various database objects in the Control Center and from the Command Center. In this section, the term visual explain function is used for this capability.

You use the visual explain function to:

• View the statistics that were used at the time of optimization. You can then compare these statistics to the current catalog statistics to help you determine whether rebinding the package might improve performance.

• Determine whether or not an index was used to access a table. If an index was not used, the visual explain function can help you determine which columns might benefit from being indexed.

• View the effects of performing various tuning techniques by comparing the before and after versions of the access plan graph for a query.

• Obtain information about each operation in the access plan, including the total estimated cost and number of rows retrieved (cardinality).

Improving Performance of a Query

You use the visual explain function to analyze and tune SQL statements. It presents a graphical view of the access plan for explained SQL statements. Tables and indexes, and each operation on them, are represented as nodes, and the flow of data is represented by the links between the nodes. You can use the information available from this graph to find ways to tune your SQL queries for better performance.

The visual explain function captures information about how SQL statements are compiled. This information allows you to understand the plan and potential execution performance of SQL statements.

This information can help you:

• Design application programs.

• Design databases.

• Understand how two tables are joined: the join method, the order in which the tables are joined, the occurrence of sorts and type of sorts.

• Determine ways of improving the performance of SQL statements (for example, by creating a new index).

• View the statistics that were used at the time of optimization. You can then compare these statistics to the current catalog statistics to help you determine whether re-binding the package might improve performance. It also helps you determine whether collecting statistics might improve performance.

• Determine whether or not an index was used to access a table. If an index was not used, the visual explain function can help you determine which columns could be included in an index to help improve query performance.

• View the effects of performing various tuning techniques for the purpose of better performance by comparing the before and after versions of the access plan graph for a query.

• Obtain information about each operation in the access plan, including the total estimated cost and number of rows retrieved.

Analyzing a Simple Dynamic SQL Statement

This section provides a simple example of how to get started analyzing a dynamic SQL query.

1. From the Control Center, click mouse button 2 on the SAMPLE database, and select Explain SQL from the pop-up menu. The Explain SQL Statement window opens.

2. In the SQL text field, enter the following SQL statement:

   select * from staff order by name
   

3. Click OK. The Access Plan Graph window opens. The graph represents the path that the optimizer chose as the most efficient in order to provide the results for your query.

4. Optional: Double-click any of the nodes (for example, the RETURN operator node). The Operator Details window opens, showing the details for that operator.

The explained SQL statement is saved automatically. To view it later:

1. From the Control Center, click mouse button 2 on the SAMPLE database, and select Show explained statements history from the pop-up menu. The Explained Statements History window opens.

2. Locate the entry you want. You can look at the SQL text column to see the SQL statement you had previously explained.

3. Click mouse button 2 on the entry, and select Show access plan from the pop-up menu. The Access Plan Graph window opens.

The online help for Visual Explain (accessible from the Help menu) provides details on how to interpret the Access Plan Graph window in order to improve the performance of SQL statements. The online help also contains detailed examples to help you learn how to use Visual Explain.

[ DB2 List of Books | Search the DB2 Books ]