Database Service Names for Autonomous Database
Autonomous Database comes with predefined database services with different performance and concurrency characteristics. You must choose one of these services for each application or user that connects to the database.
The following table compares the database services. This table applies to databases with 2 OCPUs or more, or 4 ECPUs or more. You cannot use parallelism in databases that have fewer than 2 OCPUs or fewer than 4 ECPUs unless you use manual parallelism, as explained below.
Service Name | Degree of Parallelism Footnote 1 | Resource Shares Footnote 2 | Concurrent Statements Footnote 3 | |
---|---|---|---|---|
Without Compute Auto Scaling | With Compute Auto Scaling | |||
TPURGENT Footnote 5 |
Set manually Footnote 4 | 12 |
Bounded by the sessions parameter. OCPU model: The sessions parameter is set to 300 times the number of OCPUs. ECPU model: The sessions parameter is set to 75 times the number of ECPUs. |
Bounded by the sessions parameter. OCPU model: The sessions parameter is set to 300 times the number of OCPUs. ECPU model: The sessions parameter is set to 75 times the number of ECPUs. |
TP Footnote 5 |
No parallelism | 8 |
Bounded by the sessions parameter. OCPU model: The sessions parameter is set to 300 times the number of OCPUs. ECPU model: The sessions parameter is set to 75 times the number of ECPUs. |
Bounded by the sessions parameter. OCPU model: The sessions parameter is set to 300 times the number of OCPUs. ECPU model: The sessions parameter is set to 75 times the number of ECPUs. |
HIGH |
Enabled OCPU model: The degree of parallelism will be equal to the number of OCPUs ECPU model: The degree of parallelism will be half the number of ECPUs rounded down to the nearest integer value. |
4 | 3 | 9 |
MEDIUM
|
Enabled OCPU model: The degree of parallelism will be 2 with 2 OCPUs, 3 with 3 OCPUs, and 4 for more than 3 OCPUs. ECPU model: The degree of parallelism will be half the number of ECPUs rounded down to the nearest integer value with 4 to 7 ECPUs. It will be 4 with 8 ECPUs or more. |
2 |
OCPU model: 1.25 × number of OCPUs ECPU model: 0.25125 × number of ECPUs A decimal result is truncated. |
OCPU model: 3.75 × number of OCPUs ECPU model: 0.75375 × number of ECPUs A decimal result is truncated. |
LOW |
No parallelism | 1 |
Bounded by the sessions parameter. OCPU model: The sessions parameter is set to 300 times the number of OCPUs. ECPU model: The sessions parameter is set to 75 times the number of ECPUs. |
Bounded by the sessions parameter. OCPU model: The sessions parameter is set to 300 times the number of OCPUs. ECPU model: The sessions parameter is set to 75 times the number of ECPUs. |
Footnote 1
The HIGH
and MEDIUM
services have Parallel
Query, DDL, and DML enabled by default.
If you are using Autonomous Database
with Oracle Database 19c, parallel DML requires you to commit or rollback your transaction
before you can access the same table again. If you try to access the same table without
committing or rolling back the transaction, you will get the ORA-12839
error. This restriction has been lifted in Oracle Database 23ai, so you can access the same
table again using the same session without a commit or rollback.
If needed, you can disable parallel DML in your session by running:
alter session disable parallel dml;
You can also create a logon trigger to run this statement for all your sessions. Note that the performance of your large DML statements may be impacted when you disable parallel DML.
Note that these degree of parallelism values may be doubled for simple queries like a query on a single table.
The parallel hints in your SQL statements are ignored by default. If you want to control parallelism using hints, enable hints using the following command:
alter session set optimizer_ignore_parallel_hints=false;
See the following for more information:
-
About Parallel DML Operations if you are using Autonomous Database with Oracle Database 23ai.
-
Transaction Restrictions for Parallel DML if you are using Autonomous Database with Oracle Database 19c.
Footnote 2
The CPU shares assigned to each service determine how much CPU and IO the sessions using those services will get when CPU utilization in your database reaches 100%. In that case, each service will get CPU and IO resources proportional to its assigned CPU shares. You can modify these shares when needed to adjust them for your workload. See Manage CPU/IO Shares on Autonomous Database for more information.
Footnote 3
Concurrency in this table means the number of active sessions running SQL
statements concurrently. The number of sessions that do not use parallelism, for example
sessions in the LOW
service, is bounded by the sessions
database parameter. This parameter determines the number of sessions you can have in the
database independent of what service those sessions are using. After that limit is reached,
new sessions will not be able to connect to the database. For example, if your database has
the sessions
parameter set to 300, you can have different numbers of
sessions using LOW
and TP
, with the total being limited to
300.
The HIGH
and MEDIUM
services allow as many
concurrent SQL statements to run as indicated in this table assuming you are only using one
of these services. You can still submit more SQL statements using those services, but
subsequent statements will be queued after these numbers are reached. As the running
statements finish, queued sessions will be picked from the queue. If you use the
HIGH
and MEDIUM
services, or the
TPURGENT
service with parallelism services concurrently, the concurrency
you get will be lower than the documented numbers above.
To see the concurrency levels for your database, run the following command:
SELECT * FROM CS_RESOURCE_MANAGER.LIST_CURRENT_RULES();
You can also see the currency levels in the Set Resource Management Rules card in Database Actions. See Manage CPU/IO Shares on Autonomous Database for more information.
Footnote 4
Manual parallelism is the type of parallelism where you can control the parallelism for queries, DDL, and DML statements by setting parallelism degrees on your tables and indexes. See Manually Specifying the Degree of Parallelism for more information.
Footnote 5
The TP
and TPURGENT
services are not
available for Data Warehouse workloads. If you have a workload that requires manual
parallelism, which is only available with TPURGENT
, file a Service Request
at Oracle Cloud Support to request to enable the TP
and
TPURGENT
services in your Autonomous Database instance.
Topics
- Where Do I Find Connection Descriptions for Database Services?
The available database services are part of your database connect strings and you can view the services from the Oracle Cloud Infrastructure Console or view the database services names in thetnsnames.ora
in the wallet file you can download. - Which Database Service Should I Choose for My Connection, Application, or Tool?
The service you choose for your connection, application, or tool depends on your workload characteristics. - Database Service Names for Autonomous Data Warehouse
Describes the available service names for connecting to Autonomous Database instance with a Data Warehouse workload type. - Database Service Names for Autonomous Transaction Processing and Autonomous JSON Database
Describes the available service names for connecting to Autonomous Database instance with a Transaction Processing, JSON, or APEX workload type.
Parent topic: Manage Concurrency and Priorities on Autonomous Database
Where Do I Find Connection Descriptions for Database Services?
The available database services are part of your database connect strings
and you can view the services from the Oracle Cloud
Infrastructure Console or view the database services names in the tnsnames.ora
in
the wallet file you can download.
See View TNS Names and Connection Strings for an Autonomous Database Instance for the steps to view the connection strings on the Oracle Cloud Infrastructure Console,
See Download Client Credentials (Wallets) for more information.
For example, for an Autonomous Database instance with the Transaction Processing workload type named sales,
the TNS aliases will be sales_tpurgent
, sales_tp
,
sales_high
, sales_medium
, and
sales_low
.
Parent topic: Database Service Names for Autonomous Database
Which Database Service Should I Choose for My Connection, Application, or Tool?
The service you choose for your connection, application, or tool depends on your workload characteristics.
The following general guidelines can help you choose.a database service to use:
-
TP
,TPURGENT
, orLOW
: Use theTP
or theLOW
service for running OLTP applications or tools that are expected to run short-running queries and transactions. If you have different types of users who need to have different CPU and IO priorities, useTP
,TPURGENT
, andLOW
, depending on their priorities, and adjust the CPU shares for these services based on your requirements. -
MEDIUM
: Use theMEDIUM
service for analytic workloads like long-running queries scanning large data sets, batch data load processes, adhoc operational queries, and so on. If the documented degree of parallelism and concurrency level for this service does not fit your needs, you can set the concurrency level for it so that its degree of parallelism and concurrency is adjusted for your workload.Use the
MEDIUM
service for running single-user benchmarks that measure the data scan, query and DML performance for analytic workloads and set its concurrency level to 1 to allow every SQL statement to use the maximum degree of parallelism possible.See Change MEDIUM Service Concurrency Limit (ECPU Compute Model) for more information on changing the concurrency level.
-
HIGH
: Use theHIGH
service for low-concurrency analytic workloads, such as a reporting database that is accessed by only a few users simultaneously.
If you need to switch to a different service in the same session to run SQL
statements in that service, you can use the CS_SESSION
package to do
that. See CS_SESSION Package for more information.
Parent topic: Database Service Names for Autonomous Database
Database Service Names for Autonomous Data Warehouse
Describes the available service names for connecting to Autonomous Database instance with a Data Warehouse workload type.
The service names for connections to an Autonomous Database instance with a Data Warehouse workload type are in the format:
-
databasename_high
-
databasename_medium
-
databasename_low
These services map to the LOW
,
MEDIUM
, and HIGH
consumer groups.
For example, if you create an Autonomous Database with a Data Warehouse workload type and
specify the database name as DB2024
, your service names
are:
-
db2024_high
-
db2024_medium
-
db2024_low
If you connect using the db2024_low
service, the
connection uses the LOW
consumer group.
The basic characteristics of these consumer groups are:
-
HIGH: Highest resources, lowest concurrency. Queries run in parallel.
-
MEDIUM: Less resources, higher concurrency. Queries run in parallel.
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).
Depending on your compute model, ECPU or OCPU, see the following for more information.
-
LOW: Least resources, highest concurrency. Queries run serially.
After connecting to the database using one service, do not attempt to manually switch that connection to a different service by simply changing the consumer group of the connection. When you connect using a service, Autonomous Database performs more actions to configure the connection than just setting its consumer group. You can use the procedure
CS_SESSION
.SWITCH_SERVICE
to
switch to a different service.
See SWITCH_SERVICE Procedure for more information.
Parent topic: Database Service Names for Autonomous Database
Database Service Names for Autonomous Transaction Processing and Autonomous JSON Database
Describes the available service names for connecting to Autonomous Database instance with a Transaction Processing, JSON, or APEX workload type.
The available service names for connections to an Autonomous Database instance with one of: Transaction Processing, JSON, or APEX workload type are in the format:
-
databasename_tpurgent
-
databasename_tp
-
databasename_high
-
databasename_medium
-
databasename_low
These services map to the TPURGENT
,
TP
, HIGH
, MEDIUM
and LOW
consumer groups.
For example, if you create an Autonomous Database with a Transaction Processing workload
type and specify the database name as DB2024
, your
connection service names are:
-
db2024_tpurgent
-
db2024_tp
-
db2024_high
-
db2024_medium
-
db2024_low
If you connect using the db2024_tp
service, the
connection uses the TP
consumer group.
The basic characteristics of these consumer groups are:
-
TPURGENT: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.
-
TP: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.
-
HIGH: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.
-
MEDIUM: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.
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).
Depending on your compute model, ECPU or OCPU, see the following for more information.
-
LOW: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.
After connecting to the database using one service, do not attempt to manually switch that connection to a different service by simply changing the consumer group of the connection. When you connect using a service, Autonomous Database performs more actions to configure the connection than just setting its consumer group. You can use the procedure
CS_SESSION
.SWITCH_SERVICE
to
switch to a different service.
See SWITCH_SERVICE Procedure for more information.
Parent topic: Database Service Names for Autonomous Database