Find the connections (sessions) that have consumed the most resources over their lifetime. This view is helpful for diagnosing problems where large numbers of small activities in aggregate are consuming excessive resources. A common scenario of this type is applications loading data one row at a time using singleton inserts. This can be very disruptive but each individual insert is tiny and so is the singleton transaction associated with it. This view will highlight connections engaged in these sort of activities.

Note that this overlaps somewhat in functionality with the Sessions (connections) view. If you go to the connections view, in counter mode, you could select various columns one at a time and sort by them to find the connections that have consumed the most CPU, read the most rows, etc. This view provides a convenient way to get similar information in one step.

Following is the variable used as denominator in this section:

TOTAL_TIME = MON_GET_DATABASE(#MEMBER#).SUM(LOCK_WAIT_TIME + TOTAL_SECTION_SORT_TIME + POOL_READ_TIME + POOL_WRITE_TIME + POOL_ASYNC_READ_TIME + POOL_ASYNC_WRITE_TIME + PREFETCH_WAIT_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME)

The percentage of time spent waiting for locks.

= LOCK_WAIT_TIME / TOTAL_TIME

Source:

LOCK_WAIT_TIME = MON_GET_DATABASE(#MEMBER#).SUM(LOCK_WAIT_TIME)

The percentage of time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application.

= TOTAL_SECTION_SORT_TIME / TOTAL_TIME

Source:

TOTAL_SECTION_SORT_TIME = MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_SECTION_SORT_TIME)

The percentage of time spent reading and writing in data and index pages from the table space containers (physical) for all types of table spaces.

= BP_IO_TIME / TOTAL_TIME

Source:

BP_IO_TIME = MON_GET_DATABASE(#MEMBER#).SUM(POOL_READ_TIME + POOL_WRITE_TIME)

The percentage of time for asynchronous writes and reads to complete.

= BP_ASYNC_IO_TIME / TOTAL_TIME

Source:

BP_ASYNC_IO_TIME = MON_GET_DATABASE(#MEMBER#).SUM(POOL_ASYNC_READ_TIME + POOL_ASYNC_WRITE_TIME)

The percentage of time an application spent waiting for an I/O server (prefetcher) to finish loading pages into the buffer pool.

= PREFETCH_WAIT_TIME / TOTAL_TIME

Source:

PREFETCH_WAIT_TIME = MON_GET_DATABASE(#MEMBER#).SUM(PREFETCH_WAIT_TIME)

The percentage of time required to perform the direct reads and writes.

= DIRECT_IO_TIME / TOTAL_TIME

Source:

DIRECT_IO_TIME = MON_GET_DATABASE(#MEMBER#).SUM(DIRECT_READ_TIME + DIRECT_WRITE_TIME)

The connection with maximum amount of CPU time used while within the database system.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(TOTAL_CPU_TIME)

The connection with maximum number of rows read from the table.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(ROWS_READ)

The connection with maximum number of rows that have been selected and returned to the application.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(ROWS_RETURNED)

The connection with maximum number of rows inserted, updated, or deleted.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(ROWS_MODIFIED)

The connection with maximum number of pages which have been requested from the buffer pool (logical).

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS)

The connection with maximum number of pages which have been requested from the buffer pool (logical) or been written to disk.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS + POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES)

The connection with maximum amount of committed memory in use by this memory pool.

Source: MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_POOL_USED)

The connection with maximum amount of time spent performing sorts while executing a section.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(TOTAL_SECTION_SORT_TIME)

The connection with maximum number of sorts that have been executed.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(TOTAL_SORTS)

The connection with maximum number of sorts that ran out of sort heap and may have required disk space for temporary storage.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(SORT_OVERFLOWS)

The connection with maximum number of times a threshold was violated.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(THRESH_VIOLATIONS)

The connection with maximum amount of data received via or distributed by the FCM communications layer.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(FCM_SEND_VOLUME + FCM_RECV_VOLUME)

The connection with maximum number of subagents associated with an application.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(NUM_ASSOC_AGENTS)

The connection with maximum number of locks currently held.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(NUM_LOCKS_HELD)

The connection with maximum number of times that locks have been escalated from several row locks to a table lock.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(LOCK_ESCALS)

The connection with maximum number of times that a request to lock an object timed out instead of being granted.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(LOCK_TIMEOUTS)

The connection with maximum number of times that applications or connections waited for locks.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(LOCK_WAITS)

The connection with maximum elapsed time spent waiting for locks.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(LOCK_WAIT_TIME)

The connection with maximum time spent on global lock waits.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(LOCK_WAIT_TIME_GLOBAL)

The connection with maximum number of lock waits due to the application holding the lock being on a remote member.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(LOCK_WAITS_GLOBAL)

The connection with maximum amount of time spent communicating with the cluster caching facility.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(CF_WAIT_TIME)

The connection with maximum number of times that the database system waited while it communicated with a cluster caching facility.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).SUM(CF_WAITS)

A system-wide unique ID for the application.

Source: MON_GET_CONNECTION(NULL, #MEMBER#).APPLICATION_HANDLE

The percentage of application resource usage.

The corresponding resource value.

The name of the application running at the client, as known to the database or Db2 Connect server.

Source: MON_GET_CONNECTION.APPLICATION_NAME

-