4 Performance
4.2 Performance: Time Spent Analysis
Note
The Time Spent Analysis screen is only available if the monitored database is at least DB2 V9.7.
End of the note.
You can use the Time Spent Analysis screen as the starting point for performance tuning and to identify time-based problems of your database. The Time Spent Analysis screen provides an aggregated and timeline-based view of the current or past performance situation of your database. The advantage of time spent monitoring is that you do not only have to rely on standard database key performance indicators (such as the buffer pool hit-ratio) but you can also identify how much time is really spent on different kinds of database operations.
You can use time spent monitoring to answer questions such as the following:
How do changes in system performance relate to changes in the total time spent in various DB2 components or wait situations?
Where is most of the time spent in DB2 so that these areas can be tuned?
What are the most active service classes and members on the database system, and are there any outliers?
To access the Time Spent Analysis screen, call the DBA Cockpit and choose Performance
Time Spent Analysis on the Database tab page of the DBA Cockpit. The Time Spent Analysis screen is divided into the following areas:
Selection area
Chart view Time Spent in DB2 by <drill-down criteria>
Specifying Filter and Drill-Down Criteria
In the Selection area, you can specify filter and drill-down criteria for the data to be displayed as follows:
Field Description
Source
From the dropdown list, you can choose the source of the data to be analyzed:
Periodic Data Collection
If you choose this option, the data that has already been collected by the data collector WLM Statistics is used for the time spent analysis.
For more information, see Data Collection Framework: Collector
Configuration [page 227].
Note
The granularity of the data analysis can only be as high as specified in the collection interval.
Field Description
End of the note.
Ad-hoc Data
If you choose this option, real-time monitoring data is collected. This means that specific workload situations can be analyzed in real-time. To set a time interval for the data collection and the charts to be refreshed, choose an interval from the Refresh Interval dropdown list. To start the ad-hoc data collection, choose the Resume pushbutton. If you want to change the drill-down or filter criteria, you must first stop the current Ad hoc data collection by choosing the Pause pushbutton.
Time Frame
You specify the time interval and time frame for which you want data to be analyzed.
Type of Display
Aggregate
If you choose the Aggregate option from the dropdown list, a pie chart is displayed according to your selected filter and drill-down criteria.
Timeline
If you choose the Timeline option from the dropdown list, the monitoring data is displayed in a histogram according to your filter and drill-down criteria. You can condense the granularity of the time interval from As
Collected to Hour, Day, Week, or Month.
Members
Note
You can only filter by members if you are using a distributed database system. End of the note.
From the dropdown list, you can choose the database member on which you want to apply the specific workload filter. You can choose all or single members. If you want to combine multiple members into a group (for example, by physical host), you can use the Advanced Selection option.
Service Class(es)
From the dropdown list, you can choose the service classes that have been set up on the system. You can choose all or single service classes.
If you want to combine multiple service classes into a group, choose Advanced
Metric(s)
From the dropdown list, you can choose between the following time-related WLM metrics:
All
All known time-related metrics are included.
All (Wait Times)
All wait time metrics are selected (W)
All (Processing Times)
All processing times without wait times are selected (P).
Single metrics, for example, (P) Commit Processing Time or (W)
Buffer Pool Read Time
For more information, see Monitoring metrics for DB2 workload
manager in the IBM Information Center. Advanced Selection
When you choose this option, all metrics including their parent metrics are displayed. You can now select those metrics that are relevant for your analysis. For example, you select all REORG and RUNSTATS processing times to drill down to specific members or member groups. If you choose the Choose Level of Detail link, you can specify one of the following options in the Metrics Hierarchy dialog box:
Total Request Time Only
Aggregates all metrics
Wait vs. Processing vs. Unknown Times
Aggregates metric groups
Detailed Wait and Processing Time
Includes only detailed wait and processing times
Full Details
Includes all time metrics (default) Note
The metrics are mutually exclusive and are independent of each other. Since all metric metadata is retrieved dynamically from the database, the availability of specific metrics as filter criteria depends on the database release.
Drill-Down
You can drill down into the display by choosing one of the following options from the dropdown list:
Members
All results are distributed across the different members based on the specified filter criteria.
Note
You can only drill down by members if you are using a distributed
database system.
End of the note.
Metrics
All results are displayed according to the different time-based monitoring metric occurrences and their relative values as a percentage of the total request time.
Service Classes
Based on the specified filter criteria, all results are distributed across the service classes according to their occurrence and their relative percentage to the total request time.
To avoid the chart becoming overloaded and unclear, the Reduce Display to
Most Significant checkbox is by default selected. All metrics, members, and
service classes with insignificantly small values are cumulated to give the value of Other Known Times.
Note
Make sure that drill-down and filter criteria are not the same, that is, do not use, for example, non-aggregated single metrics combined with the option to drill down by metrics.
End of the note.
The Chart View
After applying your filter and drill-down criteria in the Selection area, the relevant time spent analysis chart is generated.
Depending on the specified display type, either a pie chart or timeline histogram is displayed: Pie chart
The pie chart is the initial view of any time spent analysis.
All occurrences of metrics or service classes are displayed as well as their relative values as a percentage of the total time consumption of the monitored system. This view shows the aggregated results of the drill-down dimension for the specified time frame. The results are displayed clockwise in descending order.
Timeline histogram
The timeline-based chart lets you analyze changes in workload performance for a given time frame.
This chart type displays the cumulated occurrences of selected time-based metrics, members, or service classes into a histogram that consists of bars that are aligned to the timeline. The histogram bars are divided according to the time granularity you have specified in the Selection area.
Displaying Details and Analyzing the Charts
The analysis of a single pie chart or histogram item can vary due to the drill-down criteria you have specified (that is, metric vs. member or service class). To analyze a single item of a chart, click the item or caption. The relevant details appear in the <metric> Summary area displaying all values and occurrences for the selected time frame or time interval as follows:
Percentage of the total time related to the occurrence in the specified time interval or time frame
Processing times, nominal and worst values per service class or member Total occurrences for the total time frame or the selected time interval Line graphs of the absolute and average values
In the See Also area, you can use links such as the following to directly navigate into areas of the DBA Cockpit that might help you analyze the problem:
Configuration Changes Workload Statistics Threshold Violations
Previous filter and drill-down criteria are kept if possible.
Customizing the Charts
You can change the chart type from Stacked Columns to Stacked Area or Lines by choosing the Chart Type menu button.
To specify whether you want the data to be displayed in absolute or percentage values, you can use the Values menu button. If you choose the Percentage option, the values are displayed in relation to the total request time per time frame or per time interval.
By choosing the Chart Size menu button, you can specify the width and height of the chart to be displayed. Optionally, you can open a detailed table view by choosing the Toggle Table
Example Scenarios
Example 1
To analyze and identify I/O-related workload on the database, perform the following steps: 1. Set the Metric(s) filter to Advanced Selection and select Direct Read Time, Direct
Write Time, BP Read Time, and BP Write Time.
2. As Type of Display, choose Timeline.
3. As Drill-Down option, choose Service Classes.
4. After applying your selection, you can identify time consumption peaks in the displayed charts. To identify the responsible workloads for those peak values, pay special attention to the nominal total time per service class versus the worst service class in the Summary area.
Example 2
To identify the reason for a general system slowdown, perform the following steps: 1. As Type of Display, choose Timeline, and as Drill-Down option, choose Metrics. 2. In the Members and Service Class(es) fields, choose All and apply your selection. 3. Identify the most significant time metrics in the displayed chart.
4. In the detailed Summary area of those metrics, check for outlier members or service classes to restrict the scope to a smaller group of members or service classes. 5. For a more detailed analysis of the system slowdown, keep your selection of
members and service classes and navigate into other areas of the DBA Cockpit such as the following:
o Performance Snapshots SQL Cache
Search for statements with high values for the identified metrics. o Diagnostics Lock-Wait Events
Analyze lock-wait situations or a switch in data collection. o Performance Workload Statistics
Analyze statistical metrics for the identified members or service classes. o Configuration Workload Management Workloads and Service Classes
Check the priority settings of the involved workloads.
More Information
Monitoring metrics for DB2 workload manager in the IBM Information Center at:
https://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admi n.wlm.doc/doc/c0055265.html