Narrowing your application performance investigation

When you encounter a general performance complaint or observe a response time problem, you can try to isolate the problem to a particular application or program, before you attempt system-wide tuning actions.

About this task

When you compare the response times for application programs, you can focus your comparison on Class 2 CPU time, Class 3 response time, and not-accounted times.

When your goal is general performance improvements, a good approach is to focus on the applications that use the most resources first. For example, you might focus your investigation on the top 10 plans or packages that meet the following criteria:

  • Run most frequently.
  • Use the most DB2® (class 2) CPU time.
  • Have high elapsed times.

If you have performance history records, you can identify the transactions that show the largest increases.

Procedure

To narrow the scope of your investigation for general response time complaints:

  1. Identify the plan or package that has the longest response times.
  2. For plans that can potentially allocate many different static SQL packages, identify the package that has the longest response times.
    1. Use package-level accounting reports to determine which package has a long elapsed time.
    2. Use the class 7 CPU time for packages to determine which package has the largest CPU time or the greatest increase in CPU time.
    3. Start of changeEnable class 10 to get detailed buffer manager, lock manager, and SQL statistics at the package level.End of change
    4. Start of changeUse class 8 for package elapsed time issues.End of change
  3. Start of changeFor dynamic SQL, use the following approaches to identify the SQL statement that use the most resources:
    • Use DB2 trace:
      • Check the global and local cache usage statistic. These statistics are externalized by IFCID 0002.
      • Use IFCID 0316 to find timestamp values, bind options, cumulative execution statistics and wait times.
      • Use IFCID 0317 to find the complete statement text and attribute string.
      • Start of changeActivate IFCID 0318 to enable the capture IFCID 0316 and 0317 data.End of change
    • Capture and analyze EXPLAIN information for the dynamic statement cache. You can issue EXPLAIN statements to capture access path information in the various EXPLAIN tables. You can issue the following statement to find access path information for each statement in the dynamic statement cache:
      EXPLAIN STMTCACHE ALL
      When you issue this statement, DB2 writes a single row for each statement in the dynamic statement cache to the DSN_STATEMENT_CACHE_TABLE only. No data is written to other EXPLAIN tables. You can use the STMT_ID and QUERYNO columns to correlate the DSN_STATEMENT_CACHE_TABLE rows with the other EXPLAIN tables.
    End of change
  4. Use the OMEGAMON® SQL activity report to analyze specific SQL statements. You can also use OMEGAMON to analyze specific SQL statements, including the currently running SQL statement.
  5. If you have a history of the performance of the affected application, compare current EXPLAIN output to previous access paths and costs.