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 a default 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
```