How to analyze access plan comparison results

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

  1. In the Manage and Tune page of the workflow assistant, select a workload that contains the access plan comparison results.
  2. To review the previous comparison sessions for the workload, click the View Comparison Result icon.
  3. 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.

Feedback