Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: Different explain plan for same SQL

    Hi,

    I have dev and QA database with same DB cf and DB cfg setup.
    This is version 9.7 on linux.

    The SQL has 3 table join.
    Table A have 80 million rows
    Table B have 100,000 rows
    Table C have 120,000 rows

    Select * from A join B on A.col1=B.col1
    join C on A.col1.C.col1
    where B.col2 = 'xyz'
    and C.col2='xyz'

    B.col2 = 'xyz' filters 15 million rows from table A
    C.col2 ='xyz' filters 5 million rows from table A


    On dev server the join starts with C & A and then B
    This performs better and returns in less than 1 min.

    On QA server the join starts with B & A and then C
    This returns in 4 mins

    I tried reorg/runstats on both servers. But the plan does not change.
    Volume on both servers is identical. Is there are other way to make QA access plan work as DEV?

    I can not rewrite the query since it is generated out of cognos tool.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about comparing output of db2exfmt, as a starting point?

    Some parameters which would influence access path showed at the top of the output.
    If those were identical, compare more detailes of the output.

    Example:
    Code:
    DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
    Licensed Material - Program Property of IBM
    IBM DATABASE 2 Explain Table Format Tool
    
    
    
    ******************** EXPLAIN INSTANCE ********************
    
    DB2_VERSION:       09.07.5
    SOURCE_NAME:       SYSSH200
    SOURCE_SCHEMA:     NULLID
    SOURCE_VERSION:
    EXPLAIN_TIME:      2012-06-06-06.28.17.918000
    EXPLAIN_REQUESTER: DB2ADMIN
    
    Database Context:
    ----------------
            Parallelism:          None
            CPU Speed:            7.085164e-007
            Comm Speed:           0
            Buffer Pool size:     10250
            Sort Heap size:       256
            Database Heap size:   600
            Lock List size:       4096
            Maximum Lock List:    22
            Average Applications: 1
            Locks Available:      28835
    
    Package Context:
    ---------------
            SQL Type:           Dynamic
            Optimization Level: 5
            Blocking:           Block All Cursors
            Isolation Level:    Cursor Stability
    
    ...

Posting Permissions

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