Typical review questions

You can use specific review questions to help guide your review of performance data.

Use the following questions as a basis for your own checklist. They are not limited strictly to performance items, but your historical data can provide most of their answers. If the performance data is for modeled workloads or changed workloads, the first question to ask for each category is, "What changed?"

How often was each transaction and SQL statement used?

  1. Considering variations in the workload mix over time, are the monitoring times appropriate?
  2. Should monitoring be done more frequently during the day, week, or month to verify this?
  3. How many SELECT, INSERT, UPDATE, DELETE, PREPARE, DESCRIBE, DESCRIBE TABLE, PREPARE, OPEN, FETCH, and CLOSE statements are issued per second and per commit?
  4. How many IRLM and buffer pool requests are issued per second and per commit?

How were processor and I/O resources used?

  1. Has usage increased for functions that run at a higher priority than Db2 tasks? Examine CICS®, IMS, z/OS®, JES, TCP/IP, VTAM®, WebSphere® Application Server, IBM® MQ, other subsystems, and key applications.
  2. Is the report of processor usage consistent with previous observations?
  3. Are scheduled batch jobs able to run successfully?
  4. Do any incident reports show that the first invocation of a function takes much longer than later ones? This increased time can happen when programs have to open data sets.
  5. What is the CPU time and where is it accumulated? Separate CPU time into accounting TCB and SRB time, and distinguish non-nested, stored procedure, user-defined function, and trigger CPU times. Note the times for Db2 address spaces, DBM1, MSTR, IRLM, and DDF.
  6. In a data sharing environment, how are the coupling facility (CF) lock, group buffer pool, and SCA structures performing? What is the peak CF CPU utilization?
  7. Are unnecessary Db2 traces on?
  8. Are online monitors performing unnecessary or excessive tracing?

How much real storage was used, and how effective is storage?

  1. Is the paging rate increasing? Adequate real storage is very important for Db2 performance.
  2. What are the hit ratios for the following types of storage:
    • Buffer pools
    • EDM statement cache
    • EDM DBD cache
    • EDM skeleton pool

To what degree was disk used?

Is the number of I/O requests increasing? Db2 records both physical and logical requests. The number of physical I/Os depend on the configuration of indexes, the data records per control interval, and the buffer allocations.

To what extent were Db2 log resources used?

  1. Is the log subject to undue contention from other data sets?
    Recommendation: Do not put a recoverable (updated) resource and a log under the same RAID controller. If that controller fails, you lose both the resource and the log, and you are unable to perform forward recovery.
  2. What is the I/O rate for requests and physical blocks on the log?
  3. What is the logging rate for one log in MB per second?
  4. How fast are the disk units that are used for logging?

Do any figures indicate design, coding, or operational errors?

  1. Are disk, I/O, log, or processor resources heavily used? If so, was that heavy use expected at design time? If not, can the heavy use be explained in terms of heavier use of workloads?
  2. Is the heavy usage associated with a particular application? If so, is there evidence of planned growth or peak periods?
  3. What are your needs for concurrent read/write and query activity?
  4. Are there any disk, channel, or path problems?
  5. Are there any abends or dumps?

What are the effects of Db2 locks?

  1. What are the incidents of deadlocks and timeouts?
  2. What percentage of elapsed time is due to lock suspensions? How much lock or latch contention was encountered? Check the contention rate per second by class.
  3. How effective is lock avoidance?

Were there any bottlenecks?

  1. Were any critical thresholds reached?
  2. Are any resources approaching high utilization?