DB2 : Examining Query Plans

by bitznbitez

Anytime you have a query that is performing poorly the explain tool will come in handy.   It will show you how each table is accessed, how joins are performed, etc.   You can see if it is an effecient plan or not, and change table layout, indexing, etc. in order to improve the query. 

In DB2 there are several explain tools.   In the command center is the graphical one, which is pretty nice.   An old depricated one is dynexpln but it does still function.

The one I use most often at the command line is db2expln.   I like it because it generates a textual graph along with the text of the sql and the details of how each step is performed.   The options I generally use are as follows  :

  • -d database name
  • -t  (output to terminal)
  • -z ;  ( sets delimiter to ; )
  • -g ( generates the graph )

-f filename ( the file that has the SQL I am explaining )

Sometimes I also use -nostats to make the graph at the bottom cleaner if it is very complex.   This simply doesn’t display as much info in the graph.   The graph at the bottom is worth its weight in gold.   Without the graph, as in the days of dynexpln, you would have to construct the graph mentally from the various steps and what was reading from and writing to each queue etc.   It could be done but it was not always easy for a complex query.

Here is a sample run of db2expln for a bogus query against the IBM supplied SAMPLE Database.   As always I display something from sample as the stuff from the office cannot be displayed.  

db2inst1@blackwater ~ $ db2expln -d sample -t -z ';' -g -f x.sql 

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5

	Partition Parallel       = No
	Intra-Partition Parallel = No

	SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "DB2INST1"

Statement:

  select *
  from employee e1 , emp_act e2 , emp_photo e3 , emp_resume e4
  where e1.empno =e2.empno and e1.empno =e3.empno and e1.empno =
          e4.empno

Section Code Page = 1208

Estimated Cost = 30.292149
Estimated Cardinality = 38.933334

Access Table Name = DB2INST1.EMPLOYEE  ID = 2,6
|  Index Scan:  Name = DB2INST1.PK_EMPLOYEE  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: EMPNO (Ascending)
|  #Columns = 14
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  Evaluate Predicates Before Locking for Key
|  #Key Columns = 0
|  |  Start Key: Beginning of Index
|  |  Stop Key: End of Index
|  Data Prefetch: Eligible 0
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
Merge Join
|  Access Table Name = DB2INST1.EMP_PHOTO  ID = 2,7
|  |  #Columns = 3
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  Insert Into Sorted Temp Table  ID = t1
|  |  #Columns = 3
|  |  #Sort Key Columns = 1
|  |  |  Key 1: EMPNO (Ascending)
|  |  Sortheap Allocation Parameters:
|  |  |  #Rows     = 8.000000
|  |  |  Row Width = 132
|  |  Piped
|  Access Temp Table  ID = t1
|  |  #Columns = 3
|  |  Relation Scan
|  |  |  Prefetch: Eligible
Hash Join
|  Estimated Build Size: 4000
|  Estimated Probe Size: 4000
|  Access Table Name = DB2INST1.EMP_RESUME  ID = 2,8
|  |  #Columns = 3
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  Hash Join
|  |  Estimated Build Size: 4000
|  |  Estimated Probe Size: 4000
|  |  Access Table Name = DB2INST1.EMPPROJACT  ID = 2,11
|  |  |  #Columns = 6
|  |  |  Skip Inserted Rows
|  |  |  Avoid Locking Committed Data
|  |  |  Currently Committed for Cursor Stability
|  |  |  May participate in Scan Sharing structures
|  |  |  Scan may start anywhere and wrap, for completion
|  |  |  Fast scan, for purposes of scan sharing management
|  |  |  Scan can be throttled in scan sharing management
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  |  |  Lock Intents
|  |  |  |  Table: Intent Share
|  |  |  |  Row  : Next Key Share
|  |  |  Sargable Predicate(s)
|  |  |  |  Process Probe Table for Hash Join
Return Data to Application
|  #Columns = 26

End of section

Optimizer Plan:

                          Rows
                        Operator
                          (ID)
                          Cost   

                        38.9333
                          n/a
                        RETURN
                         ( 1)
                        30.2921
                          |
                        38.9333
                          n/a
                        HSJOIN
                         ( 2)
                        30.2921
                /------/       \------\
         19.4667                          8
           n/a                           n/a
         HSJOIN                        MSJOIN
          ( 3)                          ( 6)
         15.1511                       15.1397
        /       \                     /       \---\
     73          8                 42              *
     n/a        n/a                n/a            |
   TBSCAN      TBSCAN             FETCH            8
    ( 4)        ( 5)              ( 7)            n/a
   7.58102     7.5678            7.57941        TBSCAN
     |           |              /       \        (10)
     73          8            42         42     7.56846
    n/a         n/a          n/a        n/a       |
 Table:      Table:         IXSCAN    Table:       8
 DB2INST1    DB2INST1        ( 8)     DB2INST1    n/a
 EMPPROJACT  EMP_RESUME    0.013254   EMPLOYEE   SORT
                             |                   (11)
                             42                 7.56831
                         Index:                   |
                         DB2INST1                 8
                         PK_EMPLOYEE             n/a
                                                TBSCAN
                                                 (12)
                                                7.5678
                                                 |
                                                  8
                                                 n/a
                                              Table:
                                              DB2INST1
                                              EMP_PHOTO

ff

Advertisements