c-treeACE SQL Option for Certain Slow Parameterized Queries
A set of parameterized SQL queries, with some literals, was found to run slowly. When optimizing a parameterized query, the c-treeACE SQL optimizer uses an average selectivity. If the specified literal value has a selectivity of a great enough magnitude away from the average, the generated plan may be less than optimal. A new configuration option allows a controlled selectivity cutoff:
SETENV DH_REBUILD_SEL_CUTOFF
This controls the regeneration of the plan for queries with parameter references, during execution, based on the value set: -1 < n <= 100.
If the value is set to -1, query plans are never regenerated during execution.
If the value is set to 0, query plans are always regenerated during execution.
If the value is set between 1 and 100, query plans will be regenerated whenever the percentage difference in selectivity (i.e., the selectivity that was used during the generation of the original plan and the selectivity for the actual parameter values passed during execution) exceeds this value. For example, if the value is set to 25, the plan will be regenerated only if the selectivity of the actual parameter is different from the original selectivity than 25%.
Example
SETENV DH_REBUILD_SEL_CUTOFF=25
Default: (-1) Query plans are never regenerated during execution.
