Previous Topic: Proper StatisticsNext Topic: OPTIMIZE FOR Clause

Predicates and SQL Tuning › Influencing the Optimizer › Run-time Reoptimization

Run-time Reoptimization

If your query contains a predicate with an embedded literal value, DB2 knows about the input to the query. DB2 can take advantage of frequency distribution or histogram statistics, if available. The result is a much improved filter factor and better access path decisions by the optimizer. However, DB2 may not know about your input value:

SELECT *
FROM    EMP
WHERE MIDINIT > :H1

In this case, if the values for MIDINT are highly skewed, DB2 could make an inaccurate estimate of the filter factor for some input values.

DB2 can employ the run-time reoptimization to help your queries. For static SQL, the option of REOPT(ALWAYS) is available. This bind option instructs DB2 to recalculate access paths at runtime using the host variable parameters. This practice can improve execution time for large queries. However, if many queries are in the package, they are all reoptimized. This practice can affect the statement execution time for these queries. When you use REOPT(ALWAYS), consider separating the query that can benefit in its own package.

Dynamic SQL statements have three options:

REOPT(ALWAYS)

Reoptimizes a dynamic statement with parameter markers based upon the values that are provided on every execution.

REOPT(ONCE)

Reoptimizes a dynamic statement the first time it is executed based on the values that are provided for parameter markers. The access path is reused until the statement is removed from the dynamic statement cache and needs to be prepared again. Use this reoptimization option with care because the first execution should have good representative values.

REOPT(AUTO)

Tracks how the values for the parameter markers change on every execution and reoptimizes the query that is based upon those values if it determines that the values have changed significantly.

A system parameter called REOPTEXT (DB2 9) enables REOPT(AUTO)-like behavior, subsystem wide, for any dynamic SQL queries (without NONE, ALWAYS, or ONCE already specified) that contain parameter markers when changes are detected in the values that could influence the access path.