Use request monitor elements to monitor the
database system,
specifically the volume of work and the effort expended by the data
server to process application requests.
A request is a
directive to a database agent to perform
some work that expends database resources. Sources of the request
can include:
- A directive issued directly by an external application,
such as
an OPEN or EXECUTE directive. These are referred to as application
requests.
- A directive issued by a coordinator agent to a subagent
at the
same or a different database member.
- A directive issued by
an agent at a different database member.
Request monitor
elements measure the volume of work or
effort expended by the database server to process different types
of requests, including overall system processing, requests related
to a specific type of processing, and requests related to a specific
data server environment.
Some representative monitor elements
for measuring overall system processing information are the following:
- The rqsts_completed_total monitor
element measures the number of completed by the system.
- The total_rqst_time monitor
element measures the time spent by requests in the data server, including
wait time and processing time
- The total_wait_time monitor
element measures
the overall wait time.
- The total_cpu_time monitor
element measures
the CPU usage time.
Some representative monitor elements
for measuring client-server
processing information are the following:
- The client_idle_wait_time monitor
element
measures the time spent waiting for the next request from an open
connection.
- The tcpip_recv_volume monitor
element measures
the volume of data received by the data server from clients over TCP/IP.
Some representative monitor elements for measuring common
data server processing operations are the following:
- pool_data_l_reads is
one of the monitor elements
providing information about buffer pool resource usage.
- pool_read_time is
one of the monitor elements
providing information about I/O processing.
- lock_wait_time is
one of the monitor elements
providing information about locks and locking.
- total_section_sorts is
one of the monitor
elements providing information about sorts.
Some representative monitor elements for monitoring processing
relevant to selected types of data server environments are the following:
- fcm_recv_wait_time is one of
the monitor elements measuring fast communications manager (FCM) processing.
- wlm_queue_time_total is one of the monitor
elements measuring workload management control actions.
Accessing request metrics using table functions
You
can use the following table functions to access the request metrics:
- MON_GET_SERVICE_SUBCLASS and MON_GET_SERVICE_SUBCLASS_DETAILS
- MON_GET_WORKLOAD and MON_GET_WORKLOAD_DETAILS
- MON_GET_CONNECTION
and MON_GET_CONNECTION_DETAILS
- MON_GET_UNIT_OF_WORK and MON_GET_UNIT_OF_WORK_DETAILS
Each table function in this set of monitor table functions has
two forms, one of which has a name ending with "DETAILS." The function
that does not end with "DETAILS" provides an SQL relational interface
that returns the most commonly needed data. The other function provides
XML-based access to the monitor data and returns a more comprehensive
set of data.
This set of table functions enables you to focus
on request metrics at a particular level of aggregation. You can choose
the table function that enables you to focus on subset (or aggregation)
of the system workload you are interested in a given situation. All
of these table functions include a common set of request metric monitor
elements. Each table function may return a few additional details
not common with all the table functions.
In a database with
no user-defined workloads or service classes, all of the user work
performed by the database manager occurs in the default user workload
and user service class. The table functions that return data for each
service class (or workload) return data for a single service class
(or workload) that represents the processing for the user workload
for the entire database.
In a database with user-defined workloads
and service classes, table functions that return data for each service
class (or workload) enable you to compare processing per service class
(or workload). Using SQL, you can sum the values across service classes
(or workloads) to obtain the value of a monitor element that represents
the processing for the user workload for the entire database.
Accessing request metrics using event monitors
Request
metrics are reported by the following event monitors:
- Statistics
event monitor - Request metrics are one of several
types of information reported by this event monitor.
- UoW event
monitor - This event monitor reports similar or identical
fields as the MON_GET_UNIT_OF_WORK table function