Overriding the selectivity (filter factors) that the DB2 Optimizer estimates for predicates

Use the Override Selectivity feature to override filter factors that the DB2 optimizer estimates for predicates in SQL statements that run on DB2 for z/OS, Version 11 new-function mode or later.

Before you begin

About this task

The filter factor of a predicate is a number between 0 and 1 that estimates the proportion of rows in a table for which the predicate is true. For example, suppose that DB2 can determine that column C1 of table T contains only five distinct values: A, D, Q, W and X. In the absence of other information, DB2 estimates that one-fifth of the rows have the value D in column C1. Then the predicate C1='D' has the filter factor 0.2 for table T.

The DB2 optimizer is not always able to determine precise filter factors for a predicate or a set of predicates that are in an SQL statement, even in cases where the statistics for referenced tables are up-to-date. The result is that the optimizer generates access plans that are less than optimal.

For example, the predicate WHERE T1.C1 > ? does not allow the optimizer to estimate exact filter factor during the PREPARE or BIND phases; even if histogram statistics have been collected on C1, the optimizer cannot know what values are possible.

With the Override Selectivity feature, you can get recommendations for replacing the values that the optimizer estimates for filter factors with values that are more accurate. You can even set values of filter factors yourself.

A set of filter factors for an SQL statement constitute a selectivity profile. You can use the Override Selectivity feature to validate selectivity profiles and to deploy them if they are free of errors.

Restriction: Although DB2 for z/OS allows selectivity profiles to contain more than one set of filter factors, the Override Selectivity feature creates only one set per selectivity profile.

After you override filter factors, either by accepting recommendations or setting them yourself, you can check the set of filter factors for potential errors. Then, you can review the new access plan for the current SQL statement, and even compare it to the original access plan, without first deploying the new selectivity profile. You can also run the Index Advisor on the statement; the advisor will use the new access plan. You can then compare the advisor's recommendations to recommendations that it gave based on the original access plan.

If you want to modify the filter factors more, you can do so, re-validate them, re-test, and then deploy them.

Procedure

  1. After implementing the recommendations of the Statistics Advisor, return to the Run Single-Query Advisors and Tools page of the Invoke section in the workflow assistant. You can return there by clicking the Invoke tab on the left side of the workflow assistant. If the Run Single-Query Advisors and Tools page is not open after you click the tab, click Run Advisors and Analysis Tools on the left side of the workflow assistant to open the page.
  2. Select Override Selectivity on the left side of the workflow assistant.
  3. View the formatted SQL statement and either calculate or manually change filter factors that are marked as candidates for overriding.

    The SQL statement appears in a format that shows its structure clearly. If the Override Selectivity feature found any predicates that the DB2 optimizer might incorrectly estimate filter factors for, a Y appears next to the portions of the predicate for which there are filter factors.

    In the Proposed Filter Factor column appear any filter factors that the Override Selectivity feature recommends. If you want to specify your own filter factor, click in the row for the filter factor and specify a value from 0 to 1.

    Important: Before setting filter factors yourself, be sure that you have read about filter factors in the documentation for your version of DB2 for z/OS.
    After overriding filter factors, either by letting the Override Selectivity feature choose filter factors or setting filter factors yourself, validate the new selectivity profile. If there are no errors or warnings, you can take one or more of the following actions to decide whether or not to deploy the selectivity profile:
    • Review an access plan graph for the new access plan.
    • Review the new access plan in the Access Plan Explorer.
    • Compare the previous access plan graph for the statement with an access plan graph that uses the new filter factors.
    • Run the Index Advisor, which causes the selectivity profile to be deployed virtually. The advisor makes recommendations that are based on the selectivity profile. You can find out whether the selectivity profile causes the Index Advisor to modify recommendations that it previously gave for the statement.

    If you decide that the selectivity profile improves the access plan enough to meet your performance objectives, you can use the Override Selectivity feature to deploy the selectivity profile.


Feedback