Rule Actions

The following table lists the specific rule actions that you can trigger based on one or more conditions (as shown in the SMC). Each action applies to one or more rule types.
Action Description Rule Types Accepted Values
Set Resource Pool Assign the query to a specific WLM resource pool.

See also the other "Assign" rules in this table, which provide some flexibility in choosing resource pools.

Assemble, Compile Select from the drop-down list.
Set Priority Reduce or increase the CPU priority of the query. The priority of queries is set exclusively by rules. Priority starts at Normal, and rules may set priority to a lower (Low) or a higher (High or Critical) value. See Priority Levels. Submit, Assemble, Compile, Runtime Low, Normal, High, Critical
Set Cost Override the cost set by the planner. This action can be used to influence the behavior of subsequent rules that are applied to the same query. Assemble, Compile, Runtime Any number
Set Lookup Query Pin the query in memory because it is likely to be run frequently. Assemble, Compile True or False
Set Short Query Bias A “short query” is expected to run very quickly and use resources for a very short time; therefore, it is given a temporary boost in priority to get it finished under concurrent loads. Assemble, Compile True or False
Set Query Tags Set query tags that will be logged for this query. See Using Query Tags. All types Any string
Set Maximum Execution Time Limit Set a limit on the maximum execution time, in seconds. Cancel the query if the threshold is reached. Assemble, Compile Any number
Set Maximum Row Limit Set a row limit. A query is aborted with the following error if it attempts to return more than the specified number of rows:
WLM row limit exceeded
You can also set a maximum row limit for individual resource pools when you create them. Note that by default the system rule global_defaultRowLimit imposes a limit of 5,000,000 rows on non-superuser queries. Superuser queries are not subject to this limit. If necessary, you can modify (increase or decrease) this global row limit.
Assemble, Compile Any number
Set Maximum Wait Limit Set a limit, in seconds, for how long a query may wait in a WLM queue. Assemble, Compile Any number
Set Requested Memory (in megabytes) or Set Requested Memory Percent Set the requested amount of memory, either in megabytes, or as a percentage. Assemble, Compile Any number
Set Requested Temporary Space (in megabytes) or Set Requested Temporary Space Percent The maximum spill size per slot is fixed by the WLM configuration and calculated up-front when the resource pool is configured. This size may not be exceeded by a query, and all queries get this value for their slot when they start. A WLM rule, however, can restrict spill size. To disable spilling, or allow queries to go out of memory earlier, you can set the value to a lower number. Setting a higher value results in a warning being logged. Assemble, Compile Any number
Log at INFO, DEBUG, WARN, ERROR Log a user-defined message at the specified logging level. Logging messages at WARN and ERROR levels sends a "WLM Rule Alert" to any configured alert endpoints. See System Alerts. All types Any string
Abort Query Cancel the query and return a message to the client that states the reason. Submit, Assemble, Compile, Runtime Any string
Move Query to New Resource Pool Move a running query to a different resource pool. See Moving Queries. Compile, Runtime Default pool for profile or profile name and pool name (select from drop-down list)
Restart Query in New Pool Restart a query in a different resource pool. See Restarting Queries Compile, Runtime Select from drop-down list
Store Execution Statistics Turn on or off statistics logging to the sys.log_query view. For example, you may decide that an application that performs well does not need any statistics recorded during execution of any of its queries. All types True or False
Assign Most Concurrent Resource Pool [ With Temp Space, Without Temp Space ] Assign the resource pool that has the most concurrency (execution slots). There are three variations of this rule so you can take temporary space for spilling into account. Assemble, Compile No value required
Assign Smallest Pool for Query Estimated Memory Assign the smallest pool that fits the specified minimum amount of memory. Assemble, Compile Enter the minimum memory value (in MB)
Assign Largest Memory Resource Pool [ With Temp Space, Without Temp Space ] Assign the resource pool that has the largest memory allocation. There are three variations of this rule so you can take temporary space for spilling into account. Assemble, Compile No value required
Assign Largest Temporary Space Resource Pool Assign the resource pool that has the largest amount of temporary space for spilling. Assemble, Compile No value required
Assign Least Busy Resource Pool Assign the resource pool that is the least busy when the query comes in. Assemble, Compile No value required
Limit Concurrent Queries Limit the number of concurrent queries that can run in a resource pool. Optionally, specify per-application, role, or user limits. Submit, Assemble, Compile Number of queries and an optional throttle. For example: wlm.throttle(2, w.application)

Priority Levels

Priority settings affect the behavior of queries both before they are executed and during their execution. When the system is busy and queries are queued up (waiting), queries are sent to the worker nodes for execution based on their priority (Low, Normal, High, and Critical). Resource pools maintain four different queues, one for each level. The system serves higher-priority queries before lower-priority queries, increasing the allocation of CPU time proportionally at each level by 2-3x. For example, if you have two concurrent queries, one set to Low and one set to Normal, the Normal query takes approximately 75% of the CPU time, and the Low query approximately 25% (the remainder).

During execution, priority settings are only meaningful if queries are running concurrently and incoming concurrent queries are assigned different priorities. Two queries operating at the same priority are given equal share of the system. If only one query is running, it gets 100% of the available CPU, regardless of its priority.