Analyzing elapsed times

You should start the analysis of your DB2 subsystem by determining if you are meeting service-level objectives. The DB2 Application Response Times, Detail report (see Figure 68) shows the average, maximum, and minimum elapsed times for each application.

                    DB2 Application Response Times, Detail
                        System: 'MVS1'  DB2 ID: 'DB2A'
                     Date: '1999-11-19'  to  '2000-03-09'


                            Elapsed    Elapsed   Elapsed
Application                 seconds    seconds   seconds       Commit   Abort
   name      Profile          avg        max       min         count    count
------------ ----------- ---------- ----------  -------- ------------  ------
P.R.         QUERY             9.39      23.27      0.16        13218      18
QMF          QUERY             7.83      25.20      0.13         7876       8
SPUFI RR     QUERY             4.60      21.14      0.31           73       5


                Tivoli Decision Support for z/OS Report: DB201

If an application is not receiving the appropriate service level, you must determine why the elapsed time is high. The DB2 Transaction Statistics, Detail report (see Figure 69) shows the response-time components for each DB2 plan.

                                               DB2 Transaction Statistics, Detail
                                                 System: 'MVS1'  DB2 ID: 'DB2A'
                                              Date: '2000-01-11'  to  '2000-03-09'


                                                  DB2               Lock    Service  Archive  Archive
           Elapsed    CPU    DB2 CPU  CPU(SRB)  CPU(SRB)  IO wait   latch    wait    logwait  logread   Drain
           seconds  seconds  seconds  seconds   seconds   seconds  seconds  seconds  seconds  seconds  seconds     Commit   Abort
DB2 plan     avg      avg      avg      avg       avg       avg      avg      avg      avg      avg      avg       count    count
------------------ -------- --------  --------  -------- -------- -------- -------- -------- -------- --------  ---------  ------
DB2PM211      0.88    0.006    0.004     0.000     0.000    0.000    0.000    0.000    0.000    0.000    0.000      50983       0
DRLPLAN      21.68    7.332    4.019     0.009     0.000    0.017    0.019    0.000    0.000    0.000    0.000       7098     123
QMF310       12.90    0.680    0.165     0.004     0.000    0.001    0.001    0.000    0.000    0.000    0.000       4227      62
DSNUTIL       0.46    0.028    0.000     0.003     0.000    0.000    0.000    0.000    0.000    0.000    0.000       1391      11
DISTSERV   2128.04    0.647    0.004     1.164     0.002    0.001    0.000    0.000    0.000    0.000    0.000         95       0
DSNESPRR      6.15    0.115    0.002     0.002     0.000    0.000    0.000    0.000    0.000    0.000    0.000         55       4
                                                                                                                =========  ======
                                                                                                                    63849     200

                                     Tivoli Decision Support for z/OS Report: DB204

To determine what is causing delays for the plan to which the application belongs, examine these columns in the report:

Elapsed seconds avg

This is the average class 1 elapsed time. Compare this with the CICS or IMS transit times. You can determine the CICS and IMS transaction elapsed times from reports created by the CICS Performance feature and the IMS Performance feature of Tivoli Decision Support for z/OS.

Differences between these CICS or IMS times, and the DB2 accounting times arise mainly because the DB2 times do not include:

• Time before the first SQL call
• DB2 create thread
• DB2 terminate thread

Differences can also arise due to thread reuse in CICS or IMS, or through multiple commits in CICS. If the class 1 elapsed time is significantly less than the CICS or IMS time, check reports that show CICS transaction performance or application response times, or IMS transaction performance. For a description of the CICS or IMS reports available through Tivoli Decision Support for z/OS, refer to the CICS Performance Feature Guide and Reference or the IMS Performance Feature Guide and Reference.

Elapsed time can occur:

• In DB2 in signon, create, or terminate thread
• Outside DB2 in CICS or IMS processing

For CICS, the transaction could have been waiting outside DB2 for a thread.

DB2 CPU seconds avg

This is the average processor time spent in DB2. The problem might be due to DB2 or IMS Resource Lock Manager (IRLM) traces, or to a change in access paths. When using CPU time for analyzing DB2 performance, use only TCB time.

I/O wait seconds avg

This is the average I/O wait time during the reporting interval. This value includes:

Synchronous I/O suspension time

This is the total application wait time for synchronous I/Os. Check the total number of synchronous read I/Os reported.

If the number of read I/Os is higher than expected, check for:

• A change in the access path to data. Here, the number of GETPAGE requests is higher than expected.

  A GETPAGE is a logical read of one 4K page. If the page is in the buffer pool, it is retrieved from the buffer pool, so DASD I/O is unnecessary. It is always desirable to have required data in the buffer pools.

• Changes in the application.
• A system-wide problem in the database buffer pool.
• A system-wide problem in the EDM pool.

If I/O time is greater than expected, and not caused by more read I/Os, check for:

• Synchronous write I/Os.
• I/O contention (usually each synchronous read I/O typically takes from 15 to 25 milliseconds, depending on the DASD device).

Asynchronous read suspension time

This is the accumulated wait time for read I/O done under a thread other than the current thread:

• Sequential prefetch
• List prefetch
• Sequential detection
• Synchronous read I/O performed by a thread other than the one being reported

Asynchronous write suspension time

This is the accumulated wait time for write I/O done under a different thread:

• Asynchronous write I/O
• Synchronous write I/O performed by a thread other than the one being reported

Lock latch seconds avg

This is the average time spent in lock/latch management within DB2. This shows contention for DB2 resources. If contention is high, check the DB2 Transaction Statistics, Detail report (see Figure 69) and the DB2 Database Service Statistics, Detail report (see Figure 73).

Service wait seconds avg

This is the average wait time due to synchronous unit switching, which lets DB2 switch from one execution unit to another. For example, waits for opening data sets are included.

Archive log wait seconds avg

This is the average time the thread was suspended due to ARCHIVE LOG MODE(QUIESCE) command processing.

To have correct average times, accounting classes 2 and 3 must be active for the entire accounting period. If these classes are started and stopped within an application run, DB2 records only the time when the classes are active, thereby creating a discrepancy with the real DB2 times.

You can also look at the distribution of response-time components by application profiles. The DB2 Application Profiles, Trend report (see Figure 70) shows the response-time components for a specific profile as a percentage of the total elapsed time (class 1) over a specific time period:

Figure 70. DB2 Application Profiles, Trend report

You can improve the response time and throughput of your DB2 applications by reducing:

• The number of I/O operations
• The time needed to perform I/O operations
• The amount of processor resources consumed

The following sections describe ways to accomplish these goals.

[ Top of Page | Previous Page | Next Page | Contents ]