Performance Tip: REOPT(ONCE) when using DB2 Statement Concentrator

Body

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.