Oracle Cloud Infrastructure Documentation

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 base OCPUs.

ECPU model: The sessions parameter is set to 75 times the number of base ECPUs.

Same values as without Auto Scaling
TP Footnote 5 No parallelism 8

Bounded by the sessions parameter.

OCPU model: The sessions parameter is set to 300 times the number of base OCPUs.

ECPU model: The sessions parameter is set to 75 times the number of base ECPUs.

Same values as without Auto Scaling
HIGH

Enabled

OCPU model: The degree of parallelism will be equal to the number of base OCPUs

ECPU model: The degree of parallelism will be half the number of base 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 base 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 base OCPUs

ECPU model: 0.25125 × number of base ECPUs

A decimal result is truncated.

OCPU model: 3.75 × number of base OCPUs

ECPU model: 0.75375 × number of base 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 base OCPUs.

ECPU model: The sessions parameter is set to 75 times the number of base ECPUs.

Same values as without Auto Scaling.

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 parallelism hints in your SQL statements are ignored by default in Autonomous Database instances with the Data Warehouse workload type. The parallelism hints in your SQL statements are honored in Autonomous Database instances with Transaction Processing, JSON, or APEX workload types. If you want to control parallelism using hints in an Autonomous Database instance with the Data Warehouse workload, you can enable hints using the following command. 

alter session set optimizer_ignore_parallel_hints=false;

See the following for more information:

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 parallelism in multiple services concurrently, or if you use parallelism hints to set the degree of parallelism for your SQL statements, the concurrency you get may be different than the documented numbers shown in this table.

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

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.

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, or LOW: Use the TP or the LOW 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, use TP, TPURGENT, and LOW, depending on their priorities, and adjust the CPU shares for these services based on your requirements.

  • MEDIUM: Use the MEDIUM 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 the HIGH 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.

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.

Note

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.

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.

Note

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.