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.
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)
Thetnsnames.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. - Service Concurrency Limits for Transaction Processing Workloads (ECPU Compute Model)
Thetnsnames.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. - Service Concurrency Limits for Data Warehouse Workloads (OCPU Compute Model)
Thetnsnames.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. - Service Concurrency Limits for Transaction Processing and JSON Database Workloads (OCPU Compute Model)
Thetnsnames.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.
Parent topic: Manage Concurrency and Priorities on Autonomous Database
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.
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 |
---|---|---|
|
3 | 9 |
|
0.25125 × number of ECPUs A decimal result is truncated. |
0.75375 × number of ECPUs A decimal result is truncated. |
|
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.
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 |
---|---|---|
|
3 |
9 |
|
8 |
24 |
|
Up to 2400 |
Up to 2400 |
See Change MEDIUM Service Concurrency Limit (ECPU Compute Model) for more information.
Parent topic: Service Concurrency
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.
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 |
---|---|---|
|
75 × number of ECPUs |
75 × number of ECPUs |
|
75 × number of ECPUs |
75 × number of ECPUs |
|
3 |
9 |
|
0.25125 × number of ECPUs
A decimal result is truncated. |
0.75375 × number of ECPUs
A decimal result is truncated. |
|
75 × number of ECPUs |
75 × number of ECPUs |
See Change MEDIUM Service Concurrency Limit (ECPU Compute Model) for more information.
Parent topic: Service Concurrency
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.
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 |
---|---|---|
|
3 | 9 |
|
1.26 × number of OCPUs | 3.78 × number of OCPUs |
|
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 |
---|---|---|
|
3 |
9 |
|
20 |
60 |
|
Up to 4800 |
Up to 4800 |
Parent topic: Service Concurrency
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.
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 |
---|---|---|
|
300 × number of OCPUs | 300 × number of OCPUs |
|
300 × number of OCPUs | 300 × number of OCPUs |
|
3 | 9 |
|
1.26 × number of OCPUs | 3.78 × number of OCPUs |
|
300 × number of OCPUs | 300 × number of OCPUs |
Parent topic: Service Concurrency