Best practices

Because the queuing of queries is based on resource usage, it is important to be mindful of the impact that open cursors and long running queries can have on other work.

For example:

A poorly written application that does not close its cursors in a timely manner can cause queuing, because cursors hold and use resources for as long as they remain open.
A poorly formed query that is submitted in error and consumes a large amount of resources can block other work.

Use the monitoring queries described in Monitoring queuing in adaptive workload manager environment and Monitoring queuing in a customized adaptive workload manager environment to identify queries that are affecting throughput due to high resource usage as well as to identify queries that are blocked on a client (activity_state is IDLE).

Consider using WLM thresholds to define rules that detect and terminate applications or queries that adversely affect database performance. For example:

Use a connection idle-time threshold to terminate any connection that remains idle for more than 1 hour:
```
CREATE THRESHOLD MAX_IDLE_TIME FOR DATABASE WHEN CONNECTIONIDLETIME > 1 HOUR STOP EXECUTION
```

Use an activity runtime threshold to terminate any query that runs for more than 2 hours:

CREATE THRESHOLD MAX_QUERY_RUNTIME FOR DATABASE WHEN ACTIVITYTOTALRUNTIME > 2 HOURS STOP EXECUTION

Use a shared sort memory threshold to terminate any query using an excessive amount of sort memory if sort memory usage is causing queuing:
```
CREATE THRESHOLD MAX_SORT_MEM FOR DATABASE WHEN SORTSHRHEAPUTIL > 50 PERCENT AND BLOCKING ADMISSION FOR > 5 MINUTES STOP EXECUTION
```

If queries in service class running lower priority work are using an excessive amount of sort memory, causing the queuing of higher priority work, consider applying an activity sort memory limit to the service class where the lower priority work is executing. Applying an activity sort memory limit will reduce the memory used by queries, though they may execute slower. For example, to limit the memory usage for individual queries in the LOWPRI service superclass to 20% of the sheapthres_shr parameter value, run the following statement:

ALTER SERVICE CLASS LOWPRI ACTIVITY SORTMEM LIMIT 20