Change MEDIUM Service Concurrency Limit (ECPU Compute Model)
If your application requires customized concurrency, you can modify the concurrency limit for your Autonomous Database MEDIUM service.
Picking one of the predefined services provides concurrency values that work well for most applications. In cases where selecting one of the default services does not meet your application’s performance needs, you can use the MEDIUM service and modify the concurrency limit. For example, when you run single-user benchmarks, you can set the concurrency limit of the MEDIUM service to 1 in order to obtain the highest degree of parallelism (DOP).
Changing the concurrency limit is only allowed for an instance that has four (4) or more ECPUs.
For example, with Compute auto scaling disabled, if your instance is configured with 400 ECPUs, by default Autonomous Database provides a concurrency limit of 100 for the MEDIUM service:
0.25125 x number of ECPUs
sessions (up to 100 concurrent
queries). A decimal result is truncated.
In this example the MEDIUM service supports an application with up to 100 concurrent queries with DOP of 4. If you only need 50 concurrent queries and you want a higher DOP you can decrease the concurrency limit and the database increases the DOP. To do this, set the MEDIUM service concurrency limit to 50. When you change the concurrency limit the database calculates and sets the DOP based on the concurrency limit you select and the number of ECPUs. For this example, with the concurrency limit set to 50, the new DOP is 12.
With Compute auto scaling enabled, the DOP is set to a value three times greater. In this example the DOP value would be 36.
You can change the concurrency limit for the MEDIUM service in Database Actions or
using the PL/SQL package CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE
.
Follow these steps to change the MEDIUM service concurrency limit in Database Actions:
To reset the concurrency limit for the MEDIUM service to its default value, click Load Default Values and click Save Changes.
- Change MEDIUM Service Concurrency Limit with PL/SQL Procedure UPDATE_PLAN_DIRECTIVE (ECPU Compute Model)
As an alternative to using the Set Resource Management Rules card in Database Actions, you can use PL/SQL to change the concurrency limit for the MEDIUM service. - Change MEDIUM Service Concurrency Limit Notes (ECPU Compute Model)
Related Topics
Parent topic: Manage Concurrency and Priorities on Autonomous Database
Change MEDIUM Service Concurrency Limit with PL/SQL Procedure UPDATE_PLAN_DIRECTIVE (ECPU Compute Model)
As an alternative to using the Set Resource Management Rules card in Database Actions, you can use PL/SQL to change the concurrency limit for the MEDIUM service.
To change the MEDIUM service concurrency limit with CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE
:
When you want to go back to the default values, use the CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES
PL/SQL
procedure to revert to the default settings for the MEDIUM service.
For example:
BEGIN
CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES
(consumer_group => 'MEDIUM', concurrency_limit => TRUE);
END;
/
See CS_RESOURCE_MANAGER Package for more information.
Change MEDIUM Service Concurrency Limit Notes (ECPU Compute Model)
-
Changing the concurrency limit is only allowed for the MEDIUM service.
-
Changing the concurrency limit is only allowed when the number of ECPUs is greater than or equal to 4.
-
Changing the concurrency limit also changes the degree of parallelism (in some cases the value does not change, depending on the magnitude of the difference between the old concurrency limit and the new value you set).
-
The concurrency limit you set must be in the range:
- With Compute auto
scaling disabled: between 1 and
.75 x the number of ECPUs
- With Compute auto
scaling enabled: between 1 and
2.25 x the number of ECPUs
- With Compute auto
scaling disabled: between 1 and
-
The MEDIUM service sets the following concurrency limit and DOP values by default:
MEDIUM Database Service Default Value with Compute Auto Scaling Disabled Default Value with Compute Auto Scaling Enabled Concurrency Limit
0.25125 × number of ECPUs
when the number of ECPUs ≥ 8A decimal result is truncated
2 when the number of ECPUs is in the range
4 ≤ ECPUs < 8
0.75375 × number of ECPUs
when the number of ECPUs ≥ 8A decimal result is truncated
6 when the number of ECPUs is in the range:
4 ≤ ECPUs < 8
DOP
4 when the number of ECPUs ≥ 8
or
TRUNC (ECPU/2)
, when the number of ECPUs < 84 when the number of ECPUs ≥ 8
or
TRUNC (ECPU/2)
, when the number of ECPUs < 8 -
By changing the value of the concurrency limit, the DOP of the MEDIUM service can go as low as 2 and as high as
.75 * number of ECPUs
(if Compute auto scaling is disabled) or2.25 x number of ECPUs
(if Compute auto scaling is enabled).See Use Auto Scaling for information on Compute auto scaling.
-
At any time you can return to the default values for the MEDIUM service concurrency limit and DOP.