How do I determine which data/index areas are the best candidates to be implemented as covered

Document ID : KB000055147
Last Modified Date : 14/02/2018
Show Technical Document Details

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:

DBID125125422321311
AREABDOGPSARMMTTIXX
BLOCK40964096409640964096
#BLOCKS518624312691349674227021
LGLREADS39051652274764504347346288580831738152
LGLWRITES1030707504649996294101997
PHYSREADS37049882122773101838996637434359
PHYSWRITES778265321298907793
MRDF_MEGS2020202020
M_TOT_READS410875123819492514881041806439199
M_PHYS_READS37049882122773101858996637434359
M_SAVED_READS403763259176149630451694840
READS_PER_MEG20188.1512958.87481.52258.45242

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:

DBID125125422321311
AREABDOGPSARMMTTIXX
BLOCK40964096409640964096
#BLOCKS518624312691349674227021
LGLREADS39053901274764514347346288581231738216
LGLWRITES1033427504949996294101997
PHYSREADS37052912122774101838996638434368
PHYSWRITES779765331298907793
M_MEGS2020202020
M_TOT_READS410905923819502514881041807439208
M_PHYS_READS37052912122774101858996638434368
M_SAVED_READS403768259176149630451694840
M_READS_PER_MEG20188.412958.87481.52258.45242
POT_MRDF_MEGS00000
POT_SAVEDIO_MEG00000


DBID5114139191210
AREAVHMFMMTABIXXPRS
BLOCK40964096409640964096
#BLOCKS3744772795021125
LGLREADS42015941.19E+086779908150986351577941
LGLWRITES43552381277039
PHYSREADS104786984319732978348278558
PHYSWRITES412810424564
M_MEGS00000
M_TOT_READS00000
M_PHYS_READS00000
M_SAVED_READS00000
M_READS_PER_MEG00000
POT_MRDF_MEGS11114
POT_SAVEDIO_MEG10478698431973297834869639


DBID99311631127311
AREAREQSUPIXXIXXPRO
BLOCK40964096409640964096
#BLOCKS167443845743226960
LGLREADS17027844754449657560149643312765320
LGLWRITES360571763807318
PHYSREADS3361674964746429357942281567
PHYSWRITES138155259381127
M_MEGS00000
M_TOT_READS00000
M_PHYS_READS00000
M_SAVED_READS00000
M_READS_PER_MEG00000
POT_MRDF_MEGS6111105
POT_SAVEDIO_MEG5602749647464293579421729

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.