Performance Tip: REOPT(ONCE) when using DB2 Statement Concentrator
RickRhea 060000AVGB Visits (6266)
If the cost estimate does not correctly reflect the execution cost in real time, it is likely that the access plan picked by the query optimizer might not be optimal. With the literals specified in the query, it would be easier for the query optimizer to have a more accurate cost estimate. On the other hand, it would be more difficult to pick the right plan with parameter markers, especially if the data is not uniformly distributed, since the query optimizer would have to use a generic rule to estimate an average cost because the values are not known. Typically, the normal distribution of the data is assumed.
For IBM Tivoli service management products, if the statement concentrator is enabled, all queries will be translated into queries with parameter markers. To help DB2 query optimizer select a more efficient access plan, specify the bind option REOPT(ONCE) when running the queries. For REOPT(ONCE), query optimization occurs when the query is executed for the first time and the access plan will be reused for subsequent query execution. Through our benchmark measurements, we found that this bind option will help query performance with DB2 in IBM Tivoli service management products.