Using Adaptive Mode to Improve Performance
InterSystems SQL packages multiple options that define query planning and execution behavior in Adaptive Mode, an on by default setting that ensures the best out of the box performance for a wide set of use cases. Specifically, Adaptive Mode controls Runtime Plan Choice (RTPC), parallel processing, and automatically runs TUNE TABLE to optimize the efficiency of query execution. The individual features that Adaptive Mode governs cannot be controlled independently without turning Adaptive Mode off.
Runtime Plan Choice
RTPC is turned on when Adaptive Mode is active, enabling the optimizer to take advantage of runtime parameter values supplied within a query to determine the most optimal plan. For more information about the how RTPC functions, see Configure Runtime Plan Choice
Note that RTPC can only be effective when it has accurate information about the contents of the table. As a result, the efficacy of RTPC relies on the table statistics collected by calling TUNE TABLE.
System-Wide Auto-Parallel
When Adaptive Mode is on, system-wide automatic parallel query processing is turned on. Consequently, all SELECT queries are automatically hinted with %PARALLEL so that parallel processing is applied to any query that may benefit from it.
However, the automatic hinting does not mean that all queries are executed with parallel processing. The SQL Optimizer may decide that a query is not a good candidate for parallel processing and ignore the hint. For a list of some examples where parallel processing is not applied, even when Adaptive Mode is applied, see Parallel Query Processing Ignored.
The auto parallel threshold configuration parameter will also determine whether a query is executed in parallel or not. The higher the threshold value is, the lower the chance that parallel processing will be used. This threshold is used in complex optimization calculations, but you can think about this value as the minimal number of tuples that must reside in the visited map before the optimizer considers parallel query processing worthwhile. By default, this value is 3200. The minimum is 0. You may change the value of this setting by using $SYSTEM.SQL.Util.SetOption("AutoParallelThreshold",n,.oldval) , where n is the value you would like to set for the auto parallel threshold (.oldval is a return variable that will hold the overwritten value).
In a sharded environment, all queries will be executed with parallel processing, regardless of the parallel threshold value, when Adaptive Mode is turned on.
For more information about memory usage with parallel processing, see Shared Memory Considerations in Configure Parallel Processing.
Auto-Tune
For the query optimizer to choose the most efficient query plans, your tables will need to have an up-to-date set of statistics that accurately describe the data contained within the table. These statistics can be gathered through an efficient sampling using the TUNE TABLE command or by hard-coding them in the table definition. New tables will typically not have these statistics until a call to TUNE TABLE has been made. Adaptive Mode ensures that TUNE TABLE is run automatically on tables that do not have any statistics and are eligible for fast block sampling before the first query issued against them is executed. This facility ensures that no query misses out on the substantial performance benefits that come with gathering a set of table statistics.
For more information about some of the statistics that TUNE TABLE collects, see Table Statistics for Query Optimizer.
It is still appropriate to run TUNE TABLE after a table has been loaded with representative data or making significant changes to its contents. The auto-tune facility only operates once, when the table is queried for the first time, which may occur before the table is populated with representative data.
Turning Adaptive Mode Off
In the uncommon case that you want to turn Adaptive Mode off, you can do so by navigating to System Administration > Configuration > SQL and Object Settings > SQL and selecting the check box labelled “Turn off Adaptive Mode to disable run time plan choice and automatic tuning.”
When Adaptive Mode is off, InterSystems recommends that you consider manually configuring Runtime Plan Choice and parallel processing based on the needs of your application, and that you run TUNE TABLE manually when the distribution of your data changes significantly.