You can analyze the results of the access plan comparison to identify performance and cost changes to the SQL statements.
Before you begin
Compare access plans for SQL statements from packages or compare access plans of different EXPLAIN snapshots of a workload.
Procedure
- In the Manage and Tune page of the workflow assistant, select a workload that contains the access plan comparison results.
- To review the previous comparison sessions for the workload, click the View Comparison Result icon.
- In the Comparison History page, select a comparison session from the table. You can drill down to inspect comparison statistics at increasingly finer levels of granularity.
Package summary or workload
At this level, you can inspect these key statistics:
- Cost
- Overall estimated cost of the SQL statements in the source and target.
- Statements
- The total number of statements in the source and target.
- Regressed
- The number of SQL statements in the target that have a higher estimated cost compared to the source.
- Improved
- The number of SQL statements in the target that have a lower estimated cost compared to the source.
- Added
- The number of additional SQL statements in the target compared to the source.
- Removed
- The number of fewer SQL statements in the target compared to the source.
SQL statements
At this level, you can inspect these key statistics:
- Cost Increased
- "Yes" indicates that the SQL statement in the target package has a higher overall estimated cost compared to the source package.
- Plan Changed
- "Yes" indicates that the statement access plan in the target is different compared to that in the source.
- Cost Increase %
- The percentage calculated using the formula: (estimated cost in target - estimated cost in source ) / estimated cost in source.
- Source or Target Cost
- Overall estimated cost of the SQL statement in the source or target (in milliseconds).
- Statement Changed?
- "Added" indicates that SQL statements in the target were added to the source, "Removed" indicates that SQL statements in the source were removed from the target, and "No" indicates that SQL statements were unchanged.
Access plan for the SQL statement
At this level, you can inspect these key statistics:
- Requires Attention?
- Indicates if this statement requires additional attention because of access plan changes.
- Summary of differences in the PLAN_TABLE records
- For a description of the PLAN_TABLE columns, refer to this topic.
What to do next
If the performance of any SQL statements regressed, you can take one of these actions.
- Create a query workload with the regressed statements. You can then tune them collectively or individually. Then, rebind the packages.
- Create a plan hint and rebind the packages to apply the hint.
- If you are using DB2 for z/OS Version 9 or higher, you can revert to a previously stored access plan.