| Introduction |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 1
INTRODUCTION
This report consists of three parts:
- This Introduction and a page of general run statistics.
- A listing of any rules that will not be produced, either because
CPExpert determined that required information was not present, or
because the rules were turned off in USOURCE(DB2GUIDE).
- Findings resulting from the analysis of DB2 statistics.
- Findings in the DB2-100(series) relate to general DB2 areas.
- Findings in the DB2-200(series) relate to local buffer pools.
- Findings in the DB2-300(series) relate to Lock Manager areas.
- Findings in the DB2-400(series) relate to DDF areas.
- Findings in the DB2-500(series) relate to Log Manager areas.
- Findings in the DB2-600(series) relate to data sharing areas.
It is important to realize that these findings normally identify
a POTENTIAL problem. Your systems programming staff must decide
whether the findings (and their associated recommendations) make
sense in your environment. For example, your systems programming
staff might have deliberately selected certain parameter values.
The values might be appropriate for your installation and your
management objectives, even though CPExpert might produce a rule
indicating that there is a potential problem with the parameter.
When findings are produced by the DB2 Component, please refer to the
detailed description of the rule contained in the DB2 Component User
Manual. The User Manual will describe why the finding was made, give
an assessment of the overall performance impact of the finding, and
discuss alternatives that may be implemented to solve the problem.
Please call Computer Management Sciences at (703) 922-7027 or e-mail
to Don_Deese@cpexpert.com if you have any questions or if you have
suggestions for product improvement.
|
| Rules that will be suppressed |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 2
The following rules will be suppressed. A rule is suppressed
when (1) the required data variables do not exist in your
performance data base, (2) you are not using the DB2 feature
being evaluated, (3) the rule does not apply to the version
of DB2 being analyzed, or (4) you have 'turned off' the rule in
USOURCE(DB2GUIDE).
RULE: DB2-644 QWPAASST=0
RULE: DB2-651 R744SCN=0 R744STRC=0 R744SNAM=0
RULE: DB2-652 R744SCN=0 R744STRC=0 R744SFCN=0 R744SNAM=0
RULE: DB2-660 R744SSTM=0 R744SSRC=0 R744SNAM=0
RULE: DB2-661 R744SATM=0 R744SARC=0 R744SNAM=0
RULE: DB2-665 R744CDER=0 R744CXDR=0 R744SNAM=0
RULE: DB2-671 R744CDER=0 R744CXDR=0 R744SNAM=0
RULE: DB2-310 TURNED OFF
|
| Guidance variables |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: *ALL DB2 SUBSYSTEM: *ALL
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 3
THE FOLLOWING GUIDANCE WAS PROVIDED TO THE DB2
COMPONENT DURING THIS EXECUTION OF CPEXPERT.
DB2DATES = 01FEB1991 * START DATE FOR DATA ANALYSIS
DB2TIMES = 08:00:00 * START TIME FOR DATA ANALYSIS
DB2DATEE = 31DEC2199 * END DATE FOR DATA ANALYSIS
DB2TIMEE = 17:59:59 * END TIME FOR DATA ANALYSIS
DB2DAT2S = 0 * DEFAULT SECOND SELECTION DATE - START
DB2TIM2S = 0 * DEFAULT SECOND SELECTION TIME - START
DB2DAT2E = 0 * DEFAULT SECOND SELECTION DATE - END
DB2TIM2E = 0 * DEFAULT SECOND SELECTION TIME - END
SHIFT = N * START AND END TIMES DO NOT APPLY TO A SHIFT
SYSTEM = *ALL * SPECIFY SYSTEM TO PROCESS (*ALL = ALL)
DB2SID = *ALL * SPECIFY DB2 SID TO PROCESS (*ALL = ALL)
HTML=Y; = Y=Y; * USE SAS ODS FEATURES
HTMLLINK = Y * LINK TO CPEXPERT DOCUMENTATION
FRAME = DB2FRAME.HTM * USE GENERIC ODS FRAME NAME
CONTENTS = DB2CONTENTS.HTM * USE GENERIC ODS CONTENTS NAME
BODY = DB2BODY.HTM * USE GENERIC ODS BODY NAME
PATH = * DO NOT SPECIFY PATH FOR ODS OUTPUT
PRODTEST = PROD * PRODUCTION OR TEST SUBSYSTEM
DB2ACCTX = N * DB2 ACCOUNTING DATA AVAILABILITY
T102S106 = N * DB2 ZPARMS DATA AVAILABILITY
T102S172 = N * DB2 DEADLOCK TRACE RECORDS AVAILABILITY
T102S196 = N * DB2 TIMEOUT TRACE RECORDS AVAILABILITY
BPGUIDE = N * GUIDANCE PROVIDED FOR INDIVIDUAL BUFFER
GBPGUIDE = N * GUIDANCE PROVIDED FOR GLOBAL BUFFERS
DB2LIB = PDBLIB * SAS LIBRARY CONTAINING DB2 DATA
DB2STATS = PDBLIB.DB2STATS * SPECIFY DB2STATS SAS LIB.FILE
DB2STATB = PDBLIB.DB2STATB * SPECIFY DB2STATB SAS LIB.FILE
DB2STAT2 = PDBLIB.DB2STAT2 * SPECIFY DB2STAT2 SAS LIB.FILE
DB2GBPAT = PDBLIB.DB2GBPAT * SPECIFY DB2GBPAT SAS LIB.FILE
DB2GBPST = PDBLIB.DB2GBPST * SPECIFY DB2GBPST SAS LIB.FILE
DB2STATR = PDBLIB.DB2STATR * SPECIFY DB2STATR SAS LIB.FILE
DB2ACCT = PDBLIB.DB2ACCT * SPECIFY DB2ACCT SAS LIB.FILE
DB2Rnnn = OFF * EXAMPLE: TURN OFF DB2-nnn RULE
DB2R310 = 0 * EXAMPLE: TURN OFF DB2-nnn RULE
BUFFPGRT = 100 * ACCEPTABLE PAGE-IN RATE, ALL DB2 BUFFER POOLS
PAGERATE = 10
* ACCEPTABLE PAGE-IN RATE, INDIVIDUAL BUFFER POOL
PCTCBA = 0.500 * PERCENT BUFFERS AVAILABLE
PCTCTL = 0.950 * PERCENT CURSOR TABLE SECTIONS FOUND
PCTDBDL = 0.990 * PERCENT DATA BASE DESCRIPTOR SECTIONS FOUND
PCTDPF = 0.00 * PERCENT DYNAMIC SEQUENTIAL PREFETCH
PCTEDM = 0.250 * PERCENT FREE PAGES IN EDM POOL
PCTESC = 0.00 * PERCENT LOCK ESCALATION WAS NOT EFFECTIVE
PCTFLAM = 0.00 * PERCENT FAILED LOOK-AHEAD TAPE MOUNTS
PCTPTL = 0.950 * PERCENT PACKAGE TABLE SECTIONS FOUND
PCTPWS = 0.500 * PERCENT PAGES WRITTEN TO DASD/PAGES UPDATED
PCTRAND = 0.950 * PERCENT GETPAGE FROM RANDOM REQUESTORS
PCTRSAT = 0.00 * PERCENT RECONNECTION ATTEMPTS SUCCEEDED
PCTSEQ = 0.950 * PERCENT GETPAGE FROM SEQUENTIAL REQUESTORS
PCTSLOCK = 0.5 * PERCENT LOCK REQUESTS THAT WERE SUSPENDED
Q3STCTHW = 0 * THREAD REQUESTS WERE QUEUED AT CREATE
Q3STMEOM = 0 * END-OF-MEMORY (NON-DB2 TASK MVS DELETED)
Q3STMEOT = 0 * END-OF-TASK (NON-DB2 TASK ABENDS)
QBGLAD = 0 * CF READS PREFETCH DATA RETURNED
QBGLAN = 0 * CF PREFETCH READS, NO DATA, NO DIRECTORY
QBGLAR = 0 * CF PREFETCH READS, NO DATA, DIRECTORY CREATED
QBGLCN = 0 * TIMES WHEN CASTOUT ENGINE NOT AVAILABLE
QBGLRF = 0 * CF READS FAILED DUE LACK OF CF STORAGE
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: *ALL DB2 SUBSYSTEM: *ALL
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 4
QBGLWF = 0 * CF WRITES FAILED DUE LACK OF CF STORAGE
QBGLSU = 0 * TIMES WHEN CF WRITE ENGINE WAS AVAILABLE
QBSTARF = 0 * UNSUCCESSFUL HP TO VP READS, USING ASYNC ADMF
QBSTAWF = 0 * UNSUCCESSFUL HP TO VP WRITES, USING ASYNC ADMF
QBSTCBA = 50 * PERCENT ACTIVE BUFFERS AT INTERVAL END
QBSTDMC = 0 * DATA MANAGER THRESHOLD REACHED
QBSTHRF = 0 * UNSUCCESSFUL HP TO VP MVPG READS
QBSTHWF = 0 * UNSUCCESSFUL VP TO HP PAGE WRITES
QBSTIMW = 0 * NUMBER OF IMMEDIATE WRITES
QBSTJIS = 0 * PREFETCH I/O STREAMS DENIED
QBSTMIG = 0 * TIMES MIGRATED DATA SETS WERE ENCOUNTERED
QBSTNGT = 0 * UNSUCCESSFUL GETPAGE REQUESTS
QBSTPL1 = 0 * PREFETCH REDUCED TO ½ OF NORMAL
QBSTPL2 = 0 * PREFETCH REDUCED TO 1/4 OF NORMAL
QBSTPQF = 0 * TIMES I/O PARALLELISM WAS DOWNGRADED
QBSTREE = 0 * PREFETCH DISABLED NO READ ENGINE
QBSTRTO = 0 * TIMES RECALL TIMEOUTS OCCURRED
QBSTSPD = 0 * SEQ PREFEETCH REQ DISABLED UNAVAIL BUFFER
QBSTWEE = 0 * WRITE ENGINE NOT AVAILABLE FOR ASYNC I/O
QBSTWFD = 0 * WORKFILES DENIED DURING SORT
QBSTWFF = 0 * SORT INEFFICIENT (BUFFER SHORTAGE)
QBSTWFL = 0 * NUMBER OF MERGE PASSES (CONTROLS DB2-226)
QBSTWFR = 0 * NUMBER OF MERGE PASSES (CONTROLS DB2-226)
QBSTWIO = 16 * ASYNC WRITE I/O (CONTROLS DB2-225)
QBSTWKPD = 0 * PREFETCH ABORT - ZERO*PREFETCH QTY
QDSTQCRT = 0 * CONVERSATIONS DEALLOC DUE TO ZPARM LIMIT
QDSTQDBT = 0 * DB ACCESS QUEUED DUE TO MAX REMOTE THREADS
QISEFAIL = 0 * FAILURES DUE TO POOL FULL
QISTCOLS = 0 * COLS BYPASSED INVALIDATED DB2 SERVICE
QISTRLLM = 0 * RID TERMINATED, ENTRIES GT CALCULATED LIMIT
QISTRMAX = 0 * RID TERMINATED, MAXIMUM STORAGE
QISTRPLM = 0 * RID TERMINATED, ENTRIES GT PHYSICAL LIMIT
QISTRSTG = 0 * RID TERMINATED, INSUFFICIENT STORAGE
QJSTALR = 0 * ARCHIVE LOG READ ALLOCATIONS
QJSTALW = 2 * ARCHIVE LOG WRITE ALLOCATIONS
QJSTRACT = 0 * READS SATISFIED FROM ACTIVE LOG DATA SET
QJSTRARH = 0 * READS SATISFIED FROM ARCHIVE LOG DATA SET
QJSTTVC = 0 * READ ACCESSES DELAYED, TAPE VOLUME CONTENTION
QJSTWTB = 0 * WAITS DUE TO UNAVAILABLE WRITE BUFFER
QJSTWUR = 0 * READ ACCESSES DELAYED, UNAVAILABLE RESOURCES
QLSTCNVQ = 0 * CONV REQS QUEUED BY DDF, WAITING FOR ALLOC
QLSTINDT = 0 * THREADS INDOUBT WITH REMOTE AS COORDINATOR
QTXADEA = 0 * LOCK DEADLOCK COUNT
QTXALES = 0 * LOCKS ESCALATED TO SHARED MODE
QTXALEX = 0 * LOCKS ESCALATED TO EXCLUSIVE MODE
QTXASLOC = 0 * SUSPEND COUNT, LOCK CONFLICT
QTXATIM = 0 * LOCK TIMEOUT COUNT
QWACAWTP = 0 * WAIT TIME DUE TO PAGE LATCH CONTENTION
QWSBSBUF = 0 * BUFFER ERRORS, SMF DESTINATION
QXCALLAB = 0 * STORED PROCEDURE ABENDS
QXCALLRJ = 0 * SQL CALLS REJECTED
QXCALLTO = 0 * SQL CALLS TIMED OUT WAITING SCHEDULE
QXCOORNO = 0 * PARALLEL GROUPS EXECUTED ON A SINGLE DB2
QXDEGBUF = 0 * PARALLEL GROUPS FELL BACK BUFFERS
QXDEGENC = 0 * PARALLEL GROUPS EXECUTED IN SEQUENTIAL
QXISORR = 0 * GROUPS EXECUTED ON SINGLE DB2, REPEAT READ
QXREDGRP = 0 * PARALLEL GROUPS REDUCED DUE TO BUFFERS
QXSTDEXP = 0 * DB2 DISCARDED PREPARED STATEMENT
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: *ALL DB2 SUBSYSTEM: *ALL
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 5
QXXCSKIP = 0 * BYPASSED DB2, NOT ENOUGH BUFFERS
|
| Summary of analysis performed |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 6
SUMMARY OF DB2 COMPONENT OPERATION, EXECUTED ON 15JUN2001
DB2 STATISTICS INTERVALS PROCESSED FROM MXG DB2STATS INPUT FILE: 21
INTERVALS SELECTED BASED UPON SYSTEM/START/END CRITERIA: 21
TOTAL RULE RECORDS WRITTEN BY THE DB2 COMPONENT: 233
|
| System: SYS1DB1P, Overview of rule results |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS1 DB2 SUBSYSTEM: DB1P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 7
OVERVIEW OF RULE RESULTS
DB2-120 DB2-181 DB2-216 DB2-224 DB2-226 DB2-235 DB2-311 DB2-512
------------------------------------------------------------------------
20MAR2001:13:13:44
20MAR2001:13:14:00 X X X X
20MAR2001:13:28:41
20MAR2001:13:29:00 X X X X X
20MAR2001:13:43:37
20MAR2001:13:44:00 X X X X X X
20MAR2001:13:58:34
20MAR2001:13:59:00 X X X X X X
20MAR2001:14:13:31
20MAR2001:14:14:00 X X X X X X
20MAR2001:14:28:00 X X X X X X X
20MAR2001:14:28:27
20MAR2001:14:43:00 X X X X
20MAR2001:14:43:24
|
| System: SYS2DB2P, Overview of rule results |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS2 DB2 SUBSYSTEM: DB2P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 8
OVERVIEW OF RULE RESULTS
D D D D D D D D D
B B B B B B B B B
2 2 2 2 2 2 2 2 2
- - - - - - - - -
1 1 2 2 2 2 3 5 5
2 8 1 2 2 3 1 0 1
0 1 6 4 6 5 1 2 2
------------------------------------------------------------------------
20MAR2001:13:04:00 X X X X X X
20MAR2001:13:04:19
20MAR2001:13:19:00 X X X X X X
20MAR2001:13:19:16
20MAR2001:13:34:00 X X X X X
20MAR2001:13:34:12
20MAR2001:13:49:00 X X X X X
20MAR2001:13:49:09
20MAR2001:14:04:00 X X X X X
20MAR2001:14:04:06
20MAR2001:14:19:00 X X X X X X
20MAR2001:14:19:02
20MAR2001:14:33:58
20MAR2001:14:34:00 X X X X X X
|
| System: SYS3DB3P, Overview of rule results |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS3 DB2 SUBSYSTEM: DB3P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 9
OVERVIEW OF RULE RESULTS
D D D D D D D D D
B B B B B B B B B
2 2 2 2 2 2 2 2 2
- - - - - - - - -
1 1 2 2 2 3 3 3 5
2 8 2 2 3 0 0 1 1
0 1 4 6 5 3 5 1 2
------------------------------------------------------------------------
20MAR2001:13:02:49
20MAR2001:13:03:00 X X X X X
20MAR2001:13:17:45
20MAR2001:13:18:00 X X X X X
20MAR2001:13:32:43
20MAR2001:13:33:00 X X X X X
20MAR2001:13:47:39
20MAR2001:13:48:00 X X X X X X
20MAR2001:14:02:36
20MAR2001:14:03:00 X X X X X X
20MAR2001:14:17:32
20MAR2001:14:18:00 X X X X X
20MAR2001:14:32:00 X X X X X X X X
20MAR2001:14:32:28
|
| DB2 Subystem DB1P, General findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS1 DB2 SUBSYSTEM: DB1P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 10
RULE DB2-120: INVALID SELECT PROCEDURE WAS ENCOUNTERED .
A select procedure is made invalid by applying DB2 maintenance that
directly affects the select procedure. Invalid select procedures are
bypassed by DB2, and this can cause some degradation in performance.
Plans and packages with invalid select procedures continue to function
correctly but incur some performance degradation. You should consider
running REBIND for any plan or package that has encountered invalid
select procedures to eliminate this degradation. An invalid select
procedure was encountered during the intervals shown below:
TIMES INVALID SELECT
MEASUREMENT INTERVAL PROCEDURES WERE ENCOUNTERED
13:14-13:29, 20MAR2001 96
13:29-13:44, 20MAR2001 528
13:44-13:59, 20MAR2001 1,238
13:59-14:14, 20MAR2001 445
14:14-14:28, 20MAR2001 1,545
14:28-14:43, 20MAR2001 220
14:43-14:58, 20MAR2001 1,123
RULE DB2-181: RID LIST PROCESSING TERMINATED .
RID list processing was terminated because the number of RID entries
was greater than the limit of MAX(25% of table size, number of RIDs
that can fit into the guaranteed number of RID blocks). This finding
occurred during the intervals shown below:
TIMES RID LIST PROCESSING
MEASUREMENT INTERVAL WAS TERMINATED
13:14-13:29, 20MAR2001 17
13:29-13:44, 20MAR2001 8
13:44-13:59, 20MAR2001 31
13:59-14:14, 20MAR2001 45
14:14-14:28, 20MAR2001 79
14:28-14:43, 20MAR2001 56
14:43-14:58, 20MAR2001 84
|
| DB2 Subsystem DB1P, Local Buffer Pool Findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS1 DB2 SUBSYSTEM: DB1P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 11
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE .
Buffer Pool 0: CPExpert believes that the Sequential Steal Threshold
(VPSEQT) value might be too large for the intervals shown below. Most
of the GETPAGE requests for this buffer pool were issued by random
requestors. However, the VPSEQT threshold was not specified to favor
random access significantly over sequential access. Please ignore this
finding (or "turn off" this rule for this buffer pool) unless the
finding is consistently produced during intervals when critical work is
being processed.
BUFFERS GETPAGE PERCENT VPSEQT
MEASUREMENT INTERVAL ALLOCATED REQUESTS RANDOM VALUE
13:14-13:29, 20MAR2001 10,000 193,184 100 80
13:29-13:44, 20MAR2001 10,000 287,792 100 80
13:44-13:59, 20MAR2001 10,000 180,320 100 80
14:14-14:28, 20MAR2001 10,000 81,088 96 80
14:28-14:43, 20MAR2001 10,000 33,824 98 80
RULE DB2-216: PAGE FAULT RATE WAS HIGH FOR INDIVIDUAL BUFFER POOL .
Buffer Pool 1: The page fault rate for read and write I/O was larger
than 10 pages per second. This situation occurred for Buffer Pool 1
during the intervals shown below:
BUFFERS PAGE-IN FOR PAGE-IN FOR PAGE
MEASUREMENT INTERVAL ALLOCATED READ I/O WRITE I/O RATE
13:44-13:59, 20MAR2001 80,000 11,090 39 12.4
13:59-14:14, 20MAR2001 80,000 11,478 190 13.0
14:14-14:28, 20MAR2001 80,000 15,633 35 17.5
14:28-14:43, 20MAR2001 80,000 19,754 216 22.3
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE .
Buffer Pool 1: CPExpert believes that the Sequential Steal Threshold
(VPSEQT) value might be too large for the intervals shown below. Most
of the GETPAGE requests for this buffer pool were issued by random
requestors. However, the VPSEQT threshold was not specified to favor
random access significantly over sequential access. Please ignore this
finding (or "turn off" this rule for this buffer pool) unless the
finding is consistently produced during intervals when critical work is
being processed.
BUFFERS GETPAGE PERCENT VPSEQT
MEASUREMENT INTERVAL ALLOCATED REQUESTS RANDOM VALUE
13:14-13:29, 20MAR2001 80,000 4,029,440 98 80
13:29-13:44, 20MAR2001 80,000 3,317,760 96 80
13:44-13:59, 20MAR2001 80,000 3,363,840 96 80
13:59-14:14, 20MAR2001 80,000 5,806,080 99 80
14:14-14:28, 20MAR2001 80,000 5,949,440 98 80
14:28-14:43, 20MAR2001 80,000 4,342,784 96 80
14:43-14:58, 20MAR2001 80,000 5,330,944 97 80
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS1 DB2 SUBSYSTEM: DB1P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 12
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 1: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 1 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:14-13:29, 20MAR2001 3,228 55,888 59,116 94.5
13:29-13:44, 20MAR2001 4,464 28,992 33,456 86.7
13:44-13:59, 20MAR2001 4,630 38,752 43,382 89.3
13:59-14:14, 20MAR2001 2,370 23,376 25,746 90.8
14:14-14:28, 20MAR2001 4,492 47,152 51,644 91.3
14:28-14:43, 20MAR2001 5,844 33,664 39,508 85.2
14:43-14:58, 20MAR2001 4,576 46,384 50,960 91.0
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 2: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 2 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:14-13:29, 20MAR2001 7,688 96,800 104,488 92.6
13:29-13:44, 20MAR2001 10,936 81,184 92,120 88.1
13:44-13:59, 20MAR2001 28,056 134,608 162,664 82.8
13:59-14:14, 20MAR2001 15,488 50,016 65,504 76.4
14:14-14:28, 20MAR2001 10,376 64,336 74,712 86.1
14:28-14:43, 20MAR2001 11,136 61,728 72,864 84.7
14:43-14:58, 20MAR2001 13,576 50,896 64,472 78.9
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS1 DB2 SUBSYSTEM: DB1P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 13
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE .
Buffer Pool 3: CPExpert believes that the Sequential Steal Threshold
(VPSEQT) value might be too large for the intervals shown below. Most
of the GETPAGE requests for this buffer pool were issued by random
requestors. However, the VPSEQT threshold was not specified to favor
random access significantly over sequential access. Please ignore this
finding (or "turn off" this rule for this buffer pool) unless the
finding is consistently produced during intervals when critical work is
being processed.
BUFFERS GETPAGE PERCENT VPSEQT
MEASUREMENT INTERVAL ALLOCATED REQUESTS RANDOM VALUE
13:14-13:29, 20MAR2001 40,000 98,816 95 80
13:29-13:44, 20MAR2001 40,000 648,704 97 80
13:44-13:59, 20MAR2001 40,000 99,584 98 80
14:28-14:43, 20MAR2001 40,000 449,792 99 80
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 3: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 3 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:29-13:44, 20MAR2001 381 1,355 1,736 78.1
13:59-14:14, 20MAR2001 390 1,058 1,448 73.1
14:14-14:28, 20MAR2001 341 3,796 4,137 91.8
14:28-14:43, 20MAR2001 150 16,281 16,431 99.1
14:43-14:58, 20MAR2001 250 1,476 1,726 85.5
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 4: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 4 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:14-13:29, 20MAR2001 240 3,976 4,216 94.3
13:29-13:44, 20MAR2001 2,330 16,528 18,858 87.6
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS1 DB2 SUBSYSTEM: DB1P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 14
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:44-13:59, 20MAR2001 433 12,792 13,225 96.7
13:59-14:14, 20MAR2001 1,032 13,336 14,368 92.8
14:14-14:28, 20MAR2001 778 13,640 14,418 94.6
14:28-14:43, 20MAR2001 2,014 34,288 36,302 94.5
14:43-14:58, 20MAR2001 3,285 11,152 14,437 77.2
RULE DB2-226: DWQT, VDWQT, OR VPSEQT MIGHT BE TOO SMALL .
Buffer Pool 7: CPExpert noticed that this buffer pool is used for
sort activity. The DWQT threshold was specified as 50, the VDWQT
threshold was specified as 10, and the VPSEQT threshold was specified
as 95. While it is normally suggested that the DWQT and VDWQT values
be relatively low to avoid "spikes" in page write activity, this advice
does not apply to buffer pools heavily used by sort processing.
Instead, the advice is reversed for these buffer pools, to allow sort
processing to process sorted data in central storage. This situation
occurred for Buffer Pool 7 during the intervals shown below:
BUFFERS TOTAL MERGE THRESHOLDS REACHED
MEASUREMENT INTERVAL ALLOCATED PASSES DWQT VDWQT
13:29-13:44, 20MAR2001 7,000 646 0 5
13:44-13:59, 20MAR2001 7,000 779 0 9
14:14-14:28, 20MAR2001 7,000 728 0 10
|
| DB2 Subsystem DB1P, Lock Manager Findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS1 DB2 SUBSYSTEM: DB1P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 15
RULE DB2-311: DEADLOCKS WERE DETECTED .
A deadlock occurs when two or more application processes each hold
locks on resources that the others need and without which they cannot
proceed. DB2 scans for deadlocked processes at regular intervals,
with the scan interval set by the DEADLOCK TIME on Installation Panel
DSNTIPJ. Upon detecting a deadlock, DB2 can roll back the current unit
of work for one of the processes or request a process to terminate.
Deadlocks can significantly affect performance and should be avoided.
This situation occurred during the intervals shown below:
LOCK LOCK LOCK
MEASUREMENT INTERVAL REQUESTS SUSPENSION TIMEOUTS DEADLOCKS
13:59-14:14, 20MAR2001 7,220,224 41 0 1
14:28-14:43, 20MAR2001 3,582,976 33 1 1
RULE DB2-512: LOG READS WERE SATISFIED FROM ACTIVE LOG DATA SET .
The DB2 Log Manager statistics revealed that more than 25% of the
log reads were satisfied from the active log data set. It is
preferable that the data be in the output buffer, but this is not
always possible with an active DB2 environment. However, if a large
percent of reads are satisfied from the active log, you should ensure
that the output buffer is as large as possible. This finding occurred
during the intervals shown below:
TOTAL LOG LOG READS FROM
MEASUREMENT INTERVAL READS ACTIVE LOG DATA SET PERCENT
14:28-14:43, 20MAR2001 898 512 57.0
|
| DB2 Subystem DB2P, General findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS2 DB2 SUBSYSTEM: DB2P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 16
RULE DB2-120: INVALID SELECT PROCEDURE WAS ENCOUNTERED .
A select procedure is made invalid by applying DB2 maintenance that
directly affects the select procedure. Invalid select procedures are
bypassed by DB2, and this can cause some degradation in performance.
Plans and packages with invalid select procedures continue to function
correctly but incur some performance degradation. You should consider
running REBIND for any plan or package that has encountered invalid
select procedures to eliminate this degradation. An invalid select
procedure was encountered during the intervals shown below:
TIMES INVALID SELECT
MEASUREMENT INTERVAL PROCEDURES WERE ENCOUNTERED
13:04-13:19, 20MAR2001 2,452
13:19-13:34, 20MAR2001 6,550
13:34-13:49, 20MAR2001 4,972
13:49-14:04, 20MAR2001 4,888
14:04-14:19, 20MAR2001 3,552
14:19-14:34, 20MAR2001 3,946
14:34-14:49, 20MAR2001 3,626
RULE DB2-181: RID LIST PROCESSING TERMINATED .
RID list processing was terminated because the number of RID entries
was greater than the limit of MAX(25% of table size, number of RIDs
that can fit into the guaranteed number of RID blocks). This finding
occurred during the intervals shown below:
TIMES RID LIST PROCESSING
MEASUREMENT INTERVAL WAS TERMINATED
13:04-13:19, 20MAR2001 508
13:19-13:34, 20MAR2001 23
13:34-13:49, 20MAR2001 41
13:49-14:04, 20MAR2001 60
14:04-14:19, 20MAR2001 108
14:19-14:34, 20MAR2001 31
14:34-14:49, 20MAR2001 35
|
| DB2 Subsystem DB2P, Local Buffer Pool Findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS2 DB2 SUBSYSTEM: DB2P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 17
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE .
Buffer Pool 0: CPExpert believes that the Sequential Steal Threshold
(VPSEQT) value might be too large for the intervals shown below. Most
of the GETPAGE requests for this buffer pool were issued by random
requestors. However, the VPSEQT threshold was not specified to favor
random access significantly over sequential access. Please ignore this
finding (or "turn off" this rule for this buffer pool) unless the
finding is consistently produced during intervals when critical work is
being processed.
BUFFERS GETPAGE PERCENT VPSEQT
MEASUREMENT INTERVAL ALLOCATED REQUESTS RANDOM VALUE
13:04-13:19, 20MAR2001 10,000 202,560 98 80
13:19-13:34, 20MAR2001 10,000 426,880 100 80
13:34-13:49, 20MAR2001 10,000 695,040 100 80
13:49-14:04, 20MAR2001 10,000 547,648 100 80
14:04-14:19, 20MAR2001 10,000 508,032 99 80
14:19-14:34, 20MAR2001 10,000 536,256 100 80
14:34-14:49, 20MAR2001 10,000 412,992 100 80
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 1: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 1 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:04-13:19, 20MAR2001 38,696 25,136 63,832 39.4
13:19-13:34, 20MAR2001 16,680 38,040 54,720 69.5
13:34-13:49, 20MAR2001 8,736 30,928 39,664 78.0
13:49-14:04, 20MAR2001 47,460 12,648 60,108 21.0
14:04-14:19, 20MAR2001 33,172 28,288 61,460 46.0
14:19-14:34, 20MAR2001 109,116 29,000 138,116 21.0
14:34-14:49, 20MAR2001 6,328 17,104 23,432 73.0
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS2 DB2 SUBSYSTEM: DB2P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 18
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 2: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 2 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:04-13:19, 20MAR2001 42,528 83,120 125,648 66.2
13:19-13:34, 20MAR2001 38,288 73,552 111,840 65.8
13:34-13:49, 20MAR2001 54,576 99,232 153,808 64.5
13:49-14:04, 20MAR2001 50,048 87,776 137,824 63.7
14:04-14:19, 20MAR2001 57,280 86,096 143,376 60.0
14:19-14:34, 20MAR2001 50,800 69,488 120,288 57.8
14:34-14:49, 20MAR2001 39,216 49,664 88,880 55.9
RULE DB2-216: PAGE FAULT RATE WAS HIGH FOR INDIVIDUAL BUFFER POOL .
Buffer Pool 3: The page fault rate for read and write I/O was larger
than 10 pages per second. This situation occurred for Buffer Pool 3
during the intervals shown below:
BUFFERS PAGE-IN FOR PAGE-IN FOR PAGE
MEASUREMENT INTERVAL ALLOCATED READ I/O WRITE I/O RATE
14:19-14:34, 20MAR2001 40,000 16,542 0 18.5
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE .
Buffer Pool 3: CPExpert believes that the Sequential Steal Threshold
(VPSEQT) value might be too large for the intervals shown below. Most
of the GETPAGE requests for this buffer pool were issued by random
requestors. However, the VPSEQT threshold was not specified to favor
random access significantly over sequential access. Please ignore this
finding (or "turn off" this rule for this buffer pool) unless the
finding is consistently produced during intervals when critical work is
being processed.
BUFFERS GETPAGE PERCENT VPSEQT
MEASUREMENT INTERVAL ALLOCATED REQUESTS RANDOM VALUE
13:04-13:19, 20MAR2001 40,000 1,115,136 96 80
13:19-13:34, 20MAR2001 40,000 4,393,472 99 80
13:34-13:49, 20MAR2001 40,000 2,833,408 98 80
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS2 DB2 SUBSYSTEM: DB2P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 19
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 3: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 3 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:04-13:19, 20MAR2001 1,127 3,474 4,601 75.5
13:19-13:34, 20MAR2001 962 7,792 8,754 89.0
13:34-13:49, 20MAR2001 1,720 4,212 5,932 71.0
13:49-14:04, 20MAR2001 3,553 8,986 12,539 71.7
14:04-14:19, 20MAR2001 3,568 2,446 6,014 40.7
14:19-14:34, 20MAR2001 7,276 2,308 9,584 24.1
14:34-14:49, 20MAR2001 6,821 11,150 17,971 62.0
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 4: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 4 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:04-13:19, 20MAR2001 10,396 30,704 41,100 74.7
13:19-13:34, 20MAR2001 2,464 50,416 52,880 95.3
13:34-13:49, 20MAR2001 10,448 84,496 94,944 89.0
13:49-14:04, 20MAR2001 17,020 100,976 117,996 85.6
14:04-14:19, 20MAR2001 11,788 77,552 89,340 86.8
14:19-14:34, 20MAR2001 20,128 60,560 80,688 75.1
14:34-14:49, 20MAR2001 7,064 35,040 42,104 83.2
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS2 DB2 SUBSYSTEM: DB2P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 20
RULE DB2-226: DWQT, VDWQT, OR VPSEQT MIGHT BE TOO SMALL .
Buffer Pool 7: CPExpert noticed that this buffer pool is used for
sort activity. The DWQT threshold was specified as 50, the VDWQT
threshold was specified as 10, and the VPSEQT threshold was specified
as 95. While it is normally suggested that the DWQT and VDWQT values
be relatively low to avoid "spikes" in page write activity, this advice
does not apply to buffer pools heavily used by sort processing.
Instead, the advice is reversed for these buffer pools, to allow sort
processing to process sorted data in central storage. This situation
occurred for Buffer Pool 7 during the intervals shown below:
BUFFERS TOTAL MERGE THRESHOLDS REACHED
MEASUREMENT INTERVAL ALLOCATED PASSES DWQT VDWQT
13:04-13:19, 20MAR2001 7,000 149 0 175
13:19-13:34, 20MAR2001 7,000 74 0 254
13:34-13:49, 20MAR2001 7,000 65 0 241
13:49-14:04, 20MAR2001 7,000 140 0 298
14:04-14:19, 20MAR2001 7,000 213 0 119
14:19-14:34, 20MAR2001 7,000 84 0 1,573
14:34-14:49, 20MAR2001 7,000 135 0 599
|
| DB2 Subsystem DB2P, Lock Manager Findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS2 DB2 SUBSYSTEM: DB2P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 21
RULE DB2-311: DEADLOCKS WERE DETECTED .
A deadlock occurs when two or more application processes each hold
locks on resources that the others need and without which they cannot
proceed. DB2 scans for deadlocked processes at regular intervals,
with the scan interval set by the DEADLOCK TIME on Installation Panel
DSNTIPJ. Upon detecting a deadlock, DB2 can roll back the current unit
of work for one of the processes or request a process to terminate.
Deadlocks can significantly affect performance and should be avoided.
This situation occurred during the intervals shown below:
LOCK LOCK LOCK
MEASUREMENT INTERVAL REQUESTS SUSPENSION TIMEOUTS DEADLOCKS
14:34-14:49, 20MAR2001 832,000 84 0 5
RULE DB2-502: ARCHIVE LOG WRITE ALLOCATIONS EXCEEDED GUIDANCE .
The archive log open and close activity for write allocations was higher
than the guidance provided to CPExpert. This finding might indicate a
need for more or larger active log data sets. Archive log write open
and close activity was high during the intervals shown below:
ARCHIVE LOG OPEN
MEASUREMENT INTERVAL AND CLOSE WRITE ACTIVITY
13:04-13:19, 20MAR2001 6
RULE DB2-512: LOG READS WERE SATISFIED FROM ACTIVE LOG DATA SET .
The DB2 Log Manager statistics revealed that more than 25% of the
log reads were satisfied from the active log data set. It is
preferable that the data be in the output buffer, but this is not
always possible with an active DB2 environment. However, if a large
percent of reads are satisfied from the active log, you should ensure
that the output buffer is as large as possible. This finding occurred
during the intervals shown below:
TOTAL LOG LOG READS FROM
MEASUREMENT INTERVAL READS ACTIVE LOG DATA SET PERCENT
13:19-13:34, 20MAR2001 4,605 1,229 26.7
|
| DB2 Subystem DB3P, General findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS3 DB2 SUBSYSTEM: DB3P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 22
RULE DB2-120: INVALID SELECT PROCEDURE WAS ENCOUNTERED .
A select procedure is made invalid by applying DB2 maintenance that
directly affects the select procedure. Invalid select procedures are
bypassed by DB2, and this can cause some degradation in performance.
Plans and packages with invalid select procedures continue to function
correctly but incur some performance degradation. You should consider
running REBIND for any plan or package that has encountered invalid
select procedures to eliminate this degradation. An invalid select
procedure was encountered during the intervals shown below:
TIMES INVALID SELECT
MEASUREMENT INTERVAL PROCEDURES WERE ENCOUNTERED
13:03-13:18, 20MAR2001 887
13:18-13:33, 20MAR2001 1,252
13:33-13:48, 20MAR2001 354
13:48-14:03, 20MAR2001 3,451
14:03-14:18, 20MAR2001 1,550
14:18-14:32, 20MAR2001 1,067
14:32-14:47, 20MAR2001 2,072
RULE DB2-181: RID LIST PROCESSING TERMINATED .
RID list processing was terminated because the number of RID entries
was greater than the limit of MAX(25% of table size, number of RIDs
that can fit into the guaranteed number of RID blocks). This finding
occurred during the intervals shown below:
TIMES RID LIST PROCESSING
MEASUREMENT INTERVAL WAS TERMINATED
13:03-13:18, 20MAR2001 36
13:18-13:33, 20MAR2001 15
13:33-13:48, 20MAR2001 22
13:48-14:03, 20MAR2001 14
14:03-14:18, 20MAR2001 15
14:18-14:32, 20MAR2001 40
14:32-14:47, 20MAR2001 16
|
| DB2 Subsystem DB3P, Local Buffer Pool Findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS3 DB2 SUBSYSTEM: DB3P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 23
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE .
Buffer Pool 0: CPExpert believes that the Sequential Steal Threshold
(VPSEQT) value might be too large for the intervals shown below. Most
of the GETPAGE requests for this buffer pool were issued by random
requestors. However, the VPSEQT threshold was not specified to favor
random access significantly over sequential access. Please ignore this
finding (or "turn off" this rule for this buffer pool) unless the
finding is consistently produced during intervals when critical work is
being processed.
BUFFERS GETPAGE PERCENT VPSEQT
MEASUREMENT INTERVAL ALLOCATED REQUESTS RANDOM VALUE
13:03-13:18, 20MAR2001 10,000 298,688 100 80
13:18-13:33, 20MAR2001 10,000 999,712 100 80
13:33-13:48, 20MAR2001 10,000 623,072 100 80
13:48-14:03, 20MAR2001 10,000 336,256 100 80
14:03-14:18, 20MAR2001 10,000 191,488 98 80
14:18-14:32, 20MAR2001 10,000 452,800 100 80
14:32-14:47, 20MAR2001 10,000 91,808 99 80
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE .
Buffer Pool 1: CPExpert believes that the Sequential Steal Threshold
(VPSEQT) value might be too large for the intervals shown below. Most
of the GETPAGE requests for this buffer pool were issued by random
requestors. However, the VPSEQT threshold was not specified to favor
random access significantly over sequential access. Please ignore this
finding (or "turn off" this rule for this buffer pool) unless the
finding is consistently produced during intervals when critical work is
being processed.
BUFFERS GETPAGE PERCENT VPSEQT
MEASUREMENT INTERVAL ALLOCATED REQUESTS RANDOM VALUE
13:03-13:18, 20MAR2001 80,000 1,820,672 98 80
13:18-13:33, 20MAR2001 80,000 2,525,184 99 80
13:48-14:03, 20MAR2001 80,000 2,428,928 98 80
14:03-14:18, 20MAR2001 80,000 2,160,640 97 80
14:18-14:32, 20MAR2001 80,000 2,502,656 98 80
14:32-14:47, 20MAR2001 80,000 4,134,912 98 80
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS3 DB2 SUBSYSTEM: DB3P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 24
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 1: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 1 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:03-13:18, 20MAR2001 1,152 24,352 25,504 95.5
13:18-13:33, 20MAR2001 1,376 48,064 49,440 97.2
13:33-13:48, 20MAR2001 17,464 96,352 113,816 84.7
13:48-14:03, 20MAR2001 1,656 49,776 51,432 96.8
14:03-14:18, 20MAR2001 2,296 53,376 55,672 95.9
14:18-14:32, 20MAR2001 1,840 55,696 57,536 96.8
14:32-14:47, 20MAR2001 2,592 134,472 137,064 98.1
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 2: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 2 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:03-13:18, 20MAR2001 2,544 41,440 43,984 94.2
13:18-13:33, 20MAR2001 2,576 84,416 86,992 97.0
13:33-13:48, 20MAR2001 7,552 141,472 149,024 94.9
13:48-14:03, 20MAR2001 20,032 56,096 76,128 73.7
14:03-14:18, 20MAR2001 3,872 50,032 53,904 92.8
14:18-14:32, 20MAR2001 12,624 57,680 70,304 82.0
14:32-14:47, 20MAR2001 3,056 210,528 213,584 98.6
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS3 DB2 SUBSYSTEM: DB3P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 25
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 3: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 3 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:33-13:48, 20MAR2001 14,991 2,498 17,489 14.3
13:48-14:03, 20MAR2001 6,152 3,002 9,154 32.8
14:03-14:18, 20MAR2001 13,052 4,498 17,550 25.6
14:18-14:32, 20MAR2001 17,761 5,124 22,885 22.4
14:32-14:47, 20MAR2001 3,204 2,918 6,122 47.7
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH .
Buffer Pool 4: DB2 invoked dynamic sequential prefetch a large percent
of total sequential prefetch, as a result of DB2's sequential detection.
Please review the discussion in the DB2 Component User Manual related
to this finding. You should either use the RUNSTATS utility to reduce
the percent of dynamic detection, or use the PCTDPF guidance variable
to alter CPExpert's analysis of dynamic sequential prefetch for this
buffer pool. This situation occurred for Buffer Pool 4 during the
intervals shown below:
--SEQUENTIAL PREFETCH OPERATIONS-- PERCENT
MEASUREMENT INTERVAL NORMAL DYNAMIC TOTAL DYNAMIC
13:03-13:18, 20MAR2001 532 9,536 10,068 94.7
13:18-13:33, 20MAR2001 980 22,464 23,444 95.8
13:33-13:48, 20MAR2001 6,124 76,560 82,684 92.6
13:48-14:03, 20MAR2001 1,248 58,456 59,704 97.9
14:03-14:18, 20MAR2001 18,076 36,848 54,924 67.1
14:18-14:32, 20MAR2001 9,208 83,168 92,376 90.0
14:32-14:47, 20MAR2001 3,392 31,232 34,624 90.2
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS3 DB2 SUBSYSTEM: DB3P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 26
RULE DB2-226: DWQT, VDWQT, OR VPSEQT MIGHT BE TOO SMALL .
Buffer Pool 7: CPExpert noticed that this buffer pool is used for
sort activity. The DWQT threshold was specified as 50, the VDWQT
threshold was specified as 10, and the VPSEQT threshold was specified
as 95. While it is normally suggested that the DWQT and VDWQT values
be relatively low to avoid "spikes" in page write activity, this advice
does not apply to buffer pools heavily used by sort processing.
Instead, the advice is reversed for these buffer pools, to allow sort
processing to process sorted data in central storage. This situation
occurred for Buffer Pool 7 during the intervals shown below:
BUFFERS TOTAL MERGE THRESHOLDS REACHED
MEASUREMENT INTERVAL ALLOCATED PASSES DWQT VDWQT
13:03-13:18, 20MAR2001 7,000 93 0 735
13:18-13:33, 20MAR2001 7,000 93 0 677
13:33-13:48, 20MAR2001 7,000 41 0 296
13:48-14:03, 20MAR2001 7,000 71 0 768
14:03-14:18, 20MAR2001 7,000 97 0 471
14:18-14:32, 20MAR2001 7,000 74 0 459
14:32-14:47, 20MAR2001 7,000 52 0 527
|
| DB2 Subsystem DB3P, Lock Manager Findings |
ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001
SYSTEM: SYS3 DB2 SUBSYSTEM: DB3P
Analyzed by Version 11.2.0 of CPExpert(tm)
(C) Copyright 2001, Computer Management Sciences, Inc.
PAGE: 27
RULE DB2-303: LOCKS WERE ESCALATED TO EXCLUSIVE MODE .
Lock escalation releases a large number of page or row locks, held by
an application process on a single table or table space, to acquire a
table or table space lock, or a set of partition locks, of mode S or X.
Escalation can cause unpredictable response times. Lock escalation
should happen only when an application process updates or references
(if repeatable read is used) more pages or rows than it normally does.
This situation occurred during the intervals shown below:
NUMBER OF NUMBER OF LOCKS
MEASUREMENT INTERVAL LOCK REQUESTS ESCALATED TO EXCLUSIVE
14:32-14:47, 20MAR2001 406,528 1
RULE DB2-305: LOCK ESCALATION WAS NOT EFFECTIVE .
Lock escalation releases a large number of page or row locks, held by
an application process on a single table or table space, to acquire a
table or table space lock, or a set of partition locks, of mode S or X.
Lock escalation balances concurrency with performa |