ALTER RESOURCE POOL

Syntax

ALTER RESOURCE POOL pool_name SET (resource_setting [, ...n])

resource_setting:
    MEMORY_PERCENTAGE = percent
  | QUERY_MEMORY_PERCENTAGE
  | QUERY_TIMEOUT = seconds
  | SOFT_CPU_LIMIT_PERCENTAGE = percent
  | HARD_CPU_LIMIT_PERCENTAGE = percent
  | MAX_CONCURRENCY = integer
  | MAX_QUEUE_DEPTH = integer

For information on each resource setting, see CREATE RESOURCE POOL.

Remarks

• You cannot alter the built-in resource pools.

• CREATE POOL or DROP POOL privileges are required for altering a resource pool.

• Changes made to a resource pool take effect immediately, but running queries in the pool won’t be killed if the pool currently exceeds its new memory limit.

• The resource pool settings MEMORY_PERCENTAGE, QUERY_TIMEOUT, SOFT_CPU_LIMIT_PERCENTAGE, HARD_CPU_LIMIT_PERCENTAGE, MAX_CONCURRENCY, and MAX_QUEUE_DEPTH in the resource pools default_pool, system_auto, and system_optimizer_pool are fixed. These settings cannot be changed.

• Refer to the Permission Matrix for the required permission.

Example

The following example alters resource limits in two resource pools.

Before altering the current limits for the resource pools, it is advisable to set the current limits to a lower value, to create headroom.

ALTER RESOURCE POOL executive SET HARD_CPU_LIMIT_PERCENTAGE = 10;
ALTER RESOURCE POOL general SET HARD_CPU_LIMIT_PERCENTAGE = 10;
ALTER RESOURCE POOL executive SET MEMORY_PERCENTAGE = 10;
ALTER RESOURCE POOL general SET MEMORY_PERCENTAGE = 10;

Now set the new resource limits, as required:

ALTER RESOURCE POOL executive SET HARD_CPU_LIMIT_PERCENTAGE = 75;
ALTER RESOURCE POOL general SET HARD_CPU_LIMIT_PERCENTAGE = 25;
ALTER RESOURCE POOL executive SET MEMORY_PERCENTAGE = 55;
ALTER RESOURCE POOL general SET MEMORY_PERCENTAGE = 45;
ALTER RESOURCE POOL executive SET QUERY_MEMORY_PERCENTAGE = 10;