The MON_GET_SERVICE_SUBCLASS_STATS function returns basic statistics for one or more service subclasses.
None
>>-MON_GET_SERVICE_SUBCLASS_STATS--(--service_superclass_name--,--> >--service_subclass_name--,--member--)-------------------------><
The schema is SYSPROC.
Column name | Data type | Description |
---|---|---|
SERVICE_SUPERCLASS_NAME | VARCHAR(128) | service_superclass_name - Service superclass name monitor element |
SERVICE_SUBCLASS_NAME | VARCHAR(128) | service_subclass_name - Service subclass name monitor element |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
LAST_RESET | TIMESTAMP | last_reset - Last Reset Timestamp monitor element |
COORD_ACT_COMPLETED_TOTAL | BIGINT | coord_act_completed_total - Coordinator activities completed total monitor element |
COORD_ACT_ABORTED_TOTAL | BIGINT | coord_act_aborted_total - Coordinator activities aborted total monitor element |
COORD_ACT_REJECTED_TOTAL | BIGINT | coord_act_rejected_total - Coordinator activities rejected total monitor element |
CONCURRENT_ACT_TOP | INTEGER | concurrent_act_top - Concurrent activity top monitor element |
COORD_ACT_LIFETIME_TOP | BIGINT | coord_act_lifetime_top - Coordinator activity lifetime top monitor element |
COORD_ACT_LIFETIME_AVG | DOUBLE | coord_act_lifetime_avg - Coordinator activity lifetime average monitor element |
COORD_ACT_LIFETIME_STDDEV | DOUBLE | Standard deviation of lifetime for coordinator
activities at nesting level 0 that were associated with this service
subclass since the last reset. If the COLLECT AGGREGATE ACTIVITY DATA
parameter of the service class is set to NONE, the value of the column
is null. Units are milliseconds. This standard deviation is computed from the coordinator activity lifetime histogram and may be inaccurate if the histogram is not correctly sized to fit the data. The value of -1 is returned if any values fall into the last histogram bin. The COORD_ACT_LIFETIME_STDDEV value of a service subclass is unaffected by activities that pass through the service subclass but are remapped to a different subclass before they are completed. |
COORD_ACT_EXEC_TIME_AVG | DOUBLE | coord_act_exec_time_avg - Coordinator activities execution time average monitor element |
COORD_ACT_EXEC_TIME_STDDEV | DOUBLE | Standard deviation of the execution times for
coordinator activities at nesting level 0 that were associated with
this service subclass since the last reset. Units are milliseconds. This standard deviation is computed from the coordinator activity executetime histogram and may be inaccurate if the histogram is not correctly sized to fit the data. The value of -1 is returned if any values fall into the last histogram bin. The execution time standard deviation of a service subclass is unaffected by activities that pass through the subclass but are remapped to a different subclass before they are completed. |
COORD_ACT_QUEUE_TIME_AVG | DOUBLE | coord_act_queue_time_avg - Coordinator activity queue time average monitor element |
COORD_ACT_QUEUE_TIME_STDDEV | DOUBLE | Standard deviation of the queue time for coordinator
activities at nesting level 0 that were associated with this service
subclass since the last reset. If the COLLECT AGGREGATE ACTIVITY DATA
parameter of the service class is set to NONE, the value of the column
is null. Units are milliseconds. This standard deviation is computed from the coordinator activity queuetime histogram and may be inaccurate if the histogram is not correctly sized to fit the data. The value of -1 is returned if any values fall into the last histogram bin. The queue time standard deviation is counted only toward the service subclass in which the activity was queued. |
NUM_REQUESTS_ACTIVE | BIGINT | Number of requests that are running in the service subclass at the time that this table function is running. |
NUM_REQUESTS_TOTAL | BIGINT | Number of requests that finished running in
this service subclass since the last reset. This finished state applies
to any request regardless of its membership in an activity. If the
COLLECT AGGREGATE ACTIVITY DATA parameter of the service class is
set to NONE, the value of the column is null. The NUM_REQUESTS_TOTAL value of a service subclass is unaffected by requests that pass through the service subclass but are not completed in it. |
REQUEST_EXEC_TIME_AVG | DOUBLE | request_exec_time_avg - Request execution time average monitor element |
REQUEST_EXEC_TIME_STDDEV | DOUBLE | Standard deviation of the execution times for
requests that were associated with this service subclass since the
last reset. Units are milliseconds. If the COLLECT AGGREGATE REQUEST
DATA parameter of the service class is set to NONE, the value of this
column is NULL. This standard deviation is computed from the request executetime histogram and may be inaccurate if the histogram is not correctly sized to fit the data. The value of -1 is returned if any values fall into the last histogram bin. The execution time standard deviation of a service subclass is unaffected by requests that pass through the subclass but were not completed in it. |
REQUEST_EXEC_TIME_TOTAL | BIGINT | Sum of the execution times for requests that
were associated with this service subclass since the last reset. Units
are milliseconds. If the COLLECT AGGREGATE REQUEST DATA parameter
of the service class is set to NONE, the value of this column is NULL. This total is computed from the request execution time histogram and may be inaccurate if the histogram is not correctly sized to fit the data. The value of -1 is returned if any values fall into the last histogram bin. The execution time total of a service subclass is unaffected by requests that pass through the subclass but are not completed in it. |
ACT_REMAPPED_IN | BIGINT | act_remapped_in - Activities remapped in monitor element |
ACT_REMAPPED_OUT | BIGINT | act_remapped_out - Activities remapped out monitor element |
CONCURRENT_WLO_TOP | INTEGER | concurrent_wlo_top - Concurrent workload occurrences top monitor element |
UOW_TOTAL_TIME_TOP | BIGINT | uow_total_time_top - UOW total time top monitor element |
UOW_THROUGHPUT | DOUBLE | uow_throughput - Unit of work throughput
monitor element The unit of work throughput since the last reset of the statistics. |
UOW_LIFETIME_AVG | DOUBLE | uow_lifetime_avg - Unit of work lifetime average monitor element |
UOW_COMPLETED_TOTAL | BIGINT | uow_completed_total - Total completed units of work monitor element |
TOTAL_CPU_TIME | BIGINT | total_cpu_time - Total CPU time monitor element |
TOTAL_DISP_RUN_QUEUE_TIME | BIGINT | total_disp_run_queue_time - Total dispatcher run queue time monitor element |
ACT_THROUGHPUT | DOUBLE | act_throughput - Activity throughput monitor element |
CPU_UTILIZATION | DOUBLE | cpu_utilization - CPU utilization monitor element |
APP_ACT_COMPLETED_TOTAL | BIGINT | app_act_completed_total - Total successful external coordinator activities monitor element |
APP_ACT_ABORTED_TOTAL | BIGINT | app_act_aborted_total - Total failed external coordinator activities monitor element |
APP_ACT_REJECTED_TOTAL | BIGINT | app_act_rejected_total - Total rejected external coordinator activities monitor element |
MEMBER | SMALLINT | member - Database member monitor element |
ACTIVE_HASH_GRPBYS_TOP | BIGINT | active_hash_grpbys_top - Active hash Group By operations high watermark |
ACTIVE_HASH_JOINS_TOP | BIGINT | active_hash_joins_top - Active hash joins operations high watermark |
ACTIVE_OLAP_FUNCS_TOP | BIGINT | active_olap_funcs_top - Active OLAP functions operations high watermark |
ACTIVE_PEAS_TOP | BIGINT | active_peas_top - Active partial early aggregations operations high watermark |
ACTIVE_PEDS_TOP | BIGINT | active_peds_top - Active partial early distinct operations high watermark |
ACTIVE_SORT_CONSUMERS_TOP | BIGINT | active_sort_consumers_top - Active sort consumers high watermark |
ACTIVE_SORTS_TOP | BIGINT | active_sorts_top - Active Sorts high watermark |
ACTIVE_COL_VECTOR_CONSUMERS_TOP | BIGINT | active_col_vector_consumers_top - Active columnar_vector consumers high watermark |
SORT_CONSUMER_HEAP_TOP | BIGINT | sort_consumer_heap_top - Individual private sort heap consumer high watermark |
SORT_CONSUMER_SHRHEAP_TOP | BIGINT | sort_consumer_shrheap_top - Individual shared sort heap consumer high watermark |
SORT_HEAP_TOP | BIGINT | sort_heap_top - Sort private heap high watermark |
SORT_SHRHEAP_TOP | BIGINT | sort_shrheap_top - Sort share heap high watermark |
Some statistics are returned only if you set the COLLECT AGGREGATE ACTIVITY DATA and COLLECT AGGREGATE REQUEST DATA parameters for the corresponding service subclass to a value other than NONE.
The MON_GET_SERVICE_SUBCLASS_STATS table function returns one row of data per service subclass and per member. The function does not aggregate data across service classes (on a partition) or across partitions (for one or more service classes). However, you can use SQL queries to aggregate data.
The parameters are assessed in conjunction with each other. If you specify conflicting input parameters, no rows are returned. For example, if you specify a superclass named SUPA and a subclass named SUBB (where SUBB is not a subclass of SUPA), no rows are returned.
SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_NAME,
SUBSTR(CHAR(MEMBER),1,4) AS MEMB,
CAST(COORD_ACT_LIFETIME_AVG / 1000 AS DECIMAL(9,3))
AS AVGLIFETIME,
CAST(COORD_ACT_LIFETIME_STDDEV / 1000 AS DECIMAL(9,3))
AS STDDEVLIFETIME,
SUBSTR(CAST(LAST_RESET AS VARCHAR(30)),1,16) AS LAST_RESET
FROM TABLE(MON_GET_SERVICE_SUBCLASS_STATS(CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)), -2)) AS SCSTATS
ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME, MEMB
SUPERCLASS_NAME SUBCLASS_NAME MEMB ...
------------------- ------------------ ---- ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0 ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1 ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2 ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3 ...
... AVGLIFETIME STDDEVLIFETIME LAST_RESET
... ----------- -------------- ----------------
... 691.242 34.322 2006-07-24-11.44
... 644.740 22.124 2006-07-24-11.44
... 612.431 43.347 2006-07-24-11.44
... 593.451 28.329 2006-07-24-11.44
SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_NAME,
SUBSTR(CHAR(MEMBER),1,4) AS MEMB,
CONCURRENT_ACT_TOP AS ACTTOP,
CONCURRENT_WLO_TOP AS CONNTOP
FROM TABLE(MON_GET_SERVICE_SUBCLASS_STATS(CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)), -2)) AS SCSTATS
ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME, MEMB
SUPERCLASS_NAME SUBCLASS_NAME MEMB ACTTOP CONNTOP
------------------- ------------------ ---- --------- ---------
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0 10 7
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1 0 0
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2 0 0
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3 0 0
By checking the average execution times and numbers of activities in the output of this table function, you can get a good high-level view of the load on each member for a specific database. Any significant variations in the high-level gauges returned by this table function might indicate a change in the load on the system.
SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_NAME,
ACT_REMAPPED_IN AS MAPPED_IN,
ACT_REMAPPED_OUT AS MAPPED_OUT
FROM TABLE(MON_GET_SERVICE_SUBCLASS_STATS(CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)), -2)) AS SCSTATS
ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME
SUPERCLASS_NAME SUBCLASS_NAME MAPPED_IN MAPPED_OUT
------------------- ------------------ --------- ----------
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0 0
SUPERCLASS1 SYSDEFAULTSUBCLASS 0 0
SUPERCLASS1 SUBCLASS1 0 7
SUPERCLASS1 SUBCLASS2 7 0