Auto DOP

The optimizer usually enables parallel execution to speed up queries and meet your business needs. How can we determine an appropriate amount of parallel resources? In the optimizer, the amount of parallel resources can be described by the degree of parallelism (DOP). In actual business scenarios, you can determine whether to enable parallel execution and the DOP based on your query and business needs.

You can use a system variable to enable parallel execution for all queries in the current session and specify the DOP. However, this may cause additional parallel execution overheads for queries that do not require parallel execution or do not require a high DOP in the session, resulting in performance degradation. You can also use a hint to specify the DOP for specific queries. However, in this case, you need to look into each business query separately, which is infeasible for scenarios involving a large number of business queries.

To address these limitations, the auto DOP feature of the optimizer evaluates the execution time required for a query when a query plan is generated, and then automatically determines whether to enable parallel execution and specifies an appropriate DOP for the query. This eliminates the necessity of manual DOP configuration and consequent performance degradation.

Enable auto DOP

The optimizer supports two strategies for determining the DOP: AUTO and MANUAL. In AUTO mode, the optimizer automatically enables parallel execution and determines the DOP based on the actual situation of the query. In MANUAL mode, parallel execution can be enabled only manually.

You can use the parallel_degree_policy variable to specify the DOP strategy at the global level or session level. You can also use a hint with a higher priority to specify the DOP strategy at the query level.

Use a system variable to enable or disable auto DOP

-- Enable auto DOP at the global level.
set global parallel_degree_policy = AUTO;
-- Enable auto DOP at the session level.
set session parallel_degree_policy = AUTO;
set parallel_degree_policy = AUTO;

-- Disable auto DOP at the global level.
set global parallel_degree_policy = MANUAL;
-- Disable auto DOP at the session level.
set session parallel_degree_policy = MANUAL;
set parallel_degree_policy = MANUAL;

Use a hint to enable or disable auto DOP

The hint has a higher priority than the system variable. It allows you to enable or disable auto DOP at the query level.

-- Enable auto DOP at the query level.
select /*+parallel(auto)*/ * from t1;
-- Disable auto DOP at the query level.
select /*+parallel(manual)*/ * from t1;
select /*+parallel(8)*/ * from t1;

Related parameters

The DOP calculated by auto DOP for a query is affected by the following two system variables. You can also modify the two variables based on different deployment resources and business load to affect the behavior of auto DOP.

parallel_min_scan_time_threshold

The parallel_min_scan_time_threshold parameter specifies the threshold for enabling parallel execution for base table scans, in ms. It affects whether to enable parallel execution for a query and the value of the DOP. The default value is 1000, and the minimum value is 10.

Scenarios:

You can modify the parallel_min_scan_time_threshold parameter to control the number of queries with parallel execution enabled in an AP system and the DOPs used for the queries. After auto DOP is enabled, you can decrease the value of the parallel_min_scan_time_threshold parameter if parallel execution is not enabled for some significant queries or if the DOPs for some queries are small and have not reached the maximum DOP. This way, parallel execution can be enabled for more queries, and existing queries with parallel execution enabled can support larger DOPs. In the opposite case, you can increase the value of this parameter.

If this parameter is set to an excessively small value, parallel execution will be enabled for a large number of queries at high DOPs after you enable auto DOP, which occupies excessive system resources. To avoid this problem, the default value is set to be relatively large. When you enable auto DOP, we recommend that you assess your system load and set this parameter to an appropriate value.

Examples:

-- Set the minimum estimated execution duration that triggers auto DOP to be enabled for the current session to 10.
set parallel_min_scan_time_threshold = 10;

-- Set the minimum estimated execution duration that triggers auto DOP to be enabled at the global level to 10.
set global parallel_min_scan_time_threshold = 10;

explain select /*+parallel(auto)*/ * from t1;

-- Set the `parallel_min_scan_time_threshold` parameter to 20. The actual DOP is 4, and the estimated cost of a base table scan with parallel execution enabled is 12 ms.
set parallel_min_scan_time_threshold = 20;
=========================================================
|ID|OPERATOR             |NAME    |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------
|0 |PX COORDINATOR       |        |512000  |619089      |
|1 |└─EXCHANGE OUT DISTR |:EX10000|512000  |230501      |
|2 |  └─PX BLOCK ITERATOR|        |512000  |12189       |
|3 |    └─TABLE FULL SCAN|t1      |512000  |12189       |
=========================================================
Outputs & filters:
-------------------------------------
  0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
  1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
      dop=4

-- Set the `parallel_min_scan_time_threshold` parameter to 10. The actual DOP is 6, and the estimated cost of a base table scan with parallel execution enabled is 8 ms.
set parallel_min_scan_time_threshold = 10;
=========================================================
|ID|OPERATOR             |NAME    |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------
|0 |PX COORDINATOR       |        |512000  |542256      |
|1 |└─EXCHANGE OUT DISTR |:EX10000|512000  |153667      |
|2 |  └─PX BLOCK ITERATOR|        |512000  |8126        |
|3 |    └─TABLE FULL SCAN|t1      |512000  |8126        |
=========================================================
Outputs & filters:
-------------------------------------
  0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
  1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
      dop=6

parallel_degree_limit

The parallel_degree_limit parameter specifies the maximum DOP allowed after auto DOP is enabled. The default value is 0. In this case, the optimizer limits the maximum DOP based on the number of CPU cores of the tenant and the value of the parallel_servers_target variable.

Scenarios:

After you enable auto DOP in an AP system, the system load can be high if a large number of queries require high DOPs. In this case, you can set the parallel_degree_limit variable to a value less than the number of CPU cores to limit the maximum DOP allowed.

If only one AP query is being executed in the system, retain the default value of this variable so that the maximum DOP allowed for the query is equal to the number of CPU cores. If you want to increase the maximum DOP allowed after auto DOP is enabled, you can set this parameter to a value greater than the number of CPU cores. This modification is supported only for ultimate performance optimization for a single AP query. It is not allowed in a production environment.

Examples:

-- Set the maximum DOP allowed for the current session after auto DOP is enabled to 8.
set parallel_degree_limit = 8;

-- Set the maximum DOP allowed at the global level after auto DOP is enabled to 8.
set global parallel_degree_limit = 8;

References

• For more information about auto DOP, see Auto DOP.