Service Concurrency

The consumer groups of the predefined service names provide different levels of performance and concurrency. The available service names are different depending on your workload: Data Warehouse, Transaction Processing, or JSON Database.

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).

In this topic, the "number of ECPUs" is the ECPU count shown in the Oracle Cloud Infrastructure Console. Likewise, if your database uses the OCPU compute model, the "number of OCPUs" is the OCPU count.

Note:

OCPU is a legacy billing metric and has been retired for Autonomous Data Warehouse (Data Warehouse workload type) and Autonomous Transaction Processing (Transaction Processing workload type). Oracle recommends using ECPUs for all new and existing Autonomous Database deployments. See Oracle Support Document 2998742.1 for more information.

Service Concurrency Limits for Data Warehouse Workloads (ECPU Compute Model)

The tnsnames.ora file provided with the credentials zip file contains three database service names identifiable as high, medium, and low for Autonomous Database with Data Warehouse workloads.

The following shows the details for the number of concurrent statements for each connection service for Data Warehouse workloads, with Compute auto scaling disabled, and with Compute auto scaling enabled.

Note:

The values in this table apply when the number of ECPUs is equal to or greater than 4.

When the number of ECPUs is 2, all services use the concurrency limit 150. When the number of ECPUs is 3, all services use the concurrency limit 225 (this applies for Compute auto scaling enabled or disabled).

Database Service Name Number of Concurrent Queries with Compute Auto Scaling Disabled Number of Concurrent Queries with Compute Auto Scaling Enabled

high

3 9

medium

0.25125 × number of ECPUs

A decimal result is truncated.

0.75375 × number of ECPUs

A decimal result is truncated.

low

75 × number of ECPUs 75 × number of ECPUs

When these concurrency levels are reached for the MEDIUM and HIGH consumer groups, new SQL statements in that consumer group will be queued until one or more running statements finish. With the LOW consumer group, when the concurrency limit is reached you will not be able to connect new sessions.

For example, with auto scaling disabled on an Autonomous Database instance with 32 ECPUs, the HIGH consumer group will be able to run 3 concurrent SQL statements when the MEDIUM consumer group is not running any statements. The MEDIUM consumer group will be able to run 8 concurrent SQL statements when the HIGH consumer group is not running any statements. The LOW consumer group will be able to run 2400 concurrent SQL statements.

Note:

The HIGH consumer group can run at least 1 SQL statement when the MEDIUM consumer group is also running statements.

For this example with auto scaling enabled on an Autonomous Database instance with 32 ECPUs, the HIGH consumer group will be able to run 9 concurrent SQL statements when the MEDIUM consumer group is not running any statements. The MEDIUM consumer group will be able to run 24 concurrent SQL statements when the HIGH consumer group is not running any statements. The LOW consumer group will be able to run 2400 concurrent SQL statements.

The following table shows sample concurrent connections values for a database with 32 ECPUs with Compute auto scaling disabled and with Compute auto scaling enabled.

Database Service Name Number of Concurrent Queries with Compute Auto Scaling Disabled Number of Concurrent Queries with Compute Auto Scaling Enabled

high

3

9

medium

8

24

low

Up to 2400

Up to 2400

See Change MEDIUM Service Concurrency Limit (ECPU Compute Model) for more information.

Service Concurrency Limits for Transaction Processing Workloads (ECPU Compute Model)

The tnsnames.ora file provided with the credentials zip file contains five database service names identifiable as tpurgent, tp, high, medium, and low for Autonomous Database with Transaction Processing or JSON workloads.

The following shows the details for the default number of concurrent statements for each connection service for Transaction Processing or JSON workloads.

Note:

The values in this table apply when the number of ECPUs is equal to or greater than 4.

When the number of ECPUs is 2, all services use the concurrency limit 150. When the number of ECPUs is 3, all services use the concurrency limit 225 (this applies for Compute auto scaling enabled or disabled).

Database Service Name Concurrent Statements with Compute Auto Scaling Disabled Concurrent Statements with Compute Auto Scaling Enabled

tpurgent

75 × number of ECPUs

75 × number of ECPUs

tp

75 × number of ECPUs

75 × number of ECPUs

high

3

9

medium

0.25125 × number of ECPUs

A decimal result is truncated.

0.75375 × number of ECPUs

A decimal result is truncated.

low

75 × number of ECPUs

75 × number of ECPUs

See Change MEDIUM Service Concurrency Limit (ECPU Compute Model) for more information.

Service Concurrency Limits for Data Warehouse Workloads (OCPU Compute Model)

The tnsnames.ora file provided with the credentials zip file contains three database service names identifiable as high, medium and low for Autonomous Database with Data Warehouse workloads.

The following shows the details for the number of concurrent statements for each connection service for Data Warehouse workloads, with OCPU auto scaling disabled, and with OCPU auto scaling enabled.

Note:

The values in this table apply when the number of OCPUs is greater than 1. With 1 OCPU, the concurrency limit for each service is 300 and the DOP is 1.
Database Service Name Concurrent Statements with OCPU Auto Scaling Disabled Concurrent Statements with OCPU Auto Scaling Enabled

high

3 9

medium

1.26 × number of OCPUs 3.78 × number of OCPUs

low

300 × number of OCPUs 300 × number of OCPUs

When these concurrency levels are reached for the MEDIUM and HIGH consumer groups, new SQL statements in that consumer group will be queued until one or more running statements finish. With the LOW consumer group, when the concurrency limit is reached you will not be able to connect new sessions.

For example, for an Autonomous Database instance with 16 OCPUs and OCPU auto scaling disabled, the HIGH consumer group will be able to run 3 concurrent SQL statements when the MEDIUM consumer group is not running any statements. The MEDIUM consumer group will be able to run 20 concurrent SQL statements when the HIGH consumer group is not running any statements. The LOW consumer group will be able to run 4800 concurrent SQL statements. The HIGH consumer group can run at least 1 SQL statement when the MEDIUM consumer group is also running statements. When OCPU auto scaling is enabled, for the HIGH and MEDIUM consumer groups the concurrency values will be three times larger.

The following table shows sample concurrent connections values for a database with 16 OCPUs, showing the values with both OCPU auto scaling enabled and disabled.

Database Service Name Number of Concurrent Queries with OCPU Auto Scaling Disabled Number of Concurrent Queries with OCPU Auto Scaling Enabled

high

3

9

medium

20

60

low

Up to 4800

Up to 4800

Service Concurrency Limits for Transaction Processing and JSON Database Workloads (OCPU Compute Model)

The tnsnames.ora file provided with the credentials zip file contains five database service names identifiable as tpurgent, tp, high, medium, and low for Autonomous Database with Transaction Processing or with JSON Database workloads.

The following shows the details for the default number of concurrent statements for each connection service for Transaction Processing or JSON Database workloads, with OCPU auto scaling disabled, and with OCPU auto scaling enabled.

Note:

The values in this table apply when the number of OCPUs is greater than 1. With 1 OCPU, the concurrency limit for each service is 300 and the DOP is 1.
Database Service Name Concurrent Statements with OCPU Auto Scaling Disabled Concurrent Statements with OCPU Auto Scaling Enabled

tpurgent

300 × number of OCPUs 300 × number of OCPUs

tp

300 × number of OCPUs 300 × number of OCPUs

high

3 9

medium

1.26 × number of OCPUs 3.78 × number of OCPUs

low

300 × number of OCPUs 300 × number of OCPUs