There is a way out of this situation. DB2 has a REOPT option which can be used with PREP, BIND or REBIND utilities.This option forces the query to be re-optimized at the time of execution so that the access plan can be generated with the real values of parameter markers, host variables or special registers.
The REOPT option has 3 possible values:
REOPT NONE (default)
The access path for the statement containing host variables, parameter markers, or special registers will not be optimized using real values. The access plan is cached and used subsequently.
REOPT ONCE
The access path for the statement will be optimized using the real values of the host variables, parameter markers, or special registers when the query is first executed. This plan is cached and used subsequently.
REOPT ALWAYS
The access path for the statement will always be compiled and reoptimized using the values of the host variables, parameter markers, or special registers known at each execution time.
It is generally recommended to use REOPT ONCE as it gives a better performance than REOPT ALWAYS. But, in certain cases for some queries REOPT ALWAYS may produce better results.
To see how the REOPT parameter affects your query access plan, you can set the CURRENT EXPLAIN MODE special register equal to REOPT and then run an query.
db2 SET CURRENT EXPLAIN MODE REOPT
db2 SET CURRENT EXPLAIN SNAPSHOT REOPT (if using the Explain snapshot facility)
If you want to set REOPT for a session:
db2 "CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL EXPLSNAP ALL REOPT 3')"
To set REOPT globally:
db2set 'DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL EXPLSNAP ALL REOPT 3" '
The REOPT option can also be set globally by creating an optimization profile.
| Comments |
|
Powered by !JoomlaComment 3.25
