Monitoring activities using table functions

The activity monitoring perspective focuses on the subset of data server processing related to executing activities. In the context of SQL statements, the term activity refers to the execution of the section for a SQL statement.

Monitor elements for this perspective, referred to as activity monitor elements, are a subset of the request monitor elements. Activity monitor elements measure aspects of work done for statement section execution. Activity monitoring includes other information such as SQL statement text for the activity.

For activities in progress, activity metrics are accumulated in memory. For activities that are SQL statements, activity metrics are also accumulated in the package cache. In the package cache activity metrics are aggregated over all executions of each SQL statement section.

Use the following table functions to access current data for activities:
  • MON_GET_ACTIVITY
  • MON_GET_ACTIVITY_DETAILS
  • MON_GET_PKG_CACHE_STMT
  • MON_GET_PKG_CACHE_STMT_DETAILS
Activity monitoring information is collected by default for a new database. You can change default settings using one or both of the following settings:
  • The mon_act_metrics database configuration parameter specifies the minimum level of collection in all workloads.
  • The COLLECT ACTIVITY METRICS clause of the CREATE/ALTER WORKLOAD statement specifies the level of collection for a given workload over the minimum level of collection set for all workloads.
The possible values for each setting are the following:
None
No activity monitor elements are collected
Base
All activity monitor elements are collected
For example, to collect activity monitor elements for only selected workloads, do the following:
  1. Set the mon_act_metrics database configuration parameter to NONE.
  2. Set the COLLECT ACTIVITY METRICS clause of the CREATE/ALTER WORKLOAD statement to BASE. By default, the values for other workloads is NONE.