Thursday, December 29, 2011

Find if program is doing table scan or index scan

When a program selects table scan for a DB2 table, it processes all the records from that table and this will ok if the table is small, but if the table is big it will cause serious performance issue. DB2 resorts to table scan, when the WHERE condition in SQL query does not use any index defined on the table.

If the WHERE condition in the query uses all the fields from an index or few fields from an index, it will perform index scan and index scan is more efficient from performance standpoint. When the WHERE condition uses few fields from an index, it must refer to top order fields of the index, so DB2 can select index scan. For example, if an index has 5 fields, then WHERE condition should refer to 1st field, or 1st &2nd, or 1st & 2nd & 3rd fields and so on.

The output of EXPLAIN is stored in the PLAN_TABLE. We can find if a program is doing table scan or index scan by analyzing the records from this table.

The below query retrieves all data related to table space scan for latest package of a program. ACCESSTYPE column from the PLAN_TABLE tells if program is doing table scan or index scan.

    SELECT                                                             
      A.QUERYNO                                                        
     ,SUBSTR(A.PROGNAME,1,8) AS PROGNAME                               
     ,SUBSTR(A.COLLID,1,8)   AS COLLID                                 
     ,SUBSTR(A.TNAME,1,20)   AS TAB_NAME                               
     ,A.ACCESSTYPE                                                     
     ,A.TIMESTAMP                                                      
   FROM MYPRODDB.PLAN_TABLE AS A                                         
    WHERE (A.COLLID = 'MYCOLLID')  AND
          (A.PROGNAME = ‘TESTPGM’) AND          
          (A.ACCESSTYPE  = 'R') AND                
           A.BIND_TIME =                                               
           (SELECT MAX(B.BIND_TIME)                                    
              FROM MYPRODDB.PLAN_TABLE AS B                              
             WHERE B.PROGNAME = A.PROGNAME AND                         
                   B.COLLID   = A.COLLID);                              

The various possible values of ACCESSTYPE column is given below.

Column Name       Description
ACCESSTYPE       The method of accessing the table
I                By an index  
I1               By a one-fetch index scan
N                By an index scan when the matching predicate contains the IN keyword
R                By a table space scan
M                By a multiple index scan (followed by MX, MI, or MU)
MX               By an index scan on the index named in ACCESSNAME
MI               By an intersection of multiple indexes
MU               By a union of multiple indexes
blank            Not applicable to the current row


The below query retrieves all data related to index space scan for latest package of a program. COLCOUNT column has number of columns present in an index.  MATCHCOLS tells how many columns of the index are used in matching

    SELECT                                                             
      A.QUERYNO                                                        
     ,SUBSTR(A.PROGNAME,1,8) AS PROGNAME                               
     ,SUBSTR(A.COLLID,1,8)   AS COLLID                                 
     ,SUBSTR(A.TNAME,1,20)   AS TAB_NAME                               
     ,A.ACCESSTYPE                                                     
     ,SUBSTR(A.ACCESSNAME,1,15) AS INDEX_NAME                          
     ,X.COLCOUNT                                                       
     ,A.MATCHCOLS                                                      
   FROM  MYPRODDB.PLAN_TABLE AS A,                                        
        SYSIBM.SYSINDEXES AS X                                         
    WHERE (A.COLLID = 'MYCOLLID') AND
          (A.PROGNAME = ‘TESTPGM’) AND          
          ((X.TBCREATOR = 'MYPRODDB'      AND                          
            X.TBNAME    =  A.TNAME        AND                          
            X.CREATOR   = 'MYPRODDB'      AND                          
            X.NAME      =  A.ACCESSNAME   AND                          
            A.ACCESSTYPE IN('I','I1','N','M','MX','MI','MU'))) AND     
           A.BIND_TIME =                        
           (SELECT MAX(B.BIND_TIME)             
              FROM  MYPRODDB.PLAN_TABLE AS B       
             WHERE B.PROGNAME = A.PROGNAME AND  
                   B.COLLID   = A.COLLID)       
      WITH UR;                                  

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.