Using section explain in InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows 3.2

Take advantage of the section explain feature in IBM® DB2® for Linux®, UNIX®, and Windows® 9.7.1 and later when you tune SQL with InfoSphere® Optim™/reg> Query Workload Tuner 3.2.

Robert Heath (heathr@us.ibm.com), Information Developer, IBM

Robert HeathRobert Heath is the information developer for InfoSphere Optim Query Workload Tuner. He has been with IBM for nearly 13 years and has developed documentation and user assistance for many applications and features.



24 January 2013

Overview

DB2 for Linux, UNIX, and Windows 9.7 Fix Pack 1 introduced the section explain functionality, an addition to the DB2 explain facility. A section explain, which can be done by running one of four procedures, populates EXPLAIN tables with the contents of a statement's runtime section.

When an explain of a statement is done on a runtime section, you get the actual access plan that was used or will be used in the execution of a statement. For each operation, you also get the cumulative total cost and the cumulative first row cost. (A full accounting of the differences in the output of a section explain and the EXPLAIN statement is available.) And although you lose a number of the statistics that come with a typical explain, what you end up with is a great performance baseline against which to measure your efforts at resolving the performance slowdown that the statement caused. Moreover, if you are using an activity event monitor, you can also get the actual cardinalities for the operations in the access plan that a section explain describes. (The appendix to this article shows three ways that you can collect the actual cardinalities with an activity event monitor.)

InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows, 3.2 (IOQWT) exploits the section explain functionality in several ways:

  • When you are tuning a single SQL statement:
    • You can view an access plan graph (APG) from a section explain immediately after you capture a statement from an activity event monitor or a set of EXPLAIN tables that contains section explain data.
    • You can use a section explain and section actuals to establish a performance baseline against which you can compare the actual access plan of a statement and the actual cardinalities of the plan's operators after you have tuned the statement. IOQWT can gather the section explain information and the section actuals when you re-explain the statement with a certain option selected. IOQWT creates a temporary activity event monitor and runs the statement.
  • After tuning a query workload of statements captured from an activity event monitor, EXPLAIN tables with section explain data, packages, or the package cache, you can use a section explain as a performance baseline against which you can compare estimated performance improvements.

Viewing an APG that uses section explain from the list of captured statements

After you capture statements from an activity event monitor or a set of EXPLAIN tables that are populated with section explain data, you can immediately generate an access plan graph to see the access plan for one of those statements. Unlike in earlier releases of IOQWT, you don't have to click a button to go to the Run Single-Query Tools and Advisors page of the Invoke section, specify values for special registers, choose to generate an access plan graph, and explain the statement.

Figure 1. Overview of the steps for showing an access plan graph from a list of captured statements
Image shows overview of steps for showing an access plan graph from list of captured statements

Steps

  1. Capture statements from an activity event monitor or EXPLAIN tables that contain section explain data.
  2. Optionally, sort the statements to find a statement that needs tuning.
  3. Right-click the statement and select the option for viewing an access plan graph.

The access plan graph is displayed in a separate window.

Figure 2. An APG from a section explain of a captured statement
Image shows APG from a section explain of a captured statement

If you captured the statement from an activity event monitor that collected actual cardinalities, these values appear both in the section Description of Selected Node and in the tool tip that appears when you move your mouse cursor over a node.

Figure 3. Where actual cardinalities appear for a statement captured with section actuals
Image shows where actual cardinalities appear for a statement captured with section actuals

You can keep this window open to refer to as you tune the statement. You can also have multiple windows open to see the access plans for more than one statement at a time.


Comparing access plan graphs that use section explain and actuals

Though it is convenient to generate an access plan graph immediately after capturing, you can't compare it to another access plan graph after you've done some tuning. IOQWT can take two access plan graphs and highlight the differences among them, so you can see the benefits of the work you've done to tune a statement.

To generate an access plan graph you can use in such comparisons, you go as usual to the Run Single-Query Advisors and Analysis Tools page. However, there are a few things that you will do differently from how you otherwise might.

Figure 4. Comparing access plan graphs from section explain with access plan graphs that show EXPLAIN values
Image shows overview of the steps for comparing access plan graphs from section explain with access plan graphs that show EXPLAIN values

Steps

  1. Capture statements from any data source IOQWT supports for DB2 for Linux, UNIX, and Windows.
  2. Optionally, sort the statements to find a statement that needs tuning.
  3. Select the statement and click Invoke Advisors and Tools to push the statement to the Run Single-Query Advisors and Analysis Tools page.
  4. If there are unqualified objects in the SQL statement, specify the qualifier to use in the Schema field. The default schema is your user ID.
  5. Keep the option Re-explain the query selected.
  6. Select the option Collect actual execution values to use in access plan tools, if the statement is a SELECT statement. Selecting this option creates a temporary activity event monitor and runs the statement when you click OK in step 7, substep 3. Your user ID must have the privilege to run the CREATE EVENT MONITOR statement.

    The length of time it takes to generate the access plan graph depends on how long it takes for the activity event monitor to be created and the statement to run. This option causes IOQWT to get the actual access plans and actual cardinalities for the statement when the statement is run.

  7. Generate an access plan graph.
    1. Click Select What to Run.
    2. Select the option Display access plan graph, if it is not already selected. You can also choose to view the section actuals in a formatted and annotated version of the statement and in the Access Plan Explorer. However, this article doesn't cover these tools.
      Figure 5. An SQL statement formatted and annotated
      Image shows SQL statement formatted and annotated
      Figure 6. Access Plan Explorer showing the access plan of an SQL statement
      Image shows Access Plan Explorer showing the access plan of an SQL statement

      To use these additional tools, select the options Format and annotate SQL statement and Show access plan in Access Plan Explorer.

    3. Click OK.

    After the access plan graph is generated, it opens. Notice the section Analysis Results in the top-left corner of the workflow assistant. The SQL statement, named Query 1, now has an analysis result listed under it, called Analysis Result 1. An analysis result is the collection of outputs from the advisors and tools you ran at one time. The access plan graph you generated is part of Analysis Result 1, as are the formatted version of the statement and the information in the Access Plan Explorer, if you chose to also run those tools.

    Figure 7. The left side of the workflow assistant after the access plan graph is opened
    Image shows left side of the workflow assistant after access plan graph is opened

    Remember the name of the analysis results that contain access plan graphs you want to use for baselines in comparisons with later access plan graphs. When you select APGs to compare, you select the analysis results they are located in.

  8. Click the Invoke tab on the left side of the workflow assistant to return to the Run Single-Query Tools and Advisors page.
  9. If you captured the statement from any data source other than an activity event monitor, deselect the options Re-explain the query and Collect actual execution values to use in access plan tools, if the statement is a SELECT statement. If you captured from an activity event monitor that collected actual cardinalities, a bug in IOQWT loses them when the statement is opened in the Invoke section. By selecting these options, you can recollect the actual cardinalities. Be aware, however, that the cardinalities might differ from what they were when you captured the statement. They might differ if, after you captured the statement, changes were made to the database, changes that could affect the access plan of the statement.
  10. Run advisors and implement their recommendations. To run one or more advisors at a time, click Select What to Run and select them in the dialog that is opened.

    Tip: The recommended practice is to run one of the advisors at a time and implement its recommendations, starting with the Statistics Advisor. Because the recommendations modify the database (for example, by changing statistics or by adding indices), before running the next advisor, ensure that the options Re-explain the query and Collect actual execution values to use in access plan tools, if the statement is a SELECT statement. The advisors depend on having current information in the EXPLAIN tables. Moreover, by running the statement and collecting the actual access plan and cardinalities, you are giving the advisors a better understanding of the access plans than if you were to re-explain the statement and not collect actuals.

  11. Ensure again that the options Re-explain the query and Collect actual execution values to use in access plan tools, if the statement is a SELECT statement. Then, generate another access plan graph. Now that you've tuned the statement, you want to run the statement again to get its actual access plan and cardinalities.
  12. Compare the first access plan graph with the second one.
    1. Click the Compare tab on the left side of the workflow assistant.
    2. Select Access Plan Graph Comparisons in the top-left corner.
    3. Select Analysis Result 1 for the left side of the comparison.
    4. For the right side of the comparison, select the last analysis result for the same statement.
    5. Click Compare. Both APGs appear side by side, and the differences in the structures of the APGs are highlighted. Differences in cardinalities are not highlighted and the comparison tool does not count them as differences. However, when you mouse over a node, the tool tip that appears displays the actual cardinality for that node.
      Figure 8. A comparison of two access plan graphs
      Image shows a comparison of two access plan graphs

Using section explain when tuning query workloads

The main point of capturing section explain information is to use it as a baseline against which you can measure the estimated benefits of implementing advisor recommendations. I've shown how to compare access plan graphs for single SQL statements and see the differences. If you are tuning a query workload, you can use a much more robust set of features for comparing all the access plans from section explain to estimated access plans after you've implemented advisor recommendations.

Figure 9. Overview of the steps for comparing access plans
Image shows overview of the steps for comparing access plans based on section explain information with access plans that use EXPLAIN values from after recommendations are implemented

Steps

  1. Capture statements from an activity event monitor, EXPLAIN tables, packages, or the package cache.
  2. Save the statements to a query workload, making sure to save the section explain information with the statements. In the Input Workload Name and Description dialog, select Save the last recorded EXPLAIN information for the captured statements.

    When you save into a query workload the SQL statements you have captured from an activity event monitor, IOQWT searches for statements that have the same value for in the EXECUTABLE_ID column of the ACTIVITYSTMT table. IOQWT captures only one copy of the statement.

    Tip: After the query workload is created, double-click it to see the list of statements in it. The first statement might be the SET EVENT MONITOR statement that turned on the event monitor. For example, for this article, the statement is SET EVENT MONITOR ACTEVMON STATE 1. You don't want to tune this statement, of course. You can right-click it and select Remove. You can avoid the need to do this if you set the ACTIVITY_TYPE criterion in the filter you use to capture statements. For example, to capture only DML statements that read data, set ACTIVITY_TYPE to READ_DML. However, if you set this criterion to OTHER, the SET EVENT MONITOR statement will still appear.

  3. After capturing statements and saving them to a query workload (as described in the section above), run advisors and implement recommendations. The recommended practice is to run one of the advisors at a time, and implement its recommendations, starting with the Workload Statistics Advisor. Because the recommendations modify the database (for example, by changing statistics or by adding indices), before running the next advisor, ensure that the option Re-collect EXPLAIN information before running workload advisors is selected on the Run Workload Advisors page. The advisors depend on having current information in the EXPLAIN tables.
  4. Explain the query workload. Now that you have implemented recommendations, collect EXPLAIN information one last time. You'll compare this information with the section explain information.
    1. Click the Manage tab on the left side of the workflow assistant.
    2. Select Workload List in the top-left corner.
    3. Select the query workload and select Explain Selected Workload in the More Actions field.
    4. Compare the section explain information with the EXPLAIN snapshot.
      Terminology: In IOQWT, an EXPLAIN snapshot is simply EXPLAIN information gathered at a point in time.
    5. Click the Compare button and choose to compare two EXPLAIN snapshots of the same query workload.
    6. Select the two EXPLAIN snapshots and click OK. The section explain information is in the EXPLAIN snapshot of the type Workload Capture, which means the EXPLAIN information was captured together with the statements. The EXPLAIN snapshot you just created is the one of the type EXPLAIN Workload.

      Tip: Note either the Start timestamps or the Stop timestamps. When you view the results of the comparison, the earlier snapshot is called the Source, and the later snapshot is called the Target. Knowing which snapshot was taken first will tell you which of them is the Source and which is the Target.

      Figure 10. Dialog for choosing EXPLAIN snapshots to compare
      Image shows dialog for choosing EXPLAIN snapshots to compare
    7. Click OK when IOQWT asks whether to display the results of the comparison.
    8. Double-click the entry for the new comparison result to open this page.

As mentioned, the EXPLAIN snapshots are identified as Source and Target, and their timestamps are displayed.

Figure 11. Results of comparing the section actuals for a query workload with EXPLAIN information collected
Image shows results of comparing the section actuals for a query workload with EXPLAIN information collected after implementing advisor recommendations

In the Statements section on this page, you can see whether the performance for each statement improved. In this screenshot, the green numbers in the Cost Increase% column show that the performance of all of the statements improved, and the improvement was very large for four of the five statements.

When you view the results of a comparison, understand that IOQWT uses three criteria for comparing access plans for SQL statements that run on DB2 for Linux, UNIX, and Windows:

  • Did the estimated cost of running the statement change?
  • If the original access plan includes one or more joins, are the joins the same in the corresponding access plan?
  • Did the methods of accessing tables change?

So, when IOQWT shows an N in the first three columns of the results of the comparison, other aspects of the access plan might have changed.

To see details about the differences in the access plans for a statement, select that statement and click View Detail. You'll see two access plan graphs side by side, though they don't show cardinalities or other statistics. Instead, they show only the operations and joins in the access plans. Below the graphs are expandable lists of the differences.


Summary

IOQWT 3.2 lets you capture the actual access plans for SQL statements and, if you are capturing from an activity event monitor collecting section actuals, the actual cardinalities for the operations in those access plans. You can view the actual access plans and cardinalities directly in the Capture section without having first to go to the Invoke section, run the APG tool, then go to the Review section.

To use an actual access plan and actual cardinalities in a performance baseline, you re-explain a statement, specifying also to collect the actual plan and cardinalities. IOQWT creates a temporary activity event monitor set to collect section actuals, does a section explain, and collects the section explain information and the section actuals.

To use actual access plans in a performance baseline for a query workload, you capture statements from a source that contains section explain information. When you save the statements to a query workload, you specify to save the most recent explain information. The resulting EXPLAIN snapshot is identified by the type Workload Capture in the dialog where you choose EXPLAIN snapshots to compare. And remember that in IOQWT an EXPLAIN snapshot is simply explain information generated at a point in time.


Appendix

Section actuals give you the actual cardinalities for each operator in the access plan of a statement monitored by an activity event monitor when the statement ran. The collection of section actuals is not turned on by default when you create an activity event monitor. There are three ways to turn it on, depending on the scope that you want the collection to apply to.

Collecting section actuals for a workload

A workload in DB2 for Linux, UNIX, and Windows is not the same as a query workload in IOQWT. These workloads identify incoming work based on its source, so the work can later be monitored or managed by DB2 workload management independently of all other work.

To collect section actuals for a workload, include the IN ACTUALS BASE phrase in the COLLECT ACTIVITY DATA statement in the definition of the workload, as in the following example.

Listing 1. Using the IN ACTUALS BASE phrase in a definition of a workload
CREATE WORKLOAD MYCOLLECTWL  
CURRENT CLIENT_ACCTNG('MYCOLLECTWL') 
CURRENT CLIENT_APPLNAME('MYAPP.EXE')
COLLECT ACTIVITY METRICS EXTENDED 
COLLECT ACTIVITY DATA ON ALL MEMBERS WITH DETAILS, SECTION IN ACTUALS BASE AND VALUES;

Collecting section actuals for all SQL statements run on a database connection

In this case, after you start an activity event monitor you run this statement, where the switch to turn on the collection of section actuals is the value BASE between the <collectsectionactuals> tags.

Listing 2. Using the BASE keyword in a call to WLM_SET_CONN_ENV
CALL WLM_SET_CONN_ENV(NULL, 
'<collectactdata>WITH DETAILS, SECTION AND VALUES</collectactdata>
<collectactpartition>ALL</collectactpartition>
<collectsectionactuals>BASE</collectsectionactuals>');

Collecting section actuals for all SQL statements that run on a database

As you might guess, in this case, you set a database configuration parameter.

Listing 3. Using the SECTION_ACTUALS database configuration parameter
UPDATE DB CFG SECTION_ACTUALS USING BASE

Acknowledgements

The author thanks Cliff Leung, Michelle Jou, Yang Rui, and Xin Wu for their help and reviews of this article.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=855863
ArticleTitle=Using section explain in InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows 3.2
publish-date=01242013