Monitoring queuing in a default adaptive workload manager environment

Monitor your queries to better understand queuing behavior and resource consumption.

Queries are queued whenever the resource demands of the current workload exceed the configured resource capacity of the database server. Queuing is expected and by itself is not indicative of a problem or error. However, some applications or queries can cause unexpected queuing that results in unnecessary delays. For example, a long-running query that consumes close to 100% of database resources might block other incoming queries. You can use monitor elements exposed through table functions to better understand queuing behavior and identify which queries consume the most resources. If necessary, you can use the FORCE APPLICATION command to cancel a problematic query by terminating the application that submitted it.

Before issuing any monitor queries, it is recommended that you place the connection that performs the monitoring into the default administration workload (SYSDEFAULTADMWORKLOAD). This ensures that the monitor queries are not themselves queued. To do this, use the WLM_SET_CLIENT_INFO stored procedure and specify SYSDEFAULTADMWORKLOAD as the input workload name, for example:

CALL WLM_SET_CLIENT_INFO(null,null,null,null,'SYSDEFAULTADMWORKLOAD')

After the connection has completed any monitoring queries, remove the connection from the default administration workload. To do this, either close the connection or re-issue the WLM_SET_CLIENT_INFO stored procedure and specify NULL as the input workload name, for example:

CALL WLM_SET_CLIENT_INFO(null,null,null,null,null)