Neoview Control Query Default (CQD) Reference Guide (R2.5)

Neoview Release 2.3Introduced in release
Not applicableDeprecated in release
RISK_PREMIUM_SERIAL
Influencing Query PlansCategory
Influences the optimizer to choose a parallel plan over a serial plan, by making a serial
plan relatively more expensive.
Description
Any positive fractional
value
The default value is ‘1.2’.
Values
Review ROBUST_QUERY_OPTIMIZATION first before considering the use of this CQD.
The default setting means that a serial plan must be 20% cheaper before it is allowed to
win over competing parallel plans. A setting of 1.0 means no handicap for serial plans. A
setting of 2.0 means a serial plan must be 100% cheaper before it is allowed to win over
competing parallel plans.
If it is determined that the optimizer is using serial plans often enough where these plans
are resulting in poor performance, this CQD may be used to influence the optimizer to
consider parallel plans instead in some of those cases.
Certainly, ATTEMPT_ESP_PARALLELISM MAXIMUM could turn serial plans off
completely. However, there are many cases where serial plans do provide better performance
than parallel plans, and turning them off completely may negatively impact the performance
of queries that can do a lot better with serial plans.
Usage
Not applicableProduction usage
Specifying a risk premium insures against serial plans being chosen when they should not
have been. However, this can also result in serial plans not being chosen where the
cardinality estimation was in fact accurate and a serial plan could have performed better.
So this setting should be used with care in order to get robustness with a net gain in
performance.
Impact
Any. There may be cases where there are different applications or workloads that might
benefit from this CQD more than other workloads. In such cases this could be used at the
Service level.
Level
ROBUST_QUERY_OPTIMIZATION is a CQD that provides a robust query setting across
the board, influencing the serial plan risk premium as well. It is advised that you use that
setting instead to influence plans, unless they are specifically addressing serial plan issues
and need to use this setting independent of that CQD.
Conflicts/Synergies
Not applicableAddressing the real
problem
Neoview Release 2.3Introduced in release
Not applicableDeprecated in release
RISK_PREMIUM_SERIAL 25