Session 2: Using Ratio-based Performance

From DBArtisan
Jump to: navigation, search

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

Ratio-based analysis has been around for many years, and used to be the only technique database administrators used when they were called on to diagnose the cause of a database slowdown. Performance ratios are very good roll-up mechanisms for busy DBAs, making possible the analysis-at-a-glance approach, however ratios must be computed correctly for them to be meaningful. Key ratios in Performance Analyst are calculated using delta statistics, which is superior to computing ratios with cumulative values. Delta statistics, which are values derived by using the before and after measures in each statistical refresh done in Performance Analyst, lets you see changes in performance metrics that would otherwise be invisible if cumulative statistics were used. Performance Analyst makes it easy to see the ratios that are computed with delta statistics vs. cumulative. A triangle symbol denotes a delta-based statistic, while square symbols represent a cumulative-based measure.

Ratio-based analysis is available for all performance categories (memory, etc.) where ratios can assist in determining the performance level of a database. For example, if you would like to see memory-specific ratios, on the Performance Analyst toolbar click Memory. Performance Analyst displays the Memory home page that showcases all important memory-related ratios.

Obtaining Detail Information for a Ratio Using Performance Analyst for DB2

A ratio gives you a global picture for a particular performance measure. However, you often need more information regarding a statistical measure so you can make an informed decision. Performance Analyst has easy-to-use drill down features that make obtaining detailed information for various ratios a breeze.

For example, to view more information for the Buffer Cache Hit Ratio statistic, do the following:

  1. On either the main Home page or the I/O home page, locate the Buffer Pool Hit Ratio statistic.
  2. Position the pointer over the statistic.
    The pointer changes to a hand symbol.
  3. Double-click the left mouse button.
    Performance Analyst opens the Buffer Cache tab of the memory drill down view.
  4. Or right-click the statistic, select Details or Navigate to… > I/O > Buffer Pool.

Once either of these actions has been taken, you will be taken to a drill-down view that provides more information on the DB2 buffer pools.

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