Results 1 to 9 of 9

Thread: explain plan

  1. #1
    Join Date
    Mar 2014
    Posts
    14

    Unanswered: explain plan

    Hi,

    We have a couple of queries which run on two similar servers but on one of the servers the query runs for 1 hr and the other for 3.5 hrs. we did expln plan on both the servers on one of the servers I see there are system temp indexes where as I don't see them in the graph. One the other server the explain plan is entirely different, there are no SYSTEM.TEMPORARY_INDEX.

    Any clue from where these indexes came up.

    SYSTEM.TEMPORARY_INDEX_1
    SYSTEM.TEMPORARY_INDEX_2

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If your db2-server is running on Linux/Unix/Windows, Look in the syscat.indexes to see who created those indexes (owner/ownertype/definer) and when the indexes got created (createtime).
    Consider adding the indexes to the database that lacks them.
    Then make sure the runstats profile (or the runstats command options) are the same on both servers and that the runstats are up to date on both servers.

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    hi, db2_nube.
    Could you plz post the both execution plans here?

  4. #4
    Join Date
    Mar 2014
    Posts
    14
    SELECT DNH.STY_NBR , DNH.STR_NBR , DNH.SZ_CD , DNH.CORP_ID ,
    DNH.MDSE_CO_ID , DNH.RCMD_WK_OF_SUP_QTY ,
    DNH.MIN_MSTK_QTY , DNH.MAX_MSTK_QTY ,
    MPM.DC_STR_LTME_NBR , ROP.NBR_WK_PRJ_CNT ,
    IWF.FCST_WK01_QTY , IWF.FCST_WK02_QTY ,
    IWF.FCST_WK03_QTY , IWF.FCST_WK04_QTY ,
    IWF.FCST_WK05_QTY , IWF.FCST_WK06_QTY ,
    IWF.FCST_WK07_QTY , IWF.FCST_WK08_QTY ,
    IWF.FCST_WK09_QTY , IWF.FCST_WK10_QTY ,
    IWF.FCST_WK11_QTY , IWF.FCST_WK12_QTY ,
    IWF.FCST_WK13_QTY , IWF.FCST_WK14_QTY ,
    IWF.FCST_WK15_QTY , IWF.FCST_WK16_QTY ,
    IWF.FCST_WK17_QTY , IWF.FCST_WK18_QTY ,
    IWF.FCST_WK19_QTY , IWF.FCST_WK20_QTY ,
    IWF.FCST_WK21_QTY , IWF.FCST_WK22_QTY ,
    IWF.FCST_WK23_QTY , IWF.FCST_WK24_QTY ,
    IWF.FCST_WK25_QTY , IWF.FCST_WK26_QTY ,
    IWF.FCST_WK27_QTY , IWF.FCST_WK28_QTY ,
    IWF.FCST_WK29_QTY , IWF.FCST_WK30_QTY ,
    IWF.FCST_WK31_QTY , IWF.FCST_WK32_QTY ,
    IWF.FCST_WK33_QTY , IWF.FCST_WK34_QTY ,
    IWF.FCST_WK35_QTY , IWF.FCST_WK36_QTY ,
    IWF.FCST_WK37_QTY , IWF.FCST_WK38_QTY ,
    IWF.FCST_WK39_QTY , IWF.FCST_WK40_QTY ,
    IWF.FCST_WK41_QTY , IWF.FCST_WK42_QTY ,
    IWF.FCST_WK43_QTY , IWF.FCST_WK44_QTY ,
    IWF.FCST_WK45_QTY , IWF.FCST_WK46_QTY ,
    IWF.FCST_WK47_QTY , IWF.FCST_WK48_QTY ,
    IWF.FCST_WK49_QTY , IWF.FCST_WK50_QTY ,
    IWF.FCST_WK51_QTY , IWF.FCST_WK52_QTY ,
    IWF.FCST_WK53_QTY , CHAR(IWF.LST_UPDT_DT, ISO)
    FROM TDNH_DMD_HISTORY DNH , TIWF_INV_WKLY_FCST IWF ,
    TROP_PRD_RORD_PARM ROP , TMPM_MSTK_PARMS MPM ,
    LOC_LOCATIONS LOC
    WHERE DNH.STY_NBR =IWF.STY_NBR AND DNH.STR_NBR =IWF.STR_NBR AND
    DNH.SZ_CD =IWF.SZ_CD AND DNH.STY_NBR =MPM.STY_NBR AND
    DNH.STR_NBR =MPM.STR_NBR AND DNH.DMD_YLY_QTY > 0 AND
    DNH.STY_NBR =ROP.STY_NBR AND ROP.RORD_PROC_IND =:H00001
    AND DNH.STR_NBR =LOC.LOC_LOC_NBR AND LOC_LOC_STAT_CD <
    '8' AND IWF.LST_UPDT_DT >=:H00007
    FOR
    FETCH ONLY
    WITH UR

    Statement Isolation Level = Uncommitted Read

    Section Code Page = 819

    Estimated Cost = 2950222.500000
    Estimated Cardinality = 61091.613281

    Zigzag Join
    | Zigzag Join Dimension 1
    | No Payload In Probe
    | Access Table Name = DINV0001.TROP_PRD_RORD_PARM ID = 1155,4
    | | #Columns = 2
    | | 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
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Predicate(s)
    | | | #Predicates = 1
    | | | Insert Into Index Only Temp Table ID = t1
    | | | | #Columns = 2
    | Index Only Temp Table Completion ID = t1
    | Access Temp Table ID = t1
    | | Temporary Index Scan: Name = SYSTEM.TEMPORARY_INDEX_1
    | | | Random Access
    | | | Index Columns:
    | | | | 1: Column 1 (Ascending)
    | | | | 2: Column 2 (Include Column)
    | | #Columns = 2
    | | #Key Columns = 1
    | | | Start Key: Inclusive Value
    | | | | 1: ?
    | | | Stop Key: End of Index
    | | Index-Only Access
    | | Index Prefetch: None
    | Zigzag Join Dimension 2
    | No Payload In Probe
    | Access Table Name = DFACLT01.LOC_LOCATIONS ID = 726,4
    | | Index Scan: Name = DFACLT01.ILOC4_LOC_NBR ID = 3
    | | | Regular Index (Not Clustered)
    | | | Index Columns:
    | | | | 1: LOC_LOC_NBR (Ascending)
    | | | | 2: SITE_TYP_CD (Ascending)
    | | | | 3: LOC_LOC_STAT_CD (Ascending)
    | | #Columns = 1
    | | #Key Columns = 0
    | | | Start Key: Beginning of Index
    | | | Stop Key: End of Index
    | | Index-Only Access
    | | Index Prefetch: Sequential(43), Readahead
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Index Predicate(s)
    | | | #Predicates = 1
    | | | Insert Into Integer Sorted Temp Table ID = t2
    | | | | #Columns = 1
    | | | | Input In Sorted Order Used To Skip Sorting Of Data
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 5457.384277
    | | | | | Row Width = 4
    | Integer Sorted Temp Table Completion ID = t2
    | Access Temp Table ID = t2
    | | Temporary Index Scan: Name = SYSTEM.TEMPORARY_INDEX_2
    | | | Random Access
    | | | Index Columns:
    | | | | 1: Column 1 (Ascending)
    | | #Columns = 1
    | | #Key Columns = 1
    | | | Start Key: Inclusive Value
    | | | | 1: ?
    | | | Stop Key: End of Index
    | | Index-Only Access
    | | Index Prefetch: None
    | Zigzag Join Fact
    | Access Table Name = DINV0001.TDNH_DMD_HISTORY ID = -6,-32758
    | | Index Scan: Name = DINV0001.IDNH4_STY_SZ_STR ID = 4
    | | | Regular Index (Not Clustered)
    | | | Index Columns:
    | | | | 1: STY_NBR (Ascending)
    | | | | 2: SZ_CD (Ascending)
    | | | | 3: STR_NBR (Ascending)
    | | #Columns = 0
    | | Data-Partitioned Table
    | | All data partitions will be accessed
    | | #Key Columns = 3
    | | | Start Key: Inclusive Value
    | | | | 1: ?
    | | | | 2: [GAP Unconstrained]
    | | | | 3: ?
    | | | Stop Key: Inclusive Value
    | | | | 1: ?
    | | | | 2: [GAP Unconstrained]
    | | | | 3: ?
    | | Index-Only Access
    | | Index Prefetch: Sequential(7), Readahead
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Index Predicate(s)
    | | | Insert Into Integer Sorted Temp Table ID = t3
    | | | | #Columns = 1
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 2633925.000000
    | | | | | Row Width = 20
    | | | | Duplicate Elimination
    Integer Sorted Temp Table Completion ID = t3
    List Prefetch Preparation
    | Access Table Name = DINV0001.TDNH_DMD_HISTORY ID = -6,-32758
    | | #Columns = 8
    | | Data-Partitioned Table
    | | RID List Fetch Scan
    | | All data partitions will be accessed
    | | Fetch Using Prefetched List
    | | | Prefetch: Eligible
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Predicate(s)
    | | | #Predicates = 1
    | | | Insert Into Sorted Temp Table ID = t4
    | | | | #Columns = 11
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: STY_NBR (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 2633489.000000
    | | | | | Row Width = 52
    | | | | Piped
    Sorted Temp Table Completion ID = t4
    Access Temp Table ID = t4
    | #Columns = 11
    | Relation Scan
    | | Prefetch: Eligible
    Nested Loop Join
    | Access Table Name = DINV0001.TIWF_INV_WKLY_FCST ID = 992,4
    | | Index Scan: Name = DINV0001.IIWF1_INV_WKLY_FCS ID = 1
    | | | Regular Index (Clustered)
    | | | Index Columns:
    | | | | 1: STY_NBR (Ascending)
    | | | | 2: STR_NBR (Ascending)
    | | | | 3: SZ_CD (Ascending)
    | | #Columns = 56
    | | Single Record
    | | Fully Qualified Unique Key
    | | #Key Columns = 3
    | | | Start Key: Inclusive Value
    | | | | 1: ?
    | | | | 2: ?
    | | | | 3: ?
    | | | Stop Key: Inclusive Value
    | | | | 1: ?
    | | | | 2: ?
    | | | | 3: ?
    | | Data Prefetch: Sequential(1455549)
    | | Index Prefetch: Sequential(102310), Readahead
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Predicate(s)
    | | | #Predicates = 1
    Merge Join
    | Early Out: Single Match Per Outer Row
    | Access Table Name = DINV0001.TMPM_MSTK_PARMS ID = 1035,4
    | | #Columns = 3
    | | 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
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Predicate(s)
    | | | Insert Into Sorted Temp Table ID = t5
    | | | | #Columns = 3
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: STY_NBR (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 3933937.000000
    | | | | | Row Width = 24
    | | | | Piped
    | Sorted Temp Table Completion ID = t5
    | Access Temp Table ID = t5
    | | #Columns = 3
    | | Relation Scan
    | | | Prefetch: Eligible
    | Residual Predicate(s)
    | | #Predicates = 1
    Return Data to Application
    | #Columns = 64

    End of section


    graph doesn't show the temp indexes

  5. #5
    Join Date
    Mar 2014
    Posts
    14
    same query on different server

    Access Table Name = DINV0001.TROP_PRD_RORD_PARM ID = 1155,4
    | #Columns = 2
    | May participate in Scan Sharing structures
    | Scan may start anywhere and wrap, for completion
    | Scan can be throttled in scan sharing management
    | Relation Scan
    | | Prefetch: Eligible
    | Isolation Level: Uncommitted Read
    | Lock Intents
    | | Table: Intent None
    | | Row : None
    | Sargable Predicate(s)
    | | #Predicates = 1
    Nested Loop Join
    | Piped Inner
    | Access Table Name = DINV0001.TIWF_INV_WKLY_FCST ID = 992,4
    | | Index Scan: Name = DINV0001.IIWF1_INV_WKLY_FCS ID = 1
    | | | Regular Index (Clustered)
    | | | Index Columns:
    | | | | 1: STY_NBR (Ascending)
    | | | | 2: STR_NBR (Ascending)
    | | | | 3: SZ_CD (Ascending)
    | | #Columns = 0
    | | #Key Columns = 1
    | | | Start Key: Inclusive Value
    | | | | 1: ?
    | | | Stop Key: Inclusive Value
    | | | | 1: ?
    | | Index-Only Access
    | | Index Prefetch: Sequential(13), Readahead
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Index Predicate(s)
    | | | Insert Into Integer Sorted Temp Table ID = t1
    | | | | #Columns = 1
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 1791.000000
    | | | | | Row Width = 20
    | | | | Duplicate Elimination
    | Integer Sorted Temp Table Completion ID = t1
    | List Prefetch Preparation
    | | Access Table Name = DINV0001.TIWF_INV_WKLY_FCST ID = 992,4
    | | | #Columns = 57
    | | | RID List Fetch Scan
    | | | Fetch Using Prefetched List
    | | | | Prefetch: 199 Pages
    | | | Isolation Level: Uncommitted Read
    | | | Lock Intents
    | | | | Table: Intent None
    | | | | Row : None
    | | | Sargable Predicate(s)
    | | | | #Predicates = 2
    Insert Into Sorted Temp Table ID = t2
    | #Columns = 59
    | #Sort Key Columns = 1
    | | Key 1: STR_NBR (Ascending)
    | Sortheap Allocation Parameters:
    | | #Rows = 184074.000000
    | | Row Width = 408
    | Piped
    Access Temp Table ID = t2
    | #Columns = 59
    | Relation Scan
    | | Prefetch: Eligible
    Merge Join
    | Early Out: Single Match Per Outer Row
    | Access Table Name = DFACLT01.LOC_LOCATIONS ID = 726,4
    | | Index Scan: Name = DFACLT01.ILOC4_LOC_NBR ID = 3
    | | | Regular Index (Not Clustered)
    | | | Index Columns:
    | | | | 1: LOC_LOC_NBR (Ascending)
    | | | | 2: SITE_TYP_CD (Ascending)
    | | | | 3: LOC_LOC_STAT_CD (Ascending)
    | | #Columns = 1
    | | #Key Columns = 0
    | | | Start Key: Beginning of Index
    | | | Stop Key: End of Index
    | | Index-Only Access
    | | Index Prefetch: Sequential(43), Readahead
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Index Predicate(s)
    | | | #Predicates = 1
    Insert Into Sorted Temp Table ID = t3
    | #Columns = 60
    | #Sort Key Columns = 1
    | | Key 1: (Ascending)
    | Sortheap Allocation Parameters:
    | | #Rows = 113387.000000
    | | Row Width = 412
    | Piped
    Access Temp Table ID = t3
    | #Columns = 60
    | Relation Scan
    | | Prefetch: Eligible
    Merge Join
    | Early Out: Single Match Per Outer Row
    | Access Table Name = DINV0001.TMPM_MSTK_PARMS ID = 1035,4
    | | #Columns = 3
    | | 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
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Predicate(s)
    | | | Insert Into Sorted Temp Table ID = t4
    | | | | #Columns = 3
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: STY_NBR (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 3921482.000000
    | | | | | Row Width = 24
    | | | | Piped
    | Sorted Temp Table Completion ID = t4
    | Access Temp Table ID = t4
    | | #Columns = 3
    | | Relation Scan
    | | | Prefetch: Eligible
    | Residual Predicate(s)
    | | #Predicates = 1
    Merge Join
    | Early Out: Single Match Per Outer Row
    | Access Table Name = DINV0001.TDNH_DMD_HISTORY ID = -6,-32758
    | | #Columns = 8
    | | Data-Partitioned Table
    | | 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
    | | All data partitions will be accessed
    | | Relation Scan
    | | | Prefetch: Eligible
    | | Isolation Level: Uncommitted Read
    | | Lock Intents
    | | | Table: Intent None
    | | | Row : None
    | | Sargable Predicate(s)
    | | | #Predicates = 1
    | | | Insert Into Sorted Temp Table ID = t5
    | | | | #Columns = 8
    | | | | #Sort Key Columns = 1
    | | | | | Key 1: STY_NBR (Ascending)
    | | | | Sortheap Allocation Parameters:
    | | | | | #Rows = 3843903.000000
    | | | | | Row Width = 36
    | | | | Piped
    | Sorted Temp Table Completion ID = t5
    | Access Temp Table ID = t5
    | | #Columns = 8
    | | Relation Scan
    | | | Prefetch: Eligible
    | Residual Predicate(s)
    | | #Predicates = 2
    Return Data to Application
    | #Columns = 64

    End of section

  6. #6
    Join Date
    Mar 2014
    Posts
    14
    my question is why is the optimizer creating temp table and indexes in one environment and not in the other.

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    it is normal to have different access plans (for the same query) on different systems - happens because of differences between the systems (cpu's, ram, bufferpool sizes, runstats profiles, different cardinalities, different settings /configs etc etc).
    You need to figure out the difference that is the determining factor in this case.

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    hi, db2_nube
    I think the first plan used the new join method "zigzag join" which is available at db2 v10.1 and later.
    There is some description for zzjoin in the db2 information center:
    Zigzag join access plan examples

    The temorary index is created by optimizer automaticlly. When the qulified rows in the fact table search backup to the dimension table , db2 make these index temporarily to avoid table scan.

    The TBSCAN(6) and TBSCAN(9) operators show the following information:
    IS_TEMP_INDEX : True/False
    The scan builds an index over the temp for random access of the temp.
    (If the flag is 'true')
    The scan builds a fast integer sort structure for random access of the temp.
    (If the flag is 'false')
    and The query optimizer will use the zigzag join method
    if it determines that the tables and query fit the prerequisites for zigzag join
    Ensuring that queries fit the required criteria for the zigzag join


    So, maybe you should check the second server whether it fits these requirments:
    1、db2 version is 10.1 or later
    2、dimension tables (TROP_PRD_RORD_PARM,LOC_LOCATIONS) have the uniq constraint or primary index or unique index on the join column.
    3、the fact table (TDNH_DMD_HISTORY) has the suitable muticolum index ( STY_NBR,STR_NBR )

    and the statistics for the table referenced by the query should be up to date.

  9. #9
    Join Date
    Mar 2014
    Posts
    14
    Fensun2 thanks a lot for your inputs, I wasn't aware of the zigzag join method. I will keep you posted of my findings on the other server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •