AdaptiveMode

Enable Adaptive Mode performance optimization options.

Synopsis

[SQL]    AdaptiveMode=n

n is either 1 or 0. The default value is 1.

Description

When AdaptiveMode is enabled (n = 1), InterSystems SQL uses Adaptive Mode, which enables automation and adaptive settings whenever possible. Adaptive Mode configures the following options:

Disables freezing of existing query plans when you upgrade InterSystems IRIS® data platform to a new major version. Upon upgrade, non-frozen query plans are invalidated and replanned when called for the first time after upgrade. For more details, see Frozen Plans.
Enables automatic table tuning when an untuned table is first queried. With table tuning, InterSystems SQL gathers statistics from a table that it can use to optimize future queries. For more details, see Table Statistics for Query Optimizer.
Enables Runtime Plan Choice. In doing so, InterSystems SQL takes the runtime values of query parameters into account to consider alternative plans, including in the presence of outliers. As a result, enabling this parameter overrides the older BiasQueriesAsOutlier and RTPC settings. However, in cases where Runtime Plan Choice is not applied to a comparison condition in a query (regardless of the AdaptiveMode setting being enabled), the current setting of BiasQueriesAsOutlier (enabled or disabled) can affect the query plan, review the query plan for more information.

Changing This Parameter

On the SQL page of the Management Portal (System Administration > Configuration > SQL and Object Settings > SQL), select Turn off Adaptive Mode to disable run time plan choice and automatic tuning to disable Adaptive Mode.

You can also change AdaptiveMode with the Config.SQLOpens in a new tab class (as described in the class reference) or by editing the CPF in a text editor (as described in Editing the Active CPF).