Manage Concurrency and Priorities on Autonomous Database

Concurrency and prioritization of user requests in Autonomous Database is determined by the database service the user is connected with.

Database Service Names for Autonomous Data Warehouse

You are required to select a service when you connect to the database. The service names for Autonomous Data Warehouse connections 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 DB2020, your service names are:

  • db2020_high

  • db2020_medium

  • db2020_low

If you connect using the db2020_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.

    You can modify the MEDIUM service concurrency limit. See Change MEDIUM Service Concurrency Limit 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.

Database Service Names for Autonomous Transaction Processing and Autonomous JSON Database

You are required to select a service when you connect to the database. The service names for connecting to Autonomous Transaction Processing or Autonomous JSON Database 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 DB2020, your connection service names are:

  • db2020_tpurgent

  • db2020_tp

  • db2020_high

  • db2020_medium

  • db2020_low

If you connect using the db2020_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. Using this service the degree of parallelism is limited to four (4).

    You can modify the MEDIUM service concurrency limit (this also changes the degree of parallelism). See Change MEDIUM Service Concurrency Limit 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.

Idle Time Limits

Autonomous Database has predefined idle time limits for sessions so that idle sessions do not hold system resources for a long time.

A session may be terminated if it stays idle for more than five (5) minutes and the resources it consumes are needed by other users. This allows other active sessions to proceed without waiting for the idle session.

If you want sessions to be terminated after a certain amount of time, independent of the consumed resources needed by other users, then set the MAX_IDLE_TIME initialization parameter. The MAX_IDLE_TIME parameter specifies the maximum number of minutes that a session can be idle. After the specified amount of time, MAX_IDLE_TIME kills sessions.

Note:

Sessions that are idle for more than 48 hours are terminated whether they are holding resources or not.

See MAX_IDLE_TIME for more information.

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.

In this topic, the "number of OCPUs" is the CPU Core Count shown in the Oracle Cloud Infrastructure Console.

Service Concurrency Limits for Data Warehouse Workloads

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.

Database Service Name Concurrent Statements

high

3

medium

1.26 × number of OCPUs

low

300 × number of OCPUs

For example, for an Autonomous Database with 16 OCPUs, 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 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.

The following table shows sample concurrent connections values for a database with 16 OCPUs.

Database Service Name Number of Concurrent Queries

high

3

medium

20

low

Up to 4800

Service Concurrency Limits for Transaction Processing and JSON Database Workloads

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

Database Service Name Concurrent Statements

tpurgent

300 × number of OCPUs

tp

300 × number of OCPUs

high

3

medium

1.26 × number of OCPUs

low

300 × number of OCPUs

Change MEDIUM Service Concurrency Limit

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. When you change the concurrency limit, the degree of parallelism (DOP) is recalculated based on the number of OCPUs and the concurrency limit you select.

Note:

When you see "number of OCPUs" in this topic, this value refers to the CPU Core Count shown in the Oracle Cloud Infrastructure Console.

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)

Thus, 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.

To change the MEDIUM service concurrency limit, do the following:

  1. Use the PL/SQL procedure cs_resource_manager.update_plan_directive() to change 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

    The 9 in this error is the value 3 x number of 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 
    
    3 rows selected.
    

    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.

    Note:

    You cannot independently modify the DEGREE_OF_PARALLELISM value, as this value is calculated from the CONCURRENCY_LIMIT and the number of OCPUs.
  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;
/

When you revert the CONCURRENCY_LIMIT, both the CONCURRENCY_LIMIT and the DEGREE_OF_PARALLELISM values are set to their default values.

Change MEDIUM Service Concurrency Limit Notes

  • 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 between: 1 and 3 x the number of OCPUs.

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

    MEDIUM Database Service Default Value

    Concurrency Limit

    1.26 × number of OCPUs

    DOP

    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 auto scaling is disabled) or 6 x number of OCPUs (if auto scaling is enabled).

  • At any time you can return to the default values for the MEDIUM service concurrency limit and DOP.

Predefined Job Classes with Oracle Scheduler

Autonomous Database includes predefined job_class values to use with Oracle Scheduler.

The predefined job_class values, TPURGENT, TP, HIGH, MEDIUM and LOW map to the corresponding consumer groups. These job classes allow you to specify the consumer group a job runs in with DBMS_SCHEDULER.CREATE_JOB.

The DBMS_SCHEDULER.CREATE_JOB procedure supports PLSQL_BLOCK and STORED_PROCEDURE job types for the job_type parameter in Autonomous Database.

For example: use the following to create a single regular job to run in HIGH consumer group:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
     job_name => 'update_sales',
     job_type => 'STORED_PROCEDURE',
     job_action => 'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
     start_date => '28-APR-19 07.00.00 PM Australia/Sydney',
     repeat_interval => 'FREQ=DAILY;INTERVAL=2',
     end_date => '20-NOV-19 07.00.00 PM Australia/Sydney',
     auto_drop => FALSE,
     job_class => 'HIGH',
     comments => 'My new job');
END;
/

Note:

To use DBMS_SCHEDULER.CREATE_JOB additional grants for specific roles or privileges might be required. The ADMIN user and users with DWROLE have the required CREATE SESSION and CREATE JOB privileges. If a user does not have DWROLE then grants are required for CREATE SESSION and CREATE JOB privileges.

See Scheduling Jobs with Oracle Scheduler for more information on Oracle Scheduler and DBMS_SCHEDULER.CREATE_JOB.