Time-spent monitor element hierarchy

The information in many time-spent monitor elements is rolled up in more general monitor elements.

For example, individual wait time elements, such as that for the time spent waiting to receive the information in the next buffer from a table queue (fcm_tq_recv_wait_time) and time spent waiting for an FCM reply message (fcm_message_recv_wait_time) are both included in overall fcm_recv_wait_time element. The hierarchical organization of time-spent monitor element makes it possible to choose the element with the most appropriate level of specificity for your needs.

Dimensions and perspectives for viewing time-spent monitor elements

There are different ways that you can look at the hierarchies of time-spent monitor elements. One way is to look at them from the viewpoint of the system as a whole; you can also look at them in the context of specific activities within the system.

The system-level view or system dimension includes elements that you can use to see what the system is doing as a whole. You can also use elements in the system dimension to view time-spent information for specific workloads.

The activity-level view or activity dimension includes elements that you can use to see which specific activities the system is spending time on, such as the execution of SQL statements. All monitor elements in the activity dimension are included in the higher-level system dimension.

Within each of these two dimensions are two different perspectives that you can use to look at time-spent monitor elements:
  • Component processing times as compared to wait times
  • Component elapsed times as compared to component processing times
In the first perspective, the values for wait-time elements are independent of and complementary to the values for component processing time elements. If you add the sum of all of the reported wait times to the sum of all component processing times, the resulting value is very close to the value that is reported by the total_rqst_time monitor element. Any minor difference between the two values is the result of a small amount of miscellaneous component processing time that is not tracked by any monitor element.

In the second perspective, component elapsed times are a superset of component processing times. For example, for a logical component of the database such as that component that performs commits, the total amount of commit processing time, as reported by the total_commit_proc_time monitor element, is included in the overall elapsed time for the commit, as reported by the total_commit_time monitor element. The difference between the total elapsed time and the total processing time is made up of miscellaneous wait or processing times that are not individually tracked by the component elapsed time monitor element.

Viewing component elapsed times relative to wait times is not meaningful, because component elapsed times already include wait times that are incurred as part of the elapsed time in that part of the system. If you created a pie chart consisting of both component elapsed times and wait times, it would not be an accurate representation of the time that is spent in your system because you would be double-counting the various types of wait times.

The sections that follow describe the various dimensions (system and activity) and perspectives (component processing, wait times and component elapsed, and component processing times) from which you can view the information in time-spent monitor elements.

Tip: Not all information from time-spent elements is reported through all interfaces. For example, the client_idle_wait_time monitor element is applicable only to system-level interfaces such as the MON_GET_SERVICE_SUBCLASS table function. See the reference topic for each monitor element for a list of the interfaces that report the element.

System dimension: Wait and component processing time-spent monitor elements

The following list shows an overall view of how the monitor elements for wait time and component processing time relate to one another, as viewed from the system dimension.

The values for indented monitor elements are included in the element that precedes those elements in the next-highest level of the hierarchy.

1These FCM-related wait times do not yield meaningful information when aggregated across members. For more information, see Wait times for FCM communications for more information.

2When reported by the MON_GET_DATABASE interface or the MON_GET_DATABASE_DETAILS interface, pool_read_time includes asynchronous read time and pool_write_time includes asynchronous write time.

3These processing time elements include miscellaneous processing and wait times that are not specifically related to this component. For example, the time that the total_section_proc_time element reports includes the time that the total_section_proc_sort_time element reports. The total_section_sort_proc_time element reports the time that is spent performing activities such as table and index scans or joins.

4 These monitor elements include a small number of miscellaneous types of time spent (processing and wait times) that are not currently monitored.

System dimension: Component time-spent monitor elements

The following list shows a detailed view of the monitor elements for the time that is spent in various component areas. Each component time is represented by two different monitor elements:
  • One that reports the total amount of processing time in a component or stage of processing
  • One that reports the overall elapsed time that is spent in the component. This overall time includes the processing time for the component and any other processing or wait times that might be involved.

The values for indented monitor elements are included in the element that precedes those elements in the next-highest level of the hierarchy.

1These monitor elements include one or more different types of wait times.

2These monitor elements include a small number of miscellaneous types of time spent (processing and wait times) that are not currently monitored.

Activity dimension: Wait and component processing time-spent monitor elements

The following list shows the monitor elements that you can view for activities from the perspective of wait times as compared to component processing times.

The values for indented monitor elements are included in the element that precedes those elements in the next-highest level of the hierarchy.

1This monitor element does not include any wait time that is incurred by nested (child) activities that the statement executes.

2These FCM-related wait times do not yield meaningful information when aggregated across members. For more information, see Wait times for FCM communications.

3Includes miscellaneous processing times that are not specifically related to this component.

4Includes a small number of miscellaneous types of time spent (processing and wait times) that are not currently monitored. In addition, this time includes any processing and wait times that child activities incur.

Activity dimension: Component elapsed and component processing times

The following list shows the monitor elements that you can view for activities from the perspective of component elapsed times, which include component processing times.

The values for indented monitor elements are included in the element that precedes those elements in the next-highest level of the hierarchy.

1These monitor elements include one or more different types of wait times.

2These processing time elements include miscellaneous processing and wait times that are not specifically related to this component.