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

No risk premium‘MIN’
Higher levels of risk premium‘HIGH’ and
‘MAXIMUM’
Safe risk premium‘SYSTEM’
The default value is ‘SYSTEM’.
Values
MAXIMUM tells the optimizer to make the safest choice of query plans. This means:
• RISK_PREMIUM_NJ is set to 5.0 – nested join must be 400% cheaper before it can win
over competing (hash) join plans
• RISK_PREMIUM_SERIAL is set to 2.0 – serial plan must be 100% cheaper before it can
win over competing parallel plans
• PARTITIONING_SCHEME_SHARING is set to 2 – no partition scheme sharing between
adjacent ESP fragments
HIGH tells the optimizer to make a safer choice of query plans. This means:
• RISK_PREMIUM_NJ is set to 2.5 – nested join must be 150% cheaper before it can win
over competing (hash) join plans
• RISK_PREMIUM_SERIAL is set to 1.5 – serial plan must be 50% cheaper before it can
win over completing parallel plans
• PARTITIONING_SCHEME_SHARING is set to 1 – subset sharing of partition schemes
between adjacent ESP fragments
SYSTEM tells the optimizer to make a safe choice of query plans. This means:
• RISK_PREMIUM_NJ is set to 1.2 – nested join must be 20% cheaper before it can win
over competing (hash) join plans
• RISK_PREMIUM_SERIAL is set to 1.2 – serial plan must be 20% cheaper before it can
win over completing parallel plans
• PARTITIONING_SCHEME_SHARING is set to 1 – subset sharing of partition schemes
between adjacent ESP fragments
MIN tells the optimizer to believe its cardinality estimates are always correct when choosing
query plans. For example, don’t apply any risk premium for risky operations. This means:
• RISK_PREMIUM_NJ is set to 1.0 – nested join can win over competing (hash) join plans
purely based on cost & cardinality estimates
• RISK_PREMIUM_SERIAL is set to 1.0 – serial plan can win over completing parallel
plans purely based on cost & cardinality estimates
• PARTITIONING_SCHEME_SHARING is set to 0 – complete sharing of partition schemes
between adjacent ESP fragments
If histograms are accurate and the queries are relatively simple then you could choose a
lower robustness setting. In complex query environments where queries could end up
processing large amounts of data, you should consider higher settings.
If you notice that when queries are not performing well it is due to either nested join plans,
serial plans, or reduced parallelism, then you could consider increasing risk premiums to
see if you can get overall better performance.
Usage
It is best to try out different options to achieve best overall performance in a test environment
before implementing the changed settings in a production environment.
Production usage
Specifying a risk premium insures against nested joins or serial plans being chosen when
they should not have been. However, this can also result in such plans not being chosen
where the cardinality estimation was in fact accurate and such plans 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 27