ALTER RESOURCE QUEUE

Note: A resource queue must have either an ACTIVE_STATEMENTS or a MAX_COST value. Do not remove both these queue_attributes from a resource queue.

ALTER RESOURCE QUEUE name WITH ( queue_attribute=value [, ... ] )

where queue_attribute is:

ACTIVE_STATEMENTS=integer

MEMORY_LIMIT='memory_units'

MAX_COST=float

COST_OVERCOMMIT={TRUE|FALSE}

MIN_COST=float

PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}

ALTER RESOURCE QUEUE name WITHOUT ( queue_attribute [, ... ] )

where queue_attribute is:

ACTIVE_STATEMENTS

MEMORY_LIMIT

MAX_COST

COST_OVERCOMMIT

MIN_COST

ALTER RESOURCE QUEUE changes the limits of a resource queue. Only a superuser can alter a resource queue. A resource queue must have either an

ACTIVE_STATEMENTS or a MAX_COST value (or it can have both). You can also set or reset priority for a resource queue to control the relative share of available CPU resources used by queries associated with the queue, or memory limit of a resource queue to control the amount of memory that all queries submitted through the queue can consume on a segment host.

ALTER RESOURCE QUEUE WITHOUT removes the specified limits on a resource that were previously set. A resource queue must have either an ACTIVE_STATEMENTS or a MAX_COST value. Do not remove both these queue_attributes from a resource queue.

name

The name of the resource queue whose limits are to be altered.

ACTIVE_STATEMENTS integer

The number of active statements submitted from users in this resource queue allowed on the system at any one time. The value for ACTIVE_STATEMENTS should be an integer greater than 0. To reset ACTIVE_STATEMENTS to have no limit, enter a value of -1.

MEMORY_LIMIT 'memory_units' 

Sets the total memory quota for all statements submitted from users in this resource queue. Memory units can be specified in kB, MB or GB. The minimum memory quota for a resource queue is 10MB. There is no maximum; however the upper boundary at query execution time is limited by the physical memory of a segment host. The default value is no limit (-1).

MAX_COST float

The total query planner cost of statements submitted from users in this resource queue allowed on the system at any one time. The value for MAX_COST is specified

as a floating point number (for example 100.0) or can also be specified as an exponent (for example 1e+2). To reset MAX_COST to have no limit, enter a value of

-1.0.

COST_OVERCOMMIT boolean

If a resource queue is limited based on query cost, then the administrator can allow cost overcommit (COST_OVERCOMMIT=TRUE, the default). This means that a query that exceeds the allowed cost threshold will be allowed to run but only when the system is idle. If COST_OVERCOMMIT=FALSE is specified, queries that exceed the cost limit will always be rejected and never allowed to run.

MIN_COST float

Queries with a cost under this limit will not be queued and run immediately. Cost is measured in units of disk page fetches; 1.0 equals one sequential disk page read. The value for MIN_COST is specified as a floating point number (for example 100.0) or can also be specified as an exponent (for example 1e+2). To reset MIN_COST to have no limit, enter a value of -1.0.

PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}

Sets the priority of queries associated with a resource queue. Queries or statements in queues with higher priority levels will receive a larger share of available CPU

resources in case of contention. Queries in low-priority queues may be delayed while higher priority queries are executed.

Example

Change the active query limit for a resource queue: 

ALTER RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=25);

Change the memory limit for a resource queue: 

ALTER RESOURCE QUEUE myqueue WITH (MEMORY_LIMIT='2GB');

Reset the maximum and minimum query cost limit for a resource queue to no limit: 

ALTER RESOURCE QUEUE myqueue WITH (MAX_COST=-1.0, MIN_COST= -1.0);

Reset the query cost limit for a resource queue to 310 (or 30000000000.0) and do not allow overcommit:

ALTER RESOURCE QUEUE myqueue WITH (MAX_COST=3e+10,  COST_OVERCOMMIT=FALSE);

Reset the priority of queries associated with a resource queue to the minimum level: 

ALTER RESOURCE QUEUE myqueue WITH (PRIORITY=MIN);

Remove the MAX_COST and MEMORY_LIMIT limits from a resource queue:

ALTER RESOURCE QUEUE myqueue WITHOUT (MAX_COST, MEMORY_LIMIT);