Product DocumentationAppearance
Rule Actions
The following table lists the specific rule actions that you can trigger based on one or more conditions. 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. | Prepare | 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. | Prepare, 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. | Prepare, Runtime | Any number |
| Set Lookup Query | Pin the query in memory because it is likely to be run frequently. | Prepare | 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. | Prepare | True or False |
| Set Query Tags | Set query tags that will be logged for this query. See Query Tags. | Prepare, Runtime, Completion | 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. | Prepare | 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 exceededYou 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. | Prepare | Any number |
| Set Maximum Wait Limit | Set a limit, in seconds, for how long a query may wait in a WLM queue. | Prepare | Any number |
| Set Memory Estimate | Set a value for the estimated runtime memory that is expected to be consumed by the query. For example, by measuring its performance over time, you may know that a particular type of query tends to take less memory than the slot it will be running in is allocated. Conversely, your query could use more memory than the smallest allocation provided by a flex pool, but you want to indicate that it needs more. (Use Query Details > Statistics > Memory Used Estimate in the SMC or memory_estimated_bytes in sys.log_query to find this value for past executions of a query.) | Prepare | 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. | Prepare | 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. | Prepare | 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. | Prepare, Runtime, Completion | Any string |
| Abort Query | Cancel the query and return a message to the client that states the reason. | Prepare, Runtime | Any string |
| Move Query to New Resource Pool | Move a running query to a different resource pool. See Moving a Query to Another Pool. | Prepare, Runtime | Default pool for profile or profile name and pool name (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. | Prepare, Runtime, Completion | 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. | Prepare | No value required |
| Assign Smallest Pool for Query Estimated Memory | Assign the smallest pool that fits the specified minimum amount of memory. | Prepare | 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. | Prepare | No value required |
| Assign Largest Temporary Space Resource Pool | Assign the resource pool that has the largest amount of temporary space for spilling. | Prepare | No value required |
| Assign Least Busy Resource Pool | Assign the resource pool that is the least busy when the query comes in. | Prepare | 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. | Prepare |
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.
Parent topic:Creating Rules