Session 4: Using Workload Performance Analysis
Go Up to Performance Analyst for IBM DB2 for Linux, Unix and Windows Tutorial
Key ratios give you a global perspective on database activity. Bottleneck analysis gives you insight into things that are holding up user activity and throughput. But another technique is necessary if a database professional is to really get a handle on what’s occurring inside a badly performing database.
Workload analysis involves the investigation of two critical areas of a database’s performance:
- Application resource consumption and activity
- SQL execution
Without looking at these two key performance categories, you can miss a lot of what could be responsible for perceived performance problems. When performance on a database takes a sudden nosedive, it is not uncommon to find one or two sessions that are causing the bulk of the workload. Finding these processes can be easily accomplished by viewing session metadata coupled with resource consumption and statistical execution statistics.
Pinpointing Top Resource Consumers
Performance Analyst identifies top resource consumers in a couple of different ways. On the main home page, Performance Analyst highlights the top resource consumers across lock usage, CPU usage, and I/O usage. The percentage used across all statistical categories are displayed so a DBA can immediately pinpoint a session that is using all or most of a particular resource. The top resource sessions are also displayed on the memory, I/O, and other home pages as well with a listing for the top sessions for that category (for example, the top IO consumers appear on the I/O home page, etc.).
Getting Workload Details Using Performance Analyst for DB2
Performance Analyst offers several details on top resource applications in addition to the summaries provided on each home page. A detailed and flexible top applications view lets you view a summary of all the major resource applications on the database as well as every critical detail regarding each application connected to the database.
Further drill-downs are available that highlights specific details regarding each application’s activities and submitted SQL. To see detailed information regarding an application identified in one of Performance Analyst’s Workload Analysis sections, do the following:
- In a Workload Analysis display, position the pointer over one of the processes.
- Double-click the process or right-click, and then select Details.
If you want like to see all key workload analysis metadata for sessions, do the following:
- On the toolbar, click the Top Sessions button.
- Or right-click anywhere in Performance Analyst, point to Users, and then click Top Sessions.