The priority of user requests in Autonomous Transaction Processing 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
LOW consumer groups. For example, if you provision an Autonomous Transaction
Processing service with the name ATP1, your
service names are:
For example, a user connecting with the
service uses the 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).
LOW: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.
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 parameter specifies
the maximum number of minutes that a session can be idle. After the
specified amount of time,
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.
Predefined Job Classes with Oracle Scheduler
Processing includes predefined
job_class values to use with Oracle Scheduler.
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 procedure supports
STORED_PROCEDURE job types for the
job_type parameter in Autonomous Transaction
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