The priority of user requests in Autonomous Transaction Processing is determined by the database service the user is connected with.
Database Service Names Overview
You are required to select a service when you connect 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
service with the name ATP1, your service names are:
For example, a user connecting
atp1_tp 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 initialization parameter. The
MAX_IDLE_TIME parameter specifies the maximum
number of minutes that a session can be idle. After the specified amount of
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.
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
job types for the
job_type parameter in Autonomous Transaction
For example: use the following to create a single regular job to run
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