Sessions/Connections view tells you at a glance which connections are active, blocked, or idle, who is connected, what applications they are running and how much of various resources they are consuming. Connections (sometimes also known as sessions) are tied to users and applications. You can drill down on a connection to see details of the current state of execution of SQL that is in question.

The percentage of executing connections among all active connections.

= ACTIVE_CONN / TOTAL_CONN

TOTAL_CONN: The number of agents that have AGENT_STATE of ACTIVE and non-zero APPLICATION_HANDLE.

ACTIVE_CONN: The number of agents that have EVENT_STATE of EXECUTING, EVENT_TYPE of PROCESS and idle duration (CURRENT TIMESTAMP - AGENT_STATE_LAST_UPDATE_TIME) less than 15 seconds.

Source: MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#)

The total number of FCM buffers sent from the current database member to a remote database member.

Source: MON_GET_FCM_CONNECTION_LIST(#MEMBER#).SUM(TOTAL_BUFFERS_SENT)

The total number of FCM buffers received from a remote database member.

Source: MON_GET_FCM_CONNECTION_LIST(#MEMBER#).SUM(TOTAL_BUFFERS_RCVD)

The number of dynamic SQL statements that were attempted.

Source: MON_GET_DATABASE(#MEMBER#).SUM(DYNAMIC_SQL_STMTS)

The number of pages read by agents from the buffer pool (logical).

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

The application ID of the oldest units of work appeared in the list.

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

The total amount of log (in bytes) that will have to be redone for crash recovery.

Source: MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(LOG_TO_REDO_FOR_RECOVERY)

The total number of sorts that have been executed.

Source: MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_SORTS)

The number of agents that have AGENT_STATE of ACTIVE and non-zero APPLICATION_HANDLE.

Source: MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#)

The total number of times a page cleaner was invoked.

Source: MON_GET_DATABASE(#MEMBER#).SUM(POOL_LSN_GAP_CLNS + POOL_DRTY_PG_STEAL_CLNS + POOL_DRTY_PG_THRSH_CLNS)

The total number of hash joins that are currently running and consuming memory.

Source: MON_GET_DATABASE(#MEMBER#).SUM(ACTIVE_HASH_JOINS)

The number of static SQL statements that were attempted.

Source: MON_GET_DATABASE(#MEMBER#).SUM(STATIC_SQL_STMTS)

The number of times a buffer pool page was physically written to disk.

Source: MON_GET_DATABASE(#MEMBER#).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES)

The total number of database files closed.

Source: MON_GET_DATABASE(#MEMBER#).SUM(FILES_CLOSED)

The number of log pages written to disk by the logger.

Source: MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(LOG_WRITES)

Total number of statements issued by the client application.

Source: MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_APP_COMMITS + TOTAL_APP_ROLLBACKS)

The percentage of times a page cleaner was invoked because the logging space used had reached a predefined criterion for the database.

= POOL_LSN_GAP_CLNS / PAGE_CLEANS

Source:

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

PAGE_CLEANS = MON_GET_DATABASE(#MEMBER#).SUM(POOL_LSN_GAP_CLNS + POOL_DRTY_PG_STEAL_CLNS + POOL_DRTY_PG_THRSH_CLNS)

The total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken.

Source: MON_GET_DATABASE(#MEMBER#).SUM(SORT_HEAP_ALLOCATED)

The amount of space already used on a file system pointed to by a storage path or container.

Source: MON_GET_CONTAINER(”,#MEMBER#).SUM(FS_USED_SIZE)

The total number of OLAP functions that are currently running and consuming sort heap memory.

Source: MON_GET_DATABASE(#MEMBER#).SUM(ACTIVE_OLAP_FUNCS)

The total number of hashed GROUP BY sort memory requests that were limited because of a concurrent use of the shared or private sort heap space.

Source: MON_GET_DATABASE(#MEMBER#).SUM(POST_THRESHOLD_HASH_JOINS)

The number of read operations that do not use the buffer pool.

Source: MON_GET_DATABASE(#MEMBER#).SUM(DIRECT_READS)

The total number of deadlocks that have occurred.

Source: MON_GET_DATABASE(#MEMBER#).SUM(DEADLOCKS)

The average elapsed time spent by the logger writing log data to the disk.

= LOG_WRITE_TIME / LOG_WRITES

Source:

LOG_WRITE_TIME = MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(LOG_WRITE_TIME)

LOG_WRITES = MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(LOG_WRITES)

The ratio of sorts that ran out of sort heap and may have required disk space for temporary storage.

= SORT_OVERFLOWS / TOTAL_SORTS

Source:

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

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

The average number of SQL statements that were attempted per connection.

= SQL_STMTS / TOTAL_CONS

Source:

SQL_STMTS = MON_GET_DATABASE(#MEMBER#).SUM(DYNAMIC_SQL_STMTS + STATIC_SQL_STMTS)

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

The percentage of a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database.

= POOL_DRTY_PG_STEAL_CLNS / PAGE_CLEANS

Source:

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

PAGE_CLEANS = MON_GET_DATABASE(#MEMBER#).SUM(POOL_LSN_GAP_CLNS + POOL_DRTY_PG_STEAL_CLNS + POOL_DRTY_PG_THRSH_CLNS)

The amount of space is free on a file system pointed to by a storage path or container.

Source: MON_GET_CONTAINER(”,#MEMBER#).SUM(FS_TOTAL_SIZE - FS_USED_SIZE)

The total number of OLAP functions executed.

Source: MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_OLAP_FUNCS)

The total number of utilities currently running on the database. Number of rows in MON_GET_UTILITY is counted to get the value.

Source: MON_GET_UTILITY(#MEMBER#)

The lowest number of free FCM buffers reached during processing.

Source: MON_GET_FCM(#MEMBER#).SUM(BUFF_FREE_BOTTOM)

The maximum number of coordinating agents working at one time.

Source: MON_GET_DATABASE(#MEMBER#).SUM(COORD_AGENTS_TOP)

The number of times that partial early distinct operations received less memory than requested due to sort heap threshold being exceeded.

Source: MON_GET_DATABASE(#MEMBER#).SUM(POST_THRESHOLD_SORTS)

The number of write operations that do not use the buffer pool.

Source: MON_GET_DATABASE(#MEMBER#).SUM(DIRECT_WRITES)

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

Source: MON_GET_DATABASE(#MEMBER#).SUM(LOCK_ESCALS)

The number of log pages read from disk by the logger.

Source: MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(LOG_READS)

Average sorts that have been executed per statement issued by the client application

= TOTAL_SORTS / TOTAL_TRANS

Source:

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

TOTAL_TRANS = MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_APP_COMMITS + TOTAL_APP_ROLLBACKS)

Counter:

The total number of statements issued by the client application.

Source: MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_APP_COMMITS + TOTAL_APP_ROLLBACKS)

Delta:

The average number of sorts that have been executed per second.

= TOTAL_SORTS / (CURRENT TIMESTAMP - DB_CONN_TIME)

Source:

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

CURRENT TIMESTAMP = CURRENT TIMESTAMP

DB_CONN_TIME = MON_GET_DATABASE(#MEMBER#).MIN(DB_CONN_TIME)

The percentage of times a page cleaner was invoked because a buffer pool had reached the dirty page threshold criterion for the database.

= POOL_DRTY_PG_THRSH_CLNS / PAGE_CLEANS

Source:

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

PAGE_CLEANS = MON_GET_DATABASE(#MEMBER#).SUM(POOL_LSN_GAP_CLNS + POOL_DRTY_PG_STEAL_CLNS + POOL_DRTY_PG_THRSH_CLNS)

The percentage of space that is used on a file system pointed to by a storage path or container.

= 1 - FS_FREE_SIZE_BYTES / (FS_FREE_SIZE_BYTES + FS_USED_SIZE)

Source:

FS_FREE_SIZE_BYTES = MON_GET_CONTAINER(”,#MEMBER#).SUM(FS_TOTAL_SIZE - FS_USED_SIZE)

FS_USED_SIZE = MON_GET_CONTAINER(”,#MEMBER#).SUM(FS_USED_SIZE)

The number of active connections.

The number of agents that have EVENT_STATE of EXECUTING, EVENT_TYPE of PROCESS and idle duration (CURRENT TIMESTAMP - AGENT_STATE_LAST_UPDATE_TIME) less than 15 seconds.

Source: MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#)

A system-wide unique ID for the application.

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

The percentage of CPU time usage of the session.

= TOTAL_CPU_TIME / SESSION_CPU_TOTAL

SESSION_CPU_TOTAL: The overall amount of CPU time used while within the database system over all sessions.

Source:

TOTAL_CPU_TIME = MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(TOTAL_CPU_TIME)

The percentage of CPU time usage of the activity.

= STMT_TOTAL_CPU_TIME_US / STMT_CPU_TOTAL

STMT_CPU_TOTAL: The overall amount of CPU time used by activities over all session.

Source:

STMT_TOTAL_CPU_TIME_US = MON_GET_ACTIVITY(NULL, #MEMBER#).SUM(TOTAL_CPU_TIME)

The percentage of pages written and read of the session.

= TOTAL_IO / IO_TOTAL

IO_TOTAL: The overall number of pages written and read over all sessions.

Source:

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

The percentage of amount of committed memory in use.

= MEMORY_POOL_USED / MEMORY_TOTAL

MEMORY_TOTAL: The overall amount of committed memory in use overall all session.

Source:

MEMORY_POOL_USED = MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_POOL_USED)

State of the event last processed by this agent. The possible values are as follows:

Source: WLM_GET_SERVICE_CLASS_AGENTS(NULL,NULL,NULL,#MEMBER#).EVENT_STATE

Type of event last processed by this agent. The possible values are as follows:

Source: WLM_GET_SERVICE_CLASS_AGENTS(NULL,NULL,NULL,#MEMBER#).EVENT_TYPE

Object of the event last processed by this agent. The possible values are as follows:

Source: WLM_GET_SERVICE_CLASS_AGENTS(NULL,NULL,NULL,#MEMBER#).EVENT_OBJECT

The name of the application running at the client.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).APPLICATION_NAME

The number of rows read from the table.

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

The number of rows inserted, updated, or deleted.

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

The number of pages read by agents.

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

The number of times a page was physically written to disk.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES)

The amount of committed memory in use by this memory pool of the session.

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

The total elapsed time spent waiting for locks.

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

The number of locks currently held.

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

The total amount of time spent performing sorts while executing a section.

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

The ID that the user specified when logging in to the operating system.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).EXECUTION_ID

The system authorization id for the connection.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).SYSTEM_AUTH_ID

The hostname of the machine the client application is connecting from.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).CLIENT_HOSTNAME

The time at which the connection was established with the database server.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).MIN(CONNECTION_START_TIME)

The date and time that the unit of work first required database resources.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).UOW_START_TIME

The elapsed time from the unit of work first required database resources.

= CURRENT TIMESTAMP - UOW_START_TIME

Source:

CURRENT TIMESTAMP

UOW_START_TIME = MON_GET_CONNECTION(NULL, #MEMBER#, 1).UOW_START_TIME

The ratio of different type of pages read by agents.

= dReads /(dReads + iReads + tReads) + “d | ” + iReads /(dReads + iReads + tReads) + “i | ” + tReads /(dReads + iReads + tReads) + “t”

Source:

dReads = MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(POOL_DATA_L_READS)

iReads= MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(POOL_INDEX_L_READS)

tReads = MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS)

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

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

The number of times that hash join data exceeded the available sort heap space.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(HASH_JOIN_OVERFLOWS)

The coordinating member for an application.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).MIN(COORD_MEMBER)

The type of the last request completed by the application.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).LAST_REQUEST_TYPE)

The total max estimated cost of a session.

Source: SUM(MON_GET_ACTIVITY(NULL, #MEMBER#).MAX(QUERY_COST_ESTIMATE))

Indicates whether the application is a system application.

If IS_SYSTEM_APPL is 0 then value is NO else YES.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).IS_SYSTEM_APPL

The number of subagents associated with an application.

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

The overall max actual runtime degree of intrapartition parallelism reported at the statement, activity, transaction, or workload level.

Source: SUM(MON_GET_ACTIVITY(NULL, #MEMBER#).MAX(QUERY_ACTUAL_DEGREE))

The total number of rows that have been selected and returned to the application.

Source: SUM(MON_GET_ACTIVITY(NULL, #MEMBER#).SUM(FETCH_COUNT))

The number of dynamic SQL statements that were attempted.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(DYNAMIC_SQL_STMTS)

The number of static SQL statements that were attempted.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(STATIC_SQL_STMTS)

The number of XQuery statements executed.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(XQUERY_STMTS)

The operating system on which the client application is running.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).CLIENT_PLATFORM

The process ID of the client application that made the connection to the database.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).CLIENT_PID

The total amount of time spent performing a connection or switch user authentication or request.

Source: MON_GET_CONNECTION(NULL, #MEMBER#, 1).SUM(TOTAL_CONNECT_REQUEST_TIME + TOTAL_CONNECT_AUTHENTICATION_TIME)

The total amount of CPU time used for the session.

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

The total amount of CPU time used for the statement activity.

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