SQE Symmetric Multiprocessing Changes

On IBM® i 7.5, the behavior of the PARALLEL_DEGREE *OPTIMIZE setting is changed for database queries.

When Db2 Symmetric Multiprocessing (SMP) is enabled, the PARALLEL_DEGREE value *OPTIMIZE enables the query optimizer to determine the optimal number of parallel tasks with which to run a query.

In most environments, SMP provides the greatest benefit to longer-running queries. With this update, Db2 for i now offers a feature which enables the optimizer to only consider SMP for queries that run for longer than a user-configurable time threshold. This allows the optimizer to apply SMP specifically to those queries that will benefit the most. When running with PARALLEL_DEGREE *OPTIMIZE or *OPTIMIZE%, all queries will initially run without the use of SMP. Only when queries have exceeded the time threshold will they be eligible to use SMP. This feature is delivered with the Db2 for i 7.5 PTF group SF99950 level 3.

Minimum Runtime for Parallel Queries

A new QAQQINI option, PARALLEL_MIN_TIME, describes the minimum time in seconds that a query must run before the optimizer may consider using SMP. When PARALLEL_DEGREE option *OPTIMIZE is used, queries will initially execute with a non-parallel implementation. If a query completes within the configured PARALLEL_MIN_TIME number of seconds, the optimizer will not run the query with SMP. If the query runs longer than the configured amount of time, the optimizer may choose to replace the plan with a parallel implementation. If a parallel implementation is chosen, all subsequent executions of the query will also use SMP. The optimizer retains information about the choice of SMP for each query (identified by its QRO hash) until the plan cache is cleared or until the next IPL.

By default, PARALLEL_MIN_TIME is set to 60 seconds, and may be set to any integer value between 2 and 100,000, inclusive. In order for PARALLEL_MIN_TIME to take effect, PARALLEL_DEGREE must be set to *OPTIMIZE or *OPTIMIZE %.

The new behavior does not apply to native queries, when Adaptive Query Processing is disabled, or when PARALLEL_MIN_TIME is set to *NONE. Under these conditions, all queries using PARALLEL_DEGREE *OPTIMIZE will consider parallel execution during their initial and all subsequent optimizations, which is the same behavior as prior releases.

QAQQINI Parameter PARALLEL_DEGREE value *OPTIMIZE %

As an additional control, a percentage value may be specified for values of *OPTIMIZE when PARALLEL_DEGREE is configured with a QAQQINI file. The percentage value provides the optimizer with additional instruction when determining the number of tasks used to execute a query. In previous releases, this percentage was applied after optimization finished but before execution began.

Starting in IBM i 7.5, the percentage specified for *OPTIMIZE will be applied prior to optimization. This will provide the optimizer with more accurate information related to the number of tasks a query may run with. Because the number of tasks is taken into account during the creation of the execution plan, the overall execution plan (and degrees contained within the execution plan) may change, affecting query performance. In general, query and system performance are expected to improve, but some individual queries may be negatively affected. If no percentage is specified, the optimizer behavior remains the same as in prior releases.

Maximum System CPU Limits for Parallel Queries

Starting in IBM i 7.5, the query engine will dynamically adjust the number of tasks used by queries running with PARALLEL_DEGREE *OPTIMIZE. As these queries run, the query engine will consider the overall system CPU usage.

If the system CPU usage exceeds 80 percent, query tasks will be paused until the system CPU usage falls below 80 percent, at which time the tasks will resume running. At no time will the number of tasks executing for a query be less than one. Queries that run with reduced tasks may run more slowly, but they will continue to run to completion. The purpose of this behavior is to improve overall system responsiveness by providing a reserve level of system utilization.

The desired system utilization level may be modified from the default value of 80 percent by specifying a value for the new QAQQINI parameter PARALLEL_MAX_SYSTEM_CPU. A value of *NONE will disable the new function and will allow query tasks to run without respect to system CPU usage, which is the same behavior as prior releases.