Manage CPU/IO Shares on Autonomous AI Database

Autonomous AI 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 CPU shares assigned to each service determine how much CPU and IO the sessions using those services will get when CPU utilization in your database reaches 100%. In that case, each service will get CPU and IO resources proportional to its assigned CPU shares. The Oracle internal processes that run as automated tasks on Autonomous AI Database, such as automatic optimizer statistics gathering, run with a resource share of 1.

Workload Type	Details
All Workload Types	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:

1. Access Database Actions as the ADMIN user.

   See Access Database Actions as ADMIN for more information.

2. On the Database Actions Launchpad, under Administration, click Set Resource Management Rules.
3. Select the CPU/IO shares tab to set CPU/IO share values for consumer groups.
4. Set the desired CPU/IO share value for a consumer group by entering a value or by clicking the Decrement or Increment icons.
5. Click Save Changes.
6. 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, 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, 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.