Concurrency and prioritization of user requests in Autonomous Data Warehouse is determined by the database service the user is connected with.
Users are required to select a service when connecting to the database. The service names are in the format:
These services map to the
HIGH consumer groups. For example, if you provision
an Autonomous Data Warehouse service with the name ADW1, your
service names are:
For example, a user connecting with the
adw1_low service uses the 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.
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
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,
See MAX_IDLE_TIME for more information.
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.
to the corresponding consumer groups. These job classes allow you to specify the
consumer group a job runs in with
DBMS_SCHEDULER.CREATE_JOB procedure supports
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; /
DBMS_SCHEDULER.CREATE_JOBadditional grants for specific roles or privileges might be required. The
ADMINuser and users with
DWROLEhave the required
CREATE JOBprivileges. If a user does not have
DWROLEthen grants are required for
See Scheduling Jobs with Oracle
Scheduler for more information on Oracle Scheduler and