< Day Day Up > |
The first type of performance monitoring I discuss here is monitoring based on reading trace information. You can think of a DB2 trace as a window into the performance characteristics of aspects of the DB2 workload. DB2 traces record diagnostic information describing particular events. As DB2 operates, it writes trace information that can be read and analyzed to obtain performance information. DB2 provides six types of traces, and each describes information about the DB2 environment. These six types of traces are outlined in Table 24.1. Table 24.1. DB2 Trace Types
Note that you start DB2 traces in two ways: by specifying the appropriate DSNZPARMs at DB2 startup or by using the -START TRACE command to initiate specific traces when DB2 is already running. Each trace is broken down further into classes, each of which provides information about aspects of that trace. Classes are composed of IFCIDs. An IFCID (sometimes pronounced if-kid ) is an Instrumentation Facility Component Identifier. An IFCID defines a record that represents a trace event. IFCIDs are the single smallest unit of tracing that can be invoked by DB2. The six DB2 trace types are discussed in the following sections. Accounting TraceThe accounting trace is probably the single most important trace for judging the performance of DB2 application programs. Using the accounting trace records, DB2 writes data pertaining to the following:
There are ten groups of DB2 accounting trace classes:
Estimated overhead : DB2 accounting class 1 adds approximately 3% CPU overhead. DB2 accounting classes 1, 2, and 3 together add approximately 5% CPU overhead. You cannot run class 2 or 3 without also running class 1. Accounting trace classes 7 and 8 provide performance trace information at the package level. Enabling this level of tracing can cause significant overhead. Audit TraceThe audit trace is useful for installations that must meticulously track specific types of DB2 events. Not every shop needs the audit trace. However, those wanting to audit by AUTHID , specific table accesses , and other DB2 events will find the audit trace invaluable. Eight categories of audit information are provided:
This type of data is often required of critical DB2 applications housing sensitive data, such as payroll or billing applications. There are eleven groups of DB2 audit trace classes:
Estimated overhead : Approximately 5% CPU overhead per transaction is added when all audit trace classes are started. See the "Tracing Guidelines" section later in this chapter for additional information on audit trace overhead. Global TraceGlobal trace information is used to service DB2. Global trace records information regarding entries and exits from internal DB2 modules as well as other information about DB2 internals. It is not accessible through tools that monitor DB2 performance. Most sites will never need to use the DB2 global trace. You should avoid it unless an IBM representative requests that your shop initiate it. CAUTION IBM states that the global trace can add up to 100% CPU overhead to your DB2 subsystem. Monitor TraceAn amalgamation of useful performance monitoring information is recorded by the DB2 monitor trace. Most of the information in a monitor trace is also provided by other types of DB2 traces. The primary reason for the existence of the monitor trace type is to enable you to write application programs that provide online monitoring of DB2 performance. Information provided by the monitor trace includes the following:
There are ten groups of DB2 monitor trace classes:
Estimated overhead : The overhead that results from the monitor trace depends on how it is used at your site. If, as recommended, class 1 is always active, and classes 2 and 3 are started and stopped as required, the overhead is minimal (approximately 2 to 5%, depending on the activity of the DB2 system and the number of times that the other classes are started and stopped ). However, if your installation makes use of the reserved classes (30 through 32) or additional classes (as some vendors do), your site will incur additional overhead. NOTE Some online performance monitoring tools do not use the monitor trace; instead, they read the information directly from the DB2 control blocks. Sampling DB2 control blocks requires less overhead than a monitor trace. Performance TraceThe DB2 performance trace records an abundance of information about all types of DB2 events. You should use it only after you have exhausted all other avenues of monitoring and tuning because it consumes a great deal of system resources. When a difficult problem persists, the performance trace can provide valuable information, including the following:
There are twenty groups of DB2 performance trace classes:
Estimated overhead : When all DB2 performance trace classes are active, as much as 100% CPU overhead can be incurred by each program being traced. The actual overhead might be greater if the system has a large amount of activity. Furthermore, due to the large number of trace records cut by the DB2 performance trace, systemwide (DB2 and non-DB2) performance might suffer because of possible SMF or GTF contention . The overhead when using only classes 1, 2, and 3, however, ranges from 20 to 30% rather than 100% Statistics TraceInformation pertaining to the entire DB2 subsystem is recorded in statistics trace records. This information is particularly useful for measuring the activity and response of DB2 as a whole. Information on the utilization and status of the buffer pools, DB2 locking, DB2 logging, and DB2 storage is accumulated . There are ten groups of DB2 statistics trace classes:
Estimated overhead : An average of 2% CPU overhead per transaction. |
< Day Day Up > |