Change MEDIUM Service Concurrency Limit (OCPU 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).

Note:

Changing the concurrency limit is only allowed for an instance that has two (2) or more OCPUs.

For example, if your instance is configured with 100 OCPUs, by default Autonomous Database provides a concurrency limit of 126 for the MEDIUM service:

1.26 x number of OCPUs sessions (up to 126 concurrent queries)

In this example using the MEDIUM service supports an application with up to 126 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 thus increase the DOP. To do this, set the MEDIUM service concurrency limit to 50. When you change the concurrency limit the system calculates and sets the DOP based on the concurrency limit you select and the number of OCPUs. For this example, with the concurrency limit set to 50, the new DOP is 12. When OCPU auto scaling is 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:

1. Access Database Actions as the ADMIN user.

   See Access Database Actions as ADMIN for more information.

2. On the Database Actions Launchpad, under Administration, click Set Resource Management Rules.
3. On the Set Resource Management Rules page, select the Concurrency limit tab.
   
   Description of the illustration adb_set_concurrency_limit_ocpu.png
4. For the MEDIUM service, change the value to the desired concurrency limit by entering a value or by clicking the Decrement or Increment icons.

   If the concurrency limit you specify is not valid, based on the number of OCPUs, you will receive a message such as the following, listing the valid range of values for your instance:

   Please enter a concurrency limit between 1 and 300

   This error message example is from an instance with 100 OCPUs (the 300 maximum value shown is 3 x number of OCPUs).

5. Click Save Changes.
6. Click OK.

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 (OCPU 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 (OCPU Compute Model)
  

Change MEDIUM Service Concurrency Limit with PL/SQL Procedure UPDATE_PLAN_DIRECTIVE (OCPU 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:

1. Call the PL/SQL procedure CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE to update the concurrency limit for the MEDIUM consumer group.

   For example, with 3 OCPUs, change the MEDIUM service's concurrency limit to 2, as follows:

   BEGIN
       CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(consumer_group => 'MEDIUM', concurrency_limit => 2); 
   END;
   /
   

   If the concurrency_limit you specify is not valid, based on the number of OCPUs, you will receive a message such as the following, listing the valid range of values for your instance:

   ORA-20000: Invalid or missing value. Concurrency limit must be between 1 and 9 for the specified OCPU count

   This error message example is from an instance with 3 OCPUs.

2. Use the PL/SQL function CS_RESOURCE_MANAGER.LIST_CURRENT_RULES to verify the updated MEDIUM service concurrency limit and degree of parallelism:

   SELECT * FROM CS_RESOURCE_MANAGER.LIST_CURRENT_RULES();
   
   CONSUMER_GROUP ELAPSED_TIME_LIMIT IO_MEGABYTES_LIMIT SHARES CONCURRENCY_LIMIT DEGREE_OF_PARALLELISM 
   -------------- ------------------ ------------------ ------ ----------------- --------------------- 
   HIGH                                                      4                 3                     3 
   MEDIUM                                                    2                 2                     9 
   LOW                                                       1               900                     1 
   
   

   This procedure returns the list of values for all consumer groups. After you modify the concurrency limit as specified in Step 1, check the MEDIUM service CONCURRENCY_LIMIT and DEGREE_OF_PARALLELISM values to verify your changes.

3. After you change the concurrency limit for the MEDIUM service, test your application by connecting with the MEDIUM service to verify that the customized concurrency limit meets your performance objectives.

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 (OCPU Compute Model)

• Changing the concurrency limit is only allowed for the MEDIUM service.

• Changing the concurrency limit is only allowed when the number of OCPUs is greater than 1.

• Changing the concurrency limit also changes the degree of parallelism (in most cases, 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 OCPU auto scaling disabled: between: 1 and 3 x the number of OCPUs

  • With OCPU auto scaling enabled: between 1 and 9 x the number of OCPUs

• The MEDIUM service sets the following concurrency limit and DOP values by default:

  MEDIUM Database Service	Default Value with OCPU Auto Scaling Disabled	Default Value with OCPU Auto Scaling Enabled
  Concurrency Limit	1.26 × number of OCPUs when the number of OCPUs ≥ 4 5 when the number of OCPUs < 4	3.78 × number of OCPUs when the number of OCPUs ≥ 4 15 when the number of OCPUs < 4
  DOP	4 when the number of OCPUs ≥ 4 or The number of OCPUs, when the number of OCPUs < 4	4 when the number of OCPUs ≥ 4 or The number of OCPUs, when the number of OCPUs < 4

• By changing the value of the concurrency limit, the DOP of the MEDIUM service can go as low as 2 and as high as: 2 x number of OCPUs (if compute auto scaling is disabled) or 6 x number of OCPUs (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.