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

    Unanswered: Different explain plan for same SQL


    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
    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.

    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
    EXPLAIN_TIME:      2012-06-06-
    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