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

‘0’, ‘2’, ‘3’, ‘5’
The default value is ‘3’.
Values
Reduce the optimization level when compile time is longer than desired and queries have
relatively small execution cost and are simple in structure.
Usage
Use this CQD only as a workaround for queries with unacceptable compile time or plan
quality.
Production usage
Lowering the optimization level below the system default may result in inefficient query
execution plans. Increasing the optimization level over the system default may result in
very high compile time for complex queries.
Impact
QueryLevel
Not applicableConflicts/Synergies
Not applicableAddressing the real
problem
Neoview Release 1.0Introduced in release
Not applicableDeprecated in release
RISK_PREMIUM_NJ
Influencing Query PlansCategory
Influences the optimizer to choose other types of joins over nested joins, by making a nested
join 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 indicates that a nested join plan must be 20% cheaper before it is allowed
to win over competing safer (hash) join plans. A setting of 1.0 means no handicap for nested
joins. A setting of 5.0 means a nested join must be 400% cheaper before it is allowed to win
over competing hash join plans.
If it is determined that the optimizer is using nested joins often enough where these plans
are resulting in poor performance, this CQD may be used to influence the optimizer to
consider another join instead, such as a hash join, in some of those cases.
NESTED_JOINS OFF could turn nested joins off completely. However, there are many
cases where nested joins do provide better performance than hash joins, and turning them
off completely may negatively impact the performance of queries that can do a lot better
with nested joins.
Usage
Not applicableProduction usage
Specifying a risk premium insures against nested joins being chosen when they should not
have been. However, this can also result in nested joins not being chosen where the
cardinality estimation was in fact accurate and a nested join 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 nested join risk premium as well. It is advisable that you use
that setting instead to influence plans, unless they are specifically addressing nested join
issues and need to use this setting independent of that CQD.
Conflicts/Synergies
Not applicableAddressing the real
problem
24 Optimizer