Erfahrungen mit REOPT(ALWAYS) bzw (VARS)

Hallo Peter,

ganz ohne Grund würde ich die Option nicht nutzen, denn bei jedem SQL Statement und jeder Änderung von Host Variablen wird der Zugriffspfad neu bestimmt – das kann nicht ohne Folgen bleiben.

Die IBM-Literatur sagt ja …

Specifying the REOPT(ALWAYS) bind option:

You can specify a bind option when you want DB2 to determine access paths at both bind time and run time for statements that contain host variables and special registers.

At run time, DB2 uses the values in these variables to determine the acces paths. If the statement runs multiple times, DB2 determines the access path each time that the statement runs.

Consider using the REOPT(ALWAYS) bind option in the following circumstances:

• The SQL statement does not perform well with the access path that is chosen at bind time.
• The SQL statement takes a relatively long time to execute. For long-running SQL statements, the performance gain from the better access path that is chosen based on the input variable values for each run can be greater than the performance cost of reoptimizing the access path each time that the statement runs.

Die Einsatzbedingungen sind also zu durchleuchten:

• Ändern sich die Bedingungen/Objekte während der Programmausführung?
• Handelt es sich um wenige, aber komplexe Statements? Oder werden sehr viele, parameterisierte Statements ausgeführt?
• Ist der Erfolg von REOPT(ALWAYS) exemplarisch schon mal gemessen/bestätigt/getestet worden?
• …

To use the REOPT(ALWAYS) bind option most efficiently: first determine which SQL statements in your applications perform poorly with the REOPT(NONE) bind option and the REOPT(ONCE) bind option. Separate the code containing those statements into units that you bind into packages with the REOPT(ALWAYS) option. Bind the rest of the code into packages using the REOPT(NONE) bind option or the REOPT(ONCE) bind option, as appropriate.

   1. Determine which SQL statements in your applications perform poorly with the REOPT(NONE) bind option and the REOPT(ONCE) bind option.
   2. Separate the code containing those statements into units that you bind into packages with the REOPT(ALWAYS) option.
   3. Bind the rest of the code into packages using the REOPT(NONE) bind option or the REOPT(ONCE) bind option, as appropriate.
   4. Then bind the plan with the REOPT(NONE) bind option.

Statements in the packages bound with REOPT(ALWAYS) are candidates for repeated reoptimization at run time.

Viele Grüße
Gernot