Figure 1 shows
that 908 pages from CCATEMP are currently in memory in the disk buffer
pool. There may be thousands of CCATEMP pages containing the data structures,
either in the CCATEMP dataset or dataspace, but these are the ones currently
being referenced. It is not unusual to see 15-20% of the buffer pool consumed
by CCATEMP pages.
A total of
six thousand buffers (NUMBUF=6000) were allocated to this run. However,
only 3280 buffers currently have data. The total number of buffers in
use goes up and down during the run but can never exceed NUMBUF. Of the
6,000 buffers available, roughly 15% currently contain CCATEMP pages.
Or, looking at percentages another way, of the 3,280 buffers currently
in use, 27% contain CCATEMP pages. CCATEMP is typically the most active
file in a run because it contains data structures created and being referenced
by all logged in users.
CCAGRP
CCAGRP stores
all permanent group definitions in Table D pages. There are no Table A,
B or C pages in CCAGRP and no FCT pages except for the FPL.
Other
Model 204 Files
The remaining files
are either data files, procedure files or a combination of both.
The files named USERPROC,
SUBSPROC and SYSPROC are all procedure files. Each have the FPL page,
which is always the first page of the FCT, in memory. The first two files
then have only pages from Table D, indicating that the file probably contains
procedures only and no data.
However, SYSPROC
has one page from each of Table A, B and C also in memory. This indicates
that the file, in addition to being a procedure file, also contains data,
although probably only a small amount. The data also has at least one
field defined with the KEY attribute since one page from Table C is also
in the buffer pool. The Table A page is in the buffer pool most likely
as the result of compiling a request that referenced one of the fields
defined in the file.
The remaining files
appear to be data files. Although the pages from Table D could be User
Language procedures, we know that is not the case here because we know
the files. Each has some number of pages in the buffer pool from the FCT
and Table A, B, C, and D. Several of the files have some number of Table
C pages currently in memory, indicating that fields with the KEY attribute
are defined and are currently being, or have recently been referenced.
Some have pages from Table A indicating that requests were compiled that
referenced fields in the file. It is also possible that fields with the
FV (few valued), MV (many valued) or COD (coded) attributes have been
referenced and that data is required during evaluation.
A page is only read
into the buffer pool when data on that page is referenced by a command
or by a User Language program. Based on this, the activity at the instant
the MONITOR DISKBUFF command was issued indicates a fair amount of Ordered
Index processing in conjuction with Table B access in the HISTORY, CUSTOMER,
and ACCOUNTS files. If subsequent displays using MONITOR DISKBUFF reveal
a similar pattern, then you have a good indication that these files are
typically processed in this way.
Other clues
Suppose that the
display in Figure 1 is fairly typical most of the day. But on occassion,
with many users logged in and many files open for ad hoc requests or by
production APSY subsystems, you see something like Figure 2:
Figure
2. MONITOR DISKBUFF command output on an atypical day
FILENAME FCT TABLE A TABLE B TABLE C TABLE D *TOTAL*
-------- ---- ------- ------- ------- ------- -------
CCATEMP 0 4 0 0 0 4
ACCOUNTS 0 0 5994 0 2 5996
*TOTAL* 0 4 5994 0 2 6000
|
|
This is almost conclusive
evidence of a direct Table B search agains the ACCOUNTS file. There are
a few other possibilities--a TABLEB RECLEN or TABLEB LIST command--but
those are not generally issued in a production Online. The problem with
Table B searches, aside from the fact that they are extremely I/O intensive,
is that the reading of numerous Table B pages into the buffer pool may
cause all other pages to be replaced, especially if the buffer pool is
small or the direct search reads many Table B pages. If you need access
to those pages in the near future, the pages must be re-read from the
disk dataset, increasing overall database I/O, increasing response time
and reducing Online performance.
The same type of
results occurs when a request performs a long walk of the Ordered Index.
An example of this is:
If there are thousands
of values of LASTNAME, each value in the Ordered Index is examined to
see if it matches the pattern. This can result in reading a very large
number of Table D pages into the buffer pool, again displacing pages already
in memory with the same consequences as a direct Table B search. Figure
3 shows an example of this situation.
Figure
3. MONITOR DISKBUFF command output for a lengthy pattern match
FILENAME FCT TABLE A TABLE B TABLE C TABLE D *TOTAL*
-------- ---- ------- ------- ------- ------- -------
CCATEMP 0 2 0 0 0 2
ACCOUNTS 0 0 0 0 4998 4998
*TOTAL* 0 2 0 0 4998 5000
|
|
Either of these processes
can monopolize the buffer pool for the duration of the particular statement
and result in a degradation of performance for all users.
Another clue to how
and what data is being used is the Table A column. Consider the display
in Figure 4.
Figure
4. Files with a large number of Table A pages
FILENAME FCT TABLE A TABLE B TABLE C TABLE D *TOTAL*
-------- ---- ------- ------- ------- ------- -------
USERPROC 1 0 0 0 66 67
SUBSPROC 1 0 0 0 1 2
SCRATCH 1 0 0 0 0 1
ACCTPROCS 1 0 0 0 98 99
STATUS 0 48 246 0 452 746
TEMP 0 2 16 3 3 24
MESSAGES 0 35 2 0 20 57
|
|
The STATUS and MESSAGES
files both have quite a few Table A pages in memory. A typical file with
no FV, MV or CODED fields usually has at most one page from Table A in
the buffer pool. These high numbers for these files indicate that extensive
use is being made of fields that have some of those attributes.
Summary
If you want to know more about how your data is being accessed or require
additional information for resolving performance problems, the MONITOR
DISKBUFF command can be a valuable tool in accomplishing those objectives.
The command is restricted to system managers, but that restriction can
be overridden by the system manager issuing the following command:
RESET COMMAND MONITOR PRIVILEGE=LOGONREQ
|
|
This
grants authority to issue any MONITOR command to any logged in user.
