Session 3: Using Bottleneck/wait-based Performance Analysis in Performance Analyst for DB2

Go Up to Performance Analyst for IBM DB2 for Linux, Unix and Windows Tutorial

When a DB2 database is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things, or it can be an indicator that a database bottleneck exists. Bottleneck analysis is a valid method of measuring performance because it helps you track where a database and user sessions have been spending their time. If lock contentions or heavy table-scan activity has been dragging a database’s performance down, you can use bottleneck analysis to confirm the actual root cause.

Performance Analyst uses several key areas of DB2 metrics to form its bottleneck analysis. These areas are usually the main sources of performance drag on the databases.

Performance Analyst prominently displays bottleneck analysis on every performance home page. On the main home page, you will see a summary of all key bottlenecks threatening the database. You can also see specific bottlenecks for memory, I/O, space, objects, and users by navigating to each home page and viewing the Bottleneck Analysis section for that home page.

For example, to see bottlenecks that specifically relate to user applications, follow these steps:

1. Click the Users Performance Analyst toolbar button
2. Locate the Bottleneck Analysis section at the lower-left portion of the screen

The Users Bottleneck Analysis section shows the top bottlenecked applications, concurrency issues, and bottleneck activity count.

Obtaining Detail on System Bottlenecks

You can easily obtain more information regarding global bottlenecks that are plaguing a database by drilling down into a particular bottleneck metric. For example, if you would like to see more data regarding the current top concurrency bottlenecks, do the following:

1. Open the Performance Analyst main Home page.
2. Double-click the Concurrency area in the Bottlenecks section to be taken to the Lock Waits drill-down section.

   Performance Analyst opens the System Waits view.

3. Or right-click anywhere in Performance Analyst, point to Users, and then click Lock Waits.

   Performance Analyst opens the System Waits view.

The Lock Waits view displays critical details regarding all the lock wait events that DB2 has recorded. You can easily see what applications are waiting on locks and the average lock wait times for all applications.

Note: If you would like to see an expanded view of the Lock Waits grid shown on the top of the Lock Waits view, click the Expand button located at the top right corner of the section. All section views in Performance Analyst can be maximized/minimized using this control.

Viewing Detail for Application Bottlenecks

Not only can you obtain detail information for system bottlenecks, but you can also use Performance Analyst to get drill-down information regarding the locks held by and waited on by applications.

To see more information regarding application locks, do the following:

1. Open the Performance Analyst main Home page.
2. Double-click the Concurrency Bottlenecks area to be taken to the Lock Waits drill-down view.

   Performance Analyst opens the Lock Waits tab.

3. Or right-click anywhere in Performance Analyst, point to Users, and then click Locks.

Once on the Locks drill-down view, you can view the details for locks held and waited on by an application details by following these steps:

1. Examine the Application Details grid
2. Select an application that appears to be experiencing lock waits problem

You will see details on the locks held by, and the locks waited on, by the selected application. You can get further details on the application that is holding the locks by navigating to the Attributes drill-down and selecting the appropriate Application ID.

Proceed to Session 4: Using Workload Performance Analysis