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:
- Compare new access plans with old access plans.
- Categorize access-plan changes.
- 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
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
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
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
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
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
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
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
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
|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
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
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
REBIND PACKAGE (COLA.PACKAGE1) SWITCH
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
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
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
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
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
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.
- InfoSphere Optim Query Workload Tuner information center provides how-to information for using InfoSphere Optim Query Workload Tuner.
- Read "Tuning SQL with Optim Query Tuner, Part 1" get learn how to tune SQL statements.
- In the DB2 for z/OS information center, get the materials on EXPLAIN tables, access plan management and plan hint.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
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.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.