Reoptimizing SQL statements at run time

You can specify whether DB2® uses literal values for host variables, parameter markers, and special registers to reoptimize SQL statements at run time.

Procedure

To manage whether DB2 re-optimizes SQL statements at run time:

  1. Identify the statements that run most efficiently when DB2 follows the rules of each REOPT option.
    Option Description
    REOPT(ALWAYS) DB2 always uses literal values that are provided for parameter markers, and special registers to re-optimize the access path for any SQL statement at every execution of the statement.

    The REOPT(ALWAYS) bind option ensures the best access path for a statement. However, it can increase the cost for frequently used dynamic SQL statements.

    Use the REOPT(ALWAYS) bind option in the following circumstances:

    • The statement does not run with acceptable performance with the access path that is chosen at bind time.
    • The statement takes a relatively long time to run. For long-running statements, the performance gain from the reoptimized access can outweigh the cost of reoptimizing the access path each time that the statement runs.
    • Start of changeCONCENTRATE STATEMENTS WITH LITERALS was specified when the statement was prepared but you want DB2 to consider the literal values for access path selection.End of change
    • Start of changeThe dynamic statement is unlikely to result in the dynamic statement cache hits. In such cases, you might prefer to save the space in the cache for transactions that are more likely to benefit from reuse. The REOPT(ALWAYS) bind option prevents the placement of statements in the dynamic statement cache.End of change
    • The dynamic statement cache is not enabled.

    You can issue the following statements to identify statements that are reoptimized under the REOPT(ALWAYS) bind option:

    SELECT PLNAME,
  CASE WHEN STMTNOI <> 0
   THEN STMTNOI
   ELSE STMTNO
  END AS STMTNUM,
  SEQNO, TEXT
  FROM   SYSIBM.SYSSTMT
  WHERE STATUS IN ('B','F','G','J')
  ORDER BY PLNAME, STMTNUM, SEQNO;
    SELECT COLLID, NAME, VERSION,
  CASE WHEN STMTNOI <> 0
   THEN STMTNOI
   ELSE STMTNO
  END AS STMTNUM,
  SEQNO, STMT
  FROM SYSIBM.SYSPACKSTMT
  WHERE STATUS IN ('B','F','G','J')
  ORDER BY COLLID, NAME, VERSION, STMTNUM, SEQNO;
    REOPT(AUTO) DB2 uses literal values that are provided for parameter markers, host variables, and special registers to determine at execution time whether to re-optimize access paths for cached dynamic statements.

    REOPT(AUTO) does not apply for static SQL statements.

    Use the REOPT(AUTO) bind option to achieve a better balance between the costs of reoptimization and the costs of processing a statement. You might use the REOPT(AUTO) bind option for many statements for which you might also choose either the REOPT(ALWAYS) or REOPT(NONE) bind options. Use REOPT(AUTO) especially in the following situations:

    • The statement is dynamic and can be cached. If dynamic statement caching is not turned on when DB2 runs a statement under the REOPT(AUTO) bind option, no reoptimization occurs.
    • The statement sometimes takes a relatively long time to run, depending on the values of referenced parameter markers, especially when parameter markers refer to columns that contain skewed values or that are used in range predicates. In such situations, the estimation of qualifying rows might change based on the literal values that are used at execution time.

      For such statements, the performance gain from a new access path that is chosen might or might not outweigh the cost of reoptimization at run time.

    • Start of changeCONCENTRATE STATEMENTS WITH LITERALS was specified when the SQL statement was prepared but you want DB2 to consider the literal values for access path selection.End of change
    REOPT(ONCE) DB2 uses literal values that are provided for parameter markers, and special registers to re-optimize cached dynamic SQL statements at run time for the first execution of the statement.

    The REOPT(ONCE) bind option determines the access path for an SQL statement only one time at run time and works only with dynamic SQL statements. The REOPT(ONCE) bind option allows DB2 to store the access path for dynamic SQL statements in the dynamic statement cache.

    REOPT(ONCE) does not apply for static SQL statements.

    Use the REOPT(ONCE) bind option in the following circumstances:

    • The SQL statement is a dynamic SQL statement.
    • The SQL statement does not run with acceptable performance with the access path that is chosen at bind time.
    • The SQL statement is relatively simple and takes a relatively short time to run. For simple statements, reoptimizing the access path each time that the statement runs can degrade performance more than using the access path from the first run for each subsequent run.
    • The same SQL statement is repeated many times in a loop, or is run by many threads. Because of the dynamic statement cache, the access path that DB2 chooses for the first set of input variables performs well for subsequent executions of the same SQL statement, even if the input variable values are different each time.
    • Start of changeThe application issues ad hoc non-repeating SQL statements that might use special registers, or reference views, which use special registers, such as CURRENT DATE and CURRENT TIMESTAMP. For example, such applications include DSNTEP2, DSNTIAUL, DSNTEP4, DSNTIAD, SPUFI, and QMF™.End of change
    • Start of changeCONCENTRATE STATEMENTS WITH LITERALS was specified when the SQL statement was prepared but you want DB2 to consider the literal values for access path selection.End of change
    REOPT(NONE) You can specify that DB2 uses the access path that was selected at run time. Statements that run with acceptable performance under the REOPT(NONE) bind option might run with even better performance under the options that might change the access path at run time.

    Start of changeUnder the REOPT(NONE) bind option DB2 does not consider literal values for access path selection when CONCENTRATE STATEMENTS WITH LITERALS is specified when statements are prepared.End of change
  2. Use one, or a combination of, the following approaches to specify the appropriate type of reoptimization for each statement:
    • Start of changeCreate statement-level optimization parameters to specify the best reoptimization option for each statement.End of change
    • Separate the statements that run best under the different reoptimization options into different packages. You can then specify the appropriate bind option for each package.