Using Runtime Plan Choice
Runtime Plan Choice (RTPC) is a configuration option that allows the SQL optimizer to take advantage of outlier value information at run time (query execution time). When Adaptive Mode is turned on, RTPC is turned on; however, you may still turn RTPC on even if you have turned Adaptive Mode off.
When RTPC is activated, query preparation includes detecting whether the query contains a condition on a field that has an outlier value. If the prepare detects one or more outlier field conditions, the query is not sent to the optimizer; instead, a Runtime Plan Choice stub is generated. At execution time, the optimizer uses this stub to choose which query plan to execute: a standard query plan that ignores outlier status or an alternative query plan that optimizes for outlier status. If there are multiple outlier value conditions, the optimizer can choose from multiple alternative run time query plans.
Note that the display of the RTPC query plan differs based on the source of the SQL code:
-
The Show Plan button in Management Portal SQL interface may display an alternative run time query plan because this Show Plan takes its SQL code from the SQL interface text box.
-
If the statement has values for all parameters, the plan returned takes those values into account when preparing an optimal plan using RTPC. However, if any parameters have ? as a placeholder, a plan that does not make use of parameter values and contains the text “Different parameters may use a different plan” is returned.
-
If RTPC is not activated, or the query does not contain appropriate outlier field conditions, the optimizer creates a standard SQL Statement and a corresponding cached query.
-
If an RTPC stub is frozen, all associated alternative run time query plans are also frozen. RTPC processing remains active for a frozen query even when the RTPC configuration option is turned off.
-
You can manually suppress literal substitution when writing the query by specifying parentheses: SELECT Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=(('Yes')). If you suppress literal substitution of the outlier field in a condition, RTPC is not applied to the query. In this case, the optimizer creates a standard cached query with literal substitution.
Application of RTPC
For SELECT and CALL statements, the system applies RTPC to any field that Tune Table has determined to have an outlier value, when that field is specified in a condition where it is compared to a literal. This comparison condition can be:
-
A WHERE clause condition using an equality (=), non-equality (!=), IN, or %INLIST predicate.
-
An ON clause join condition with an equality (=), non-equality (!=), IN, or %INLIST predicate.
If RTPC is applied, the optimizer determines at run time whether to apply the standard query plan or an alternative query plan.
RTPC is not applied to INSERT, UPDATE, or DELETE statements or in the following cases:
-
The query contains unresolved ? input parameters.
-
The query specifies the literal value surrounded by double parentheses, suppressing literal substitution.
-
The literal is supplied to the outlier field condition by a subquery that does not contain an outlier field condition.
Overriding or Disabling RTPC
You can override RTPC for a specific query by specifying the %NORUNTIME restriction keyword. If the query SELECT Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=? would result in RTPC processing, the query SELECT %NORUNTIME Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=? would override RTPC, resulting in a standard query plan.
When Adaptive Mode is turned off, you can enable or disable RTPC for all processes system-wide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("RTPC",flag,.oldval). The flag argument is a boolean used to set (1) or unset (0) RTPC. The oldvalue argument returns the prior RTPC setting as a boolean value.
To determine the current settings, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.