Performance Tip: REOPT(ONCE) when using DB2 Statement Concentrator
RickRhea 060000AVGB Visits (6607)
For DB2 on Linux, UNIX and Windows environments, the query performance is related to the input variable values (or literals) that are passed to the database. When a query is issued by a user or an application, the database engine will parse the query and transform the query into an internal format that the DB2 engine understands. With the internal query representation, the DB2 query optimizer will try to estimate the execution cost of the possible access plans. Based on the statistics collected in the database, the cost of the query execution will be estimated, and one of the access plans will be selected based on the cost estimate.
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.