Manage CPU/IO Shares on Autonomous Data Warehouse

Autonomous Data Warehouse 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 HIGH, MEDIUM, LOW are 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 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.

You can set CPU/IO shares from the service console or using the PL/SQL package cs_resource_manager.update_plan_directive.

Follow these steps to set CPU/IO shares from the service console:

  1. From the Autonomous Data Warehouse details page, click Service Console.
  2. On the Service Console click Administration.
  3. Click Set Resource Management Rules.
  4. Select CPU/IO shares to set CPU/IO share values for consumer groups.
  5. Set the CPU/IO share values.
  6. Click Save changes.

For example, the following figure shows CPU/IO share values that you can modify:

Description of dwcs_manage_set_shares.png follows
Description of the illustration dwcs_manage_set_shares.png

Click Load Default Values to load the default values; then click Save changes to apply the populated values.

You can also change the default values using the PL/SQL procedure cs_resource_manager.update_plan_directive:

For example, running the following script with the ADMIN user sets CPU/IO shares to 8, 2, and 1 for consumer groups HIGH, MEDIUM, and LOW respectively. This will allow 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;
/