Managing Priorities on Autonomous Transaction Processing

The priority of user requests in Autonomous Transaction Processing 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_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 provision an Autonomous Transaction Processing service with the name ATP1, your service names are:

  • atp1_tpurgent

  • atp1_tp

  • atp1_high

  • atp1_medium

  • atp1_low

For example, a user connecting with the atp1_tp service uses the consumer group TP.

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

  • 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 Transaction Processing performs more actions to configure the connection than just setting its consumer group.

Idle Time Limits

Autonomous Transaction Processing 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.

Predefined Job Classes with Oracle Scheduler

Autonomous Transaction Processing 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 Transaction Processing.

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.