ALTER RESOURCE QUEUE

Change the limits for resource queues.

Summary

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

Note: A resource queue must have one of the values of ACTIVE_STATEMENTS or MAX_COST. Both cannot be deleted from queue_attributes in the resource queue.

describe

A resource queue must have an ACTIVE_STATEMENTS or MAX_COST value (or both). Users can set or reset the priority of a resource queue to control the CPU available resources used by queries related to the resource queue, or set the memory limit of the resource queue to control the total amount of memory that all submitted queries in the resource queue can use on a Segment host.

ALTER RESOURCE QUEUE WITHOUT Removes the restrictions specified on the resource before. A resource queue must have one of the values ACTIVE_STATEMENTS or MAX_COST. Both cannot be deleted from the queue_attributes of the resource queue.

Parameters

name

The name of the resource queue to which it is restricted.

ACTIVE_STATEMENTS integer

The number of active statements allowed in the system to submit in the resource queue at any time. The value of ACTIVE_STATEMENTS should be an integer greater than 0. To reset ACTIVE_STATEMENTS to no limit, specify 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 as kB, MB, or GB. The minimum memory quota for the resource queue is 10MB. There is no maximum value; however, the upper boundary of query execution time is limited by the physical memory of the Segment host. The default value is unlimited (-1).

MAX_COST float

The total cost of query optimizer for any time the system allows user-submitted statements in the resource queue. The value of MAX_COST is specified as a floating point number (for example 100.00) or can also be specified as an exponent (for example 1e+2). To reset MAX_COST to no limit, enter a value -1.0.

COST_OVERCOMMIT boolean

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

MIN_COST float

Queries that cost less than this limit will not be queued but run immediately. The cost is measured in units of obtained disk pages. 1.0 equals sequential disk page reads. The value of MIN_COST is specified as a floating point number (for example 100.00) or can also be specified as an exponent (for example 1e+2). To reset MIN_COST to no limit, enter a value -1.0.

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

Set the priority of the query associated with the resource queue. Queries or statements in queues with higher priority will gain more share of available CPU resources in the competition. Queries in low-priority queues may be delayed while performing higher-priority queries

Example

Change the active query limit for resource queues:

ALTER RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20);

Change the memory limit of the resource queue:

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

Reset the maximum and minimum query cost limits for resource queues to unlimited:

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

Reset the query cost limit for resource queues to 3^10 (or 300000000.0) Do not allow excessive use:

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

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

ALTER RESOURCE QUEUE myqueue WITH (PRIORITY=MIN);

Remove MAX_COST and MEMORY_LIMIT restrictions from resource queues:

ALTER RESOURCE QUEUE myqueue WITHOUT (MAX_COST, MEMORY_LIMIT);

compatibility