How do I determine which data/index areas are the best candidates to be implemented as covered
Article ID: 55147
Updated On:
Products
Issue/Introduction
Description:
This article provides SQL Datacom Dynamic System Tables queries to determine the areas for which the COVERED Multi-User startup option may yield the most benefit. If you have not implemented covered areas, here is a way to determine the potential benefits of covering one or more areas. If you are already using covered areas, here is a way to verify that your choices are yielding the best benefit.
Solution:
When considering which data or index areas to cover, one important consideration is the potential for saving I/O. In many cases, the first step in determining Memory Resident Data Facility (MRDF) COVERED specifications is to use the potential I/O savings to locate the candidate pool of areas from which to choose for the COVERED specification.
Once this pool is determined, you can choose to "weight" the candidate areas using addition criteria such as SLA requirements, available memory to use for MRDF covered areas, and so on.
To begin the candidate selection process, issue the following two SQL queries. These queries should be executed after the MUF has been active long enough to capture system table data representative of your processing load. If you cycle MUF on a regular basis, issue these queries just prior to the shutdown.
Query 1:
*
* GET ALL EXISTING COVERED DATASETS AND CALCULATE SAVED I/O
* PER MB
*
SELECT
A.DBID, A.AREA_NAME AS AREA,
D.BLOCK_LENGTH AS BLOCK, D.IN_USE_BLOCKS AS #BLOCKS,
A.LOGICAL_READS AS LGLREADS, A.LOGICAL_WRITES AS LGLWRITES,
A.PHYSICAL_READS AS PHYSREADS, A.PHYSICAL_WRITES AS PHYSWRITES,
( M.MEMORY_SIZE/(1024 *1024)) AS MRDF_MEGS,
M.TOTAL_READS AS M_TOT_READS,
(M.ACTIVE_READS + M.NONFIRST_READS) AS M_PHYS_READS,
M.MRDF_READS AS M_SAVED_READS ,
(M.MRDF_READS / (M.MEMORY_SIZE /(1024*1024))) AS READS_PER_MEG
FROM SYSADM.MUF_AREA_STATS A, SYSADM.DIR_DATASET D,
SYSADM.MUF_COVEREDVIRTUAL M
WHERE
A.DBID = M.DBID AND
A.AREA_NAME = M.AREA_NAME AND
A.DBID = D.DBID AND
A.AREA_NAME = D.AREA_NAME AND
M.VIRTUAL_COVERED = 'C'
ORDER BY 12 DESC;
Query 2:
*
* GET DATASETS OVER 1 MEG THAT HAVE MORE THAN 10000
* PHYSICAL READS
*
* CALCULATE POTENTIAL SAVED I/O PER MEG COVERED
*
SELECT
A.DBID,
A.AREA_NAME AS AREA,
D.BLOCK_LENGTH AS BLOCK,
D.IN_USE_BLOCKS AS #BLOCKS,
A.LOGICAL_READS AS LGLREADS,
A.LOGICAL_WRITES AS LGLWRITES,
A.PHYSICAL_READS AS PHYSREADS,
PHYSICAL_WRITES AS PHYSWRITES,
( M.MEMORY_SIZE/(1024 * 1024)) AS M_MEGS,
M.TOTAL_READS AS M_TOT_READS,
(M.ACTIVE_READS + M.NONFIRST_READS) AS M_PHYS_READS,
M.MRDF_READS AS M_SAVED_READS ,
(M.MRDF_READS / (M.MEMORY_SIZE /(1024*1024))) AS M_READS_PER_MEG ,
0 AS POT_MRDF_MEGS,
0 AS POT_SAVEDIO_MEG
FROM SYSADM.MUF_AREA_STATS A, SYSADM.DIR_DATASET D,
SYSADM.MUF_COVEREDVIRTUAL M
WHERE
A.DBID = M.DBID AND
A.AREA_NAME = M.AREA_NAME AND
A.DBID = D.DBID AND
A.AREA_NAME = D.AREA_NAME AND
M.VIRTUAL_COVERED = 'C'
UNION
SELECT
A.DBID,
A.AREA_NAME AS AREA,
D.BLOCK_LENGTH AS BLOCK,
D.IN_USE_BLOCKS AS #BLOCKS,
A.LOGICAL_READS AS LGLREADS,
A.LOGICAL_WRITES AS LGLWRITES,
A.PHYSICAL_READS AS PHYSREADS,
PHYSICAL_WRITES AS PHYSWRITES,
0 AS M_MEGS,
0 AS M_TOT_READS,
0 AS M_PHYS_READS,
0 AS M_SAVED_READS,
0 AS M_READS_PER_MEG,
(((D.BLOCK_LENGTH / 1024) * D.IN_USE_BLOCKS)/ 1024) AS POT_MRDF_MEGS,
(A.PHYSICAL_READS / (((D.BLOCK_LENGTH / 1024) * D.IN_USE_BLOCKS)/ 1024))
AS POT_SAVEDIO_MEG
FROM SYSADM.MUF_AREA_STATS A, SYSADM.DIR_DATASET D
WHERE
A.PHYSICAL_READS > 1 AND
((D.BLOCK_LENGTH / 1024) * D.IN_USE_BLOCKS) > 1024 AND
A.DBID = D.DBID AND
A.AREA_NAME = D.AREA_NAME
ORDER BY 13 DESC, 15 DESC;
In the following example, the data was returned from Query 1. It reports the current MRDF settings. Here we have 100 MB devoted to covering five tables, split equally at 20 MB each. In addition to reporting the information collected in the dynamic system table, the query also returns an actual calculation of saved read I/Os (M_SAVED_READS) divided by the megabytes of memory in use (MRDF_MEGS). Only rows with current covered specifications are listed.
Query 1:
| DBID | 125 | 125 | 422 | 321 | 311 |
| AREA | BDO | GPS | ARM | MTT | IXX |
| BLOCK | 4096 | 4096 | 4096 | 4096 | 4096 |
| #BLOCKS | 51862 | 43126 | 9134 | 96742 | 27021 |
| LGLREADS | 39051652 | 27476450 | 4347346 | 2885808 | 31738152 |
| LGLWRITES | 103070 | 75046 | 4999 | 6294 | 101997 |
| PHYSREADS | 3704988 | 2122773 | 101838 | 996637 | 434359 |
| PHYSWRITES | 7782 | 6532 | 129 | 890 | 7793 |
| MRDF_MEGS | 20 | 20 | 20 | 20 | 20 |
| M_TOT_READS | 4108751 | 2381949 | 251488 | 1041806 | 439199 |
| M_PHYS_READS | 3704988 | 2122773 | 101858 | 996637 | 434359 |
| M_SAVED_READS | 403763 | 259176 | 149630 | 45169 | 4840 |
| READS_PER_MEG | 20188.15 | 12958.8 | 7481.5 | 2258.45 | 242 |
In the following example, the data was returned from Query 2. It reports the current MRDF settings (same as Query 1) but also provides potential I/O savings for those datasets with significant physical read I/O activity. You can compare both the existing MRDF COVERED efficiency as well as potential areas.
Query 2:
| DBID | 125 | 125 | 422 | 321 | 311 |
| AREA | BDO | GPS | ARM | MTT | IXX |
| BLOCK | 4096 | 4096 | 4096 | 4096 | 4096 |
| #BLOCKS | 51862 | 43126 | 9134 | 96742 | 27021 |
| LGLREADS | 39053901 | 27476451 | 4347346 | 2885812 | 31738216 |
| LGLWRITES | 103342 | 75049 | 4999 | 6294 | 101997 |
| PHYSREADS | 3705291 | 2122774 | 101838 | 996638 | 434368 |
| PHYSWRITES | 7797 | 6533 | 129 | 890 | 7793 |
| M_MEGS | 20 | 20 | 20 | 20 | 20 |
| M_TOT_READS | 4109059 | 2381950 | 251488 | 1041807 | 439208 |
| M_PHYS_READS | 3705291 | 2122774 | 101858 | 996638 | 434368 |
| M_SAVED_READS | 403768 | 259176 | 149630 | 45169 | 4840 |
| M_READS_PER_MEG | 20188.4 | 12958.8 | 7481.5 | 2258.45 | 242 |
| POT_MRDF_MEGS | 0 | 0 | 0 | 0 | 0 |
| POT_SAVEDIO_MEG | 0 | 0 | 0 | 0 | 0 |
| DBID | 511 | 413 | 91 | 91 | 210 |
| AREA | VHM | FMM | TAB | IXX | PRS |
| BLOCK | 4096 | 4096 | 4096 | 4096 | 4096 |
| #BLOCKS | 374 | 477 | 279 | 502 | 1125 |
| LGLREADS | 4201594 | 1.19E+08 | 6779908 | 15098635 | 1577941 |
| LGLWRITES | 4 | 355 | 238 | 127 | 7039 |
| PHYSREADS | 104786 | 98431 | 97329 | 78348 | 278558 |
| PHYSWRITES | 4 | 128 | 104 | 24 | 564 |
| M_MEGS | 0 | 0 | 0 | 0 | 0 |
| M_TOT_READS | 0 | 0 | 0 | 0 | 0 |
| M_PHYS_READS | 0 | 0 | 0 | 0 | 0 |
| M_SAVED_READS | 0 | 0 | 0 | 0 | 0 |
| M_READS_PER_MEG | 0 | 0 | 0 | 0 | 0 |
| POT_MRDF_MEGS | 1 | 1 | 1 | 1 | 4 |
| POT_SAVEDIO_MEG | 104786 | 98431 | 97329 | 78348 | 69639 |
| DBID | 99 | 311 | 631 | 127 | 311 |
| AREA | REQ | SUP | IXX | IXX | PRO |
| BLOCK | 4096 | 4096 | 4096 | 4096 | 4096 |
| #BLOCKS | 1674 | 438 | 457 | 432 | 26960 |
| LGLREADS | 1702784 | 475444 | 9657560 | 1496433 | 12765320 |
| LGLWRITES | 360 | 571 | 763 | 80 | 7318 |
| PHYSREADS | 336167 | 49647 | 46429 | 35794 | 2281567 |
| PHYSWRITES | 138 | 155 | 259 | 38 | 1127 |
| M_MEGS | 0 | 0 | 0 | 0 | 0 |
| M_TOT_READS | 0 | 0 | 0 | 0 | 0 |
| M_PHYS_READS | 0 | 0 | 0 | 0 | 0 |
| M_SAVED_READS | 0 | 0 | 0 | 0 | 0 |
| M_READS_PER_MEG | 0 | 0 | 0 | 0 | 0 |
| POT_MRDF_MEGS | 6 | 1 | 1 | 1 | 105 |
| POT_SAVEDIO_MEG | 56027 | 49647 | 46429 | 35794 | 21729 |
If you examine the results from Query 1, notice that the best performing area is BDO in DBID 125. It gets 20188 reads per megabye. The worst area is the IXX for DBID 311 at only 242 reads per megabyte. If you still want to allocate 100 MB to covered areas, the results of Query 2 shows that this 100 MB is best utilized by covering different areas than are now covered.
The last two rows of the report, POT_MRDF_MEGS and POT_SAVEDIO_MEG , are used to choose the best candidates and the quantity to assign each one. The currently covered tables are listed in the Query 2 results as having zero potential MRDF megabytes and zero potential saved I/O per megabyte.
The MUF startup values based on these criteria would be:
COVERED VHM511,1M,FIRST COVERED FMM413,1M,FIRST COVERED TAB091,1M,FIRST COVERED IXX091,1M,FIRST COVERED PRS210,4M,FIRST COVERED REQ099,6M,FIRST COVERED SUP311,1M,FIRST COVERED IXX631,1M,FIRST COVERED IXX127,1M,FIRST COVERED PRO311,83M,FIRST
These tables all will provide a better performance savings than the best table we now have covered as measured by potential savings per megabyte.
Keep in mind that the potential savings are based on perfect conditions where a minimum number of blocks are continually reread. Because we live in an imperfect world, the actual savings will be less than the potential. The percent of potential realized depends on how much the data is "re-accessed" and if the covered area is "purged" due to the database being closed to MUF or a MUF cycle. Whatever actual percentage of the potential savings results, the calculated potentials should still point you to the right tables for the best performance.
MRDF covered area assignment, as with all tuning, is an ongoing exercise that needs to be re-measured and adjusted as needed.
You could just use Query 2 to review and never issue Query 1. It provides everything in one report. We think it is worthwhile, though, to use Query 1 to review the current assignments.
Happy tuning.
Environment
Component: DB
Feedback
