Managing Concurrency and Priorities on Autonomous Data Warehouse

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

Overview

Users are required to select a service when connecting to the database. The service names 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 provision an Autonomous Data Warehouse service with the name ADW1, your service names are:

  • adw1_high

  • adw1_medium

  • adw1_low

For example, a user connecting with the adw1_low service uses the consumer group LOW.

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.

  • LOW: Least resources, highest concurrency. Queries run serially.

Idle Time Limits

Autonomous Data Warehouse 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.

See MAX_IDLE_TIME for more information.

Concurrency

The concurrency level of these consumer groups changes based on the number of OCPUs you subscribe to. The HIGH consumer group’s concurrency is fixed and does not change based on the number of OCPUs. The MEDIUM and LOW consumer groups can run more concurrent SQL statements if you scale up the compute capacity of your service.

Note:

The HIGH consumer group is configured for low concurrency, even a single query in this consumer group can use all resources in your database. If your workload has concurrent queries Oracle recommends using the MEDIUM consumer group. If your concurrency requirements are not met with the MEDIUM consumer group, you can use the LOW consumer group or you can scale up your compute capacity and continue using the MEDIUM consumer group.

For example, for an Autonomous Data Warehouse 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 1600 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 a consumer group new SQL statements in that consumer group will be queued until one or more running statements finish.

Predefined Job Classes with Oracle Scheduler

Autonomous Data Warehouse includes predefined job_class values to use with Oracle Scheduler.

The predefined job_class values, LOW, MEDIUM, and HIGH 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 Data Warehouse.

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.