Manage CPU/IO Shares on Autonomous Database

Autonomous Database 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.

The CPU/IO shares assigned to the consumer groups determine the CPU/IO resources a consumer group can use with respect to the other consumer groups. The default CPU/IO shares depend on the Autonomous Database workload.

Workload Type	Details
Data Warehouse	By default, the CPU/IO shares assigned to the consumer groups HIGH, MEDIUM, LOW are 4, 2, and 1, respectively. With the default settings the consumer group HIGH will be able to use 4 times more CPU/IO resources compared to LOW and 2 times more CPU/IO resources compared to MEDIUM, when needed. The consumer group MEDIUM will be able to use 2 times more CPU/IO resources compared to LOW, when needed.
Transaction Processing JSON Database	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. 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.

Workload Type

Details

Data Warehouse

By default, the CPU/IO shares assigned to the consumer groups HIGH, MEDIUM, LOW are 4, 2, and 1, respectively. With the default settings the consumer group HIGH will be able to use 4 times more CPU/IO resources compared to LOW and 2 times more CPU/IO resources compared to MEDIUM, when needed. The consumer group MEDIUM will be able to use 2 times more CPU/IO resources compared to LOW, when needed.

Transaction Processing

JSON Database

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. 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 in Database Actions or using the PL/SQL package CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE.

To use Database Actions to change the CPU/IO share values for consumer groups:

Access Database Actions as the ADMIN user.

See Access Database Actions as ADMIN for more information.
On the Database Actions Launchpad, under Administration, click Set Resource Management Rules.
Select the CPU/IO shares tab to set CPU/IO share values for consumer groups.
Set the desired CPU/IO share value for a consumer group by entering a value or by clicking the Decrement or Increment icons.
Click Save Changes.
Click OK.

To reset CPU/IO shares values to the defaults, click Load Default Values and click Save Changes to apply the populated values.

As an alternative to using Database Actions, you can use the PL/SQL procedure CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE to change the CPU/IO share values for consumer groups.

For example, on an Autonomous Data Warehouse database, run the following script as the ADMIN user to set the CPU/IO shares to 8, 2, and 1 for consumer groups HIGH, MEDIUM, and LOW respectively. This allows the consumer group HIGH to use 4 times more CPU/IO resources compared to the consumer group MEDIUM and 8 times CPU/IO resources compared to the consumer group LOW:

BEGIN
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(consumer_group => 'HIGH', shares => 8);
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(consumer_group => 'MEDIUM', shares => 2);
   CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(consumer_group => 'LOW', shares => 1);
END;
/

For example, on an Autonomous JSON Database or on an Autonomous Transaction Processing database, run the following script as the ADMIN user to set CPU/IO shares to 12, 4, 2, 1, and 1 for the consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW respectively. This allows 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;
/

When you want to go back to the default shares values you can use the PL/SQL procedure CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES to revert to the default settings.

For example, on an Autonomous Data Warehouse database, run the following script as the ADMIN user to set the CPU/IO shares to default values for the consumer groups HIGH, MEDIUM, and LOW:

BEGIN
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'HIGH', shares => TRUE);
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'MEDIUM', shares => TRUE);
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'LOW', shares => TRUE);
END;
/

For example, on an or on an Autonomous JSON Database or on an Autonomous Transaction Processing database, run the following script as the ADMIN user to set the default values for CPU/IO shares for the consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW:

BEGIN
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'TPURGENT', shares => TRUE);
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'TP', shares => TRUE);
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'HIGH', shares => TRUE);
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'MEDIUM', shares => TRUE);
   CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'LOW', shares => TRUE);
END;
/

See CS_RESOURCE_MANAGER Package for more information.

Parent topic: Monitor and Manage Performance

Oracle Cloud Infrastructure Documentation