Handling access-plan changes using InfoSphere Optim Query Workload Tuner on DB2 for z/OS

For a large number of packages

Access-plan changes can happen at any point during the database-application or data-server maintenance lifecycle. It is important for DBAs to handle these changes quickly and effectively. However, most DBAs lack tools to deal with access-plan changes in large numbers of packages in enterprise-scale applications. In this article, we describe methods of managing query access plans in such situations. We also introduce a tool provided in IBM® InfoSphere® Optim™ Query Workload Tuner 3.1 to implement these methods.

Share:

Kendrick Ren (kren@ca.ibm.com), Senior Software Engineer, IBM

Kendrick Ren photoKendrick Ren is technical lead for the IBM Optim Query Tuner and Optim Query Workload Tuner products, working out of the IBM Toronto lab. He has worked with these products in their previous incarnations as the DB2 optimization expert and Optimization Service Center products since the team was founded in 2004. He works closely with customers and business partners using these products, assisting them in the area of query optimization. Previously, he worked on the IBM WebSphere Commerce Server product.



Yuan Gao (gaoybj@cn.ibm.com), Staff Software Engineer, IBM

Yuan GaoYuan Gao is a software developer for the IBM Optim Query Tuner and Optim Query Workload Tuner products, working in the IBM China development lab since 2008. He is an IBM Certified Database Administrator and developer for DB2 for Linux, UNIX, and Windows; and an IBM Certified Solution Developer for XML. He has rich experience in SQL performance tuning and Eclipse RCP development.



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

Robert HeathRobert Heath has worked at IBM for 12 years and is currently an information developer for InfoSphere Optim Query Workload Tuner. He proclaims to be living proof that degrees in English and French literature do not necessarily impede one's career in software development. Perhaps Samuel Johnson put it best when he said, — uh, well, perhaps this isn't the right place for a quotation from an 18th-century essayist, poet, and lexicographer. Besides, this bio is already getting a bit long, don't you think? Right, then. Done.



Cliff Leung (cleung@us.ibm.com), Data Studio Software Architect, IBM  

Cliff LeungCliff Leung is a senior technical staff member in the Information Management organization at the Silicon Valley Laboratory in San Jose. He has been the chief architect of IBM Optim Query Tuner product since 2008 and is responsible for overall product direction and strategy. He has extensive experience in query compilation, optimization, and performance.



19 July 2012

Also available in Russian

Overview

It is generally inevitable for DBAs to rebind packages for applications that run on DB2® for z/OS® in the following situations:

  • DB2 for z/OS data-server upgrades or maintenance (applying an APAR related to SQL processing, for example)
  • Changes to database applications (such as changing SQL statements or fixing errors in application logic)
  • Changes to physical schemas (such as creating new indices for packages)
  • Changes to statistics (executing RUNSTATS, for example)

After a rebind operation, the DB2 optimizer might generate different access plans for packages, and it is possible for changed access plans to cause severe performance regression. As such, it is critical for DBAs to handle plan changes promptly and effectively. However, it is hard to know which queries or packages have changed access plans, what the changes are, and how the changes will affect performance in the production system, especially when the number of statements or packages is huge.

In this article, we introduce an effective method for handling access-plan changes. It can be summarized in three steps:

  1. Compare new access plans with old access plans.
  2. Categorize access-plan changes.
  3. Counteract the changes that might cause performance regression by taking one or a combination of the following actions:
    • Revert to old access plans, if necessary and applicable.
    • Tune queries that have problematic access plans.
    • Lock down query access plans.

InfoSphere Optim Query Workload Tuner 3.1 introduced a new tool: workload access plan comparison, which enables this method and aims to help DBAs handle access-plan changes easily and efficiently. Please note that this article explains the method for dealing with access-plan changes and uses screen captures from InfoSphere Optim Query Workload Tuner to illustrate the points. This article is not intended to provide how-to information for using InfoSphere Optim Query Workload Tuner. For more information about navigating to the various product features and to see a detailed introduction about how to launch the various functions in InfoSphere Optim Query Workload Tuner, see Resources.


Comparing new access plans with old access plans

Suppose you plan to rebind a massive number of packages after an APAR is applied on your DB2 for z/OS data server. In order to compare new access plans with the old ones, you should perform an incremental rebind (i.e., rebind one collection at a time). One advisable approach is to BIND the packages to a dummy collection ID first to check the new access plans before replacing the packages in the real production environment. Of course, it would be better to do validation on a test system first. For example, your original packages are bound under collection COLA, and you BIND the packages to a new collection, COLB. Then you can compare the access plans of the same packages under collections COLA and COLB.

Another possible approach is to BIND the packages with a new version ID, also called package versioning. For example, you can specify the new version ID, ver2, to distinguish from the old version ID, ver1, in the BIND operation. Then you can compare the access plans in ver1 against those in ver2 of the same packages.

The third approach is to REBIND the packages directly. You compare access plans of different bind times. However, since this will have effect on the runtime application, you should preserve the old access plans for later recovery, in case some regression is found. We discuss the details in "Revert to old access plans if necessary and applicable."

DB2 provides a utility called EXPLAIN, which can show detailed information about the access plan that the optimizer chooses for a SQL statement. EXPLAIN records are populated into the EXPLAIN tables after the EXPLAIN utility is successfully run, and these records can be used for access-plan analysis. When you compare access plans, you are really comparing EXPLAIN records. Therefore, when you bind or rebind packages, set the EXPLAIN option to YES in the BIND or REBIND statement to keep the EXPLAIN records of packages for further analysis. EXPLAIN records will be populated into the EXPLAIN tables that use the package owner as their schema.

When you compare access plans, you should pay attention to four items in particular:

  • Join sequences
  • Join methods
  • Table access methods
  • Sorts

However, it is not easy to compare EXPLAIN records for a large number of queries or packages manually. Suppose you have 1,000 packages, and there are an average of 10 statements in each package. If it took you 5 minutes to compare the access-plan changes for each statement manually, it would take you more than 34 days to go through all the packages. That's worse than looking for a needle in a haystack and certainly not the best use of your time.

Fortunately, InfoSphere Optim Query Workload Tuner 3.1 provides a tool that lets you compare access plans in packages and even in massive numbers of packages. This tool supports two types of comparisons:

  • You can compare access plans and estimated costs of statements in packages from two collection IDs.
  • You can compare access plans and estimated costs of statements in packages from same collection ID, but with different package versions or bind times.

It provides multiple options to specify which packages to compare. Comparisons are summarized by package. You can see an overview of all packages, then drill down to see the access plans and estimated cost changes of statements in each package. As you can see in Figure 1, five statements in the package are regressed, and two are improved (calculated by estimated cost, which is generated by the DB2 optimizer). You can read the count from the Regressed and Improved columns (indicated by ①) of the top table, which shows the summary of comparison of the packages.

Figure 1. Comparison result by package summary
Image shows comparison result by package summary

You can also see in the bottom table that the access plans of six statements are changed (indicated by ②). This table summarizes the comparison of the SQL statements of the package. You can select a statement and click on the first icon in the tool bar (indicated by ③) to drill down to see the detailed access-plan comparison, as shown in Figure 2.

Figure 2. Comparison of two access plans for an SQL statement
Image shows comparison of two access plans for an SQL statement

As you can see, differences in join sequences, join methods, table access methods, and PLAN_TABLE records are shown from top to bottom. If there is more than one query block, you can easily switch from one to the other.

In our example, there is only one query block in the access plan. Both the join sequences and join methods have changed, as shown in the close-up view in Figure 3:

  • Old access plan— The DB2 optimizer joins table LINEITEM with table ORDER with a nested loop join, joins table CUSTOMER with a hybrid join, then joins table NATION with a nested loop join.
  • New access plan— The DB2 optimizer joins table ORDER with table CUSTOMER with a nested loop join, joins table NATION with a nested loop join, then joins table LINEITEM with a sort merge join.
Figure 3. Comparison of join sequences for an SQL statement
Image shows comparison of join sequences for an SQL statement

The access method of table ORDER has also changed, shown in the close-up view in Figure 4:

  • Old access plan— The number of matching columns in the index scan for table ORDER is 2. The table is sorted and not accessed with a pre-fetch.
  • New access plan— The number of matching columns in the index scan for table ORDER is 0, which indicates an index screening. The table is accessed via a sequential pre-fetch.
Figure 4. Comparison of methods for accessing a table
Image shows comparison of methods for accessing a table

You can also generate an HTML report of the comparison, so you can share it with your team or print them for offline reading.


Categorize access-plan changes

After you compare the access plans of the statements in your packages and identify changes, the next step is to categorize the changes to evaluate the performance effect. Generally, access-plan changes can be categorized into two groups: changes that require analysis and changes that may be ignored.

Changes that require analysis might lead to performance regression. Table 1 provides a sample checklist to use when determining whether to scrutinize an access-plan change.

Table 1. Checklist of changes that require analysis
No.Change
1 An index scan (IXSCAN) changed to a table scan (TBSCAN).
2 A matching index scan changed to index screening.
3 The number of matching columns in the index scan of the new access plan has decreased compared to the old access plan.
4 A join method changed.
5 Hash access is used in the old access plan but not in the new access plan.
6 Parallelism is used in the old access plan but not in the new access plan.

Changes that may be ignored have a negligible effect on performance, and the effect might even be for the better.

However, it is time-consuming to check each access-plan change manually. It is also hard to select only certain types of access-plan changes for further analysis. Fortunately, InfoSphere Optim Query Workload Tuner can help. The workload access-plan comparison tool uses the checklist in Table 1 to annotate access-plan changes in the comparison result. You can also filter comparisons to see only specific types of changes. Figure 5 shows parts of the flexible filter criteria combination on access-plan changes. Additional customizable options are provided to filter the comparison result by access plan characteristics.

Figure 5. Post-comparison filter
Image shows post-comparison filter

Revert to old access plans if necessary and applicable

After you find the access-plan changes that require attention, it is critical to correct them quickly and effectively, especially if they are slowing down applications on production systems. Before you start fixing the problems, you can return the performance of an application to its previous level by reverting to the old access plans, if you preserved them. To preserve access plans, you use the DB2 access-path stability feature, introduced in DB2 9 for z/OS with APAR PK52522 and PK52523 and enhanced in DB2 10 for z/OS.

You can enable access-path stability for all packages on a subsystem or for individual packages. Use DSNZPARM PLANMGMT in macro DSN6SPRM to control this when rebinding all packages on a subsystem. Use the PLANMGMT option of the REBIND statement to control this for individual packages you rebind.

For example, suppose you have the package COLA.PACKAGE1, and you plan to rebind it and preserve its access plan. You can issue the following command: REBIND PACKAGE (COLA.PACKAGE1) PLANMGMT (BASIC).

DB2 generates new access plans for the package and preserves old access plans by copying them into the access-path repository. If you compare the rebound package with the previous package version and find performance regression, you can issue the following command to revert the package's access plans to the old ones: REBIND PACKAGE (COLA.PACKAGE1) SWITCH (PREVIOUS).

After this command is executed, the previous access plans replace the current ones, and the current access plans are placed into the access-path repository.

When you use InfoSphere Optim Query Workload Tuner, you do not have to issue this last command manually. You can use this dialog to select all the packages you want to restore access plans for, choose from two SWITCH options, and rebind those packages.

Figure 6. Manage access-plan dialog
Image shows managing access-plan dialog

Tune queries that have problematic access plans

If you cannot revert to previous plans, you can tune query access plans to make the application run better. The DB2 optimizer might fail to generate optimal access plans for various reasons, such as lack of statistics for database objects, poor index design, or bad query predicate writing. So the typical tuning actions would be collecting the proper statistics, revising query text, and evaluating and optimizing index usage. You would need to tune the SQL statements together as a query workload to improve the performance of the application as a whole.

Optim Query Workload Tuner integrates the workload access-plan comparison tool with a set of advisers and tools to tune the query workloads with. Figure 7 shows the dialog you use to generate a new query workload from the results of a comparison. In this dialog, you can apply a post-comparison filter, which lets you select a subset of SQL statements from the comparison result and save them as a new query workload.

Figure 7. Generate new query workload dialog
Image shows generating new query workload dialog

After you create a new query workload, you can use workload advisers to tune the queries that have problematic access plans. Figure 8 shows recommendations from the Workload Statistics Advisor. The recommendations are in the form of RUNSTATS control statements that will collect statistics found missing, incorrect, or outdated. These statements are listed in the area bordered by the square in this screenshot. The tables that the RUNSTATS statements will collect statistics for are listed above this area.

Figure 8. Workload Statistics Advisor recommendations
Image shows Workload Statistics Advisor recommendations

After you run the recommended RUNSTATS control statements, the DB2 optimizer can evaluate more accurately the access-plan cost for statements in the query workload. You can compare the more-accurate access plans with the previous access plans by using the workload access plan comparison tool again.


Lock down access plans

While many queries have access plans that can be optimized by tuning, you might have some with access plans that the DB2 optimizer stubbornly refuses to optimize. In such cases, you can use plan hints, which allow you to lock down access plans that meet your expectations. When you bind or rebind packages, DB2 will apply the plan hints you defined when generating query access plans.

However, it is too complicated to understand and figure out which table records should be used when defining a plan hint. Also, it is error-prone to create, validate, and deploy a plan hint against table records manually. InfoSphere Optim Query Workload provides a GUI tool — Visual Plan Hint — to make the process smooth and integrates it with the access-plan comparison functionality. You can choose to customize the old or new access plan of a query as a hint and deploy it, as is shown in Figure 9.

Figure 9. Create a plan hint
Image shows creating a plan hint

Figure 10 shows that the old plan is captured by the workload access-plan comparison tool. You can easily edit and validate the plan hint. Then you can specify parameters for hint deployment and deploy the hint to database.

Figure 10. Edit and deploy plan hint
Image shows editing and deploying plan hint

Conclusion

This article describes a method for effectively handling changes to access plans for queries in large packages. This method involves three steps: comparing new access plans with old ones, categorizing access-plan changes, and counteracting the changes that might cause performance regression. This article also introduces the workload access-plan comparison tool in InfoSphere Optim Query Workload Tuner. With this method and this tool, you can save time and effort fixing access plans that regress because of changes to the environments that your applications are running in.

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=825866
ArticleTitle=Handling access-plan changes using InfoSphere Optim Query Workload Tuner on DB2 for z/OS
publish-date=07192012