Manage Database Service Consumer Groups

Oracle Autonomous Transaction Processing uses Oracle Database Resource Manager consumer groups to define different workload characteristics for each of the pairs of database services described in Predefined Database Service Names for Autonomous Transaction Processing Dedicated Databases. If the default values used in these consumer groups do not meet a given database's usage model, you can change the values.

Manage CPU/IO Shares

Autonomous Transaction Processing comes with predefined CPU/IO shares assigned to different consumer groups. You can modify these predefined CPU/IO shares if your workload requires different CPU/IO resource allocations.

By default, the CPU/IO shares assigned to the consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW are 12, 8, 4, 2, and 1, respectively. The shares determine how much CPU/IO resources a consumer group can use with respect to the other consumer groups. With the default settings the consumer group TPURGENT will be able to use 12 times more CPU/IO resources compared to LOW, when needed. The consumer group TP will be able to use 4 times more CPU/IO resources compared to MEDIUM, when needed.

You can set CPU/IO shares using the PL/SQL package cs_resource_manager.update_plan_directive. For example, running the following script as the ADMIN user sets CPU/IO shares to 12, 4, 2, 1, and 1 for consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW respectively. This will allow the consumer group TPURGENT to use 3 times more CPU/IO resources compared to the consumer group TP and 12 times CPU/IO resources compared to the consumer group MEDIUM:

BEGIN
   cs_resource_manager.update_plan_directive(consumer_group => 'TPURGENT', shares => 12);
   cs_resource_manager.update_plan_directive(consumer_group => 'TP', shares => 4);
   cs_resource_manager.update_plan_directive(consumer_group => 'HIGH', shares => 2);
   cs_resource_manager.update_plan_directive(consumer_group => 'MEDIUM', shares => 1);
   cs_resource_manager.update_plan_directive(consumer_group => 'LOW', shares => 1);
END;
/

Manage Runaway SQL Statements

Use the PL/SQL procedure cs_resource_manager.update_plan_directive to change the rules Autonomous Transaction Processing uses to terminate SQL statements automatically for a given consumer group.

When a SQL statement in the specified consumer group runs more than the specified runtime limit or does more IO than the specified amount, then the SQL statement will be terminated.

For example, to set a runtime limit of 120 seconds and an IO limit of 1000MB for the HIGH consumer group run the following command when connected to the database as the ADMIN user:

BEGIN
     cs_resource_manager.update_plan_directive(consumer_group => 'HIGH', io_megabytes_limit => 1000, elapsed_time_limit => 120);
END;
/	

To reset the values and lift the limits, you can set the values to null:

BEGIN
     cs_resource_manager.update_plan_directive(consumer_group => 'HIGH', io_megabytes_limit => null, elapsed_time_limit => null);
END;
/