DB2 EXPLAIN - 2

******Please read my post on generation of explain plan before proceeding with analysis*****

ANALYZING EXPLAIN PLAN -  DB2

Components of explain plan

Below are the major blocks of explain plan.
1.Instance Details

2.Original Query and Optimized Query

3.Access Plan

4.Plan Details

             Here are few important points to be noted while analyzing the explain plan. Access plan gives the complete picture of path taken by the DB2 engine and cost. Cost provided is in Timerons and is completely different than the human time metrics. Timerons is hardware signal unit and does not have any relation or formula to convert it to normal time. It is used to know the relative execution time for the query. 

Components of Access Plan

               Access plan is to be analyzed from right to left and bottom to top. First I will try to explain the components of access plan.

1. The numerical in RED indicates the cardinality of the rows affected.

2. The Green value is the name of activity performed in access path (i.e. IXSCAN - Index Scan) , the second value in green on right side is name of schema and table (DP-TABLE: Data partitioned Table).

3.The numerical in Pink indicates the cost of the activity (i.e index scan in the below case) in access plan. 

4. The Numerical in Blue mentioned in braces is the process id which can later be used analyze the activity in detail from Plan Details in explain plan.

              In the below access plan DB2 engine is performing the index scan and then retrieving those rows from table using RID. The rows fetched are grouped and returned.To Know more about the Index scan in below access plan we can drill the plan details section by using process id (i.e 4 for the Index scan). If no.of rows returned by index scan is high (i.e if cardinality is high) or if the timerons taken is high then the performance is bad. Usually it is assumed that if the number of timerons is in hundreds then its good. If the timerons is in millions then the performance is real bad. These parameters vary for different queries in different scenarios.If you find any table scans (for tables with more than 10k rows) then it is a bad performance. we have to make sure that there are no table scans for huge tables in access plan.

              Once we analyze the access plan we can know which index is taking highest time or which index is using more cardinality. The major solution for most of the performance enhancement is either SQL tuning or Index creation or both in few cases. I will cover these topics in my coming posts.

Example for Access Plan

Total Cost: 25651.8

Query Degree: 0

               Rows 

              RETURN

              (   1)

               Cost 

                I/O 

                |

                 1 

              GRPBY 

              (   2)

              25651.8 

               13505 

                |

              73880.7 

              FETCH 

              (   3)

              25646.5 

               13505 

           /----+-----\

       421.19       1.12048e+09 

       IXSCAN   DP-TABLE: <SCHEMA_NAME>  

       (   4)               <TABLE_NAME>

       12.8697          

          1 

         |

     1.12048e+09 

 DP-INDEX: SYSIBM  

  <INDEX_NAME>

**Please post your valuable suggestions regarding query tuning and comments regarding this article.