Professional Web Applications Themes

Can Someone Explain (No Pun Intended) this Explain Plan Output ? - IBM DB2

We're running DB2 v7.1 on OS/390. The DBA guys gave us a macro to generate an explain plan against a query that is stored in a PDS member. The output for a query in the test environment looks like: PLAN TABLE NAME / ACCESS / IX N N N N C C C C NUM INDEX NAME MTHD MATCHCOLS ONLY U J O G U J O G ---- -------------------------- ---- --------- ---- - - - - - - - - 1 AUTHID.BIGTABLE 0 I 2 N N N N N N N N N AUTHID.INDEX1 2 AUTHID.LOOKUP 1 R ...

  1. #1

    Default Can Someone Explain (No Pun Intended) this Explain Plan Output ?

    We're running DB2 v7.1 on OS/390. The DBA guys gave us a macro to
    generate an explain plan against a query that is stored in a PDS member.
    The output for a query in the test environment looks like:

    PLAN TABLE NAME / ACCESS / IX N N N N C C C C
    NUM INDEX NAME MTHD MATCHCOLS ONLY U J O G U J O G
    ---- -------------------------- ---- --------- ---- - - - - - - - -
    1 AUTHID.BIGTABLE 0 I 2 N N N N N N N N N
    AUTHID.INDEX1
    2 AUTHID.LOOKUP 1 R N N N N N N N N N

    3 3 N N N N N N N N Y



    In the prod environment, the same query gets the following explain plan:

    PLAN TABLE NAME / ACCESS / IX N N N N C C C C
    NUM INDEX NAME MTHD MATCHCOLS ONLY U J O G U J O G
    ---- -------------------------- ---- --------- ---- - - - - - - - -
    1 AUTHID.LOOKUP 0 R N N N N N N N N N

    2 AUTHID.BIGTABLE 2 I 2 N N Y N N N Y N N
    AUTHID.INDEX1

    3 3 N N N N N N N N Y



    In prod, the big table has about 100 million rows. In Test, about 25
    million.

    The lookup table has 48 rows.


    I'm wondering whether the change in sequence matters. Also, some of the
    flags (NJ, CJ) are different. I have no idea what these flags mean.



    Thanks.

    Richard Winston Guest

  2. #2

    Default Re: Can Someone Explain (No Pun Intended) this Explain Plan Output ?

    > I'm wondering whether the change in sequence matters. Also, some of the
    > flags (NJ, CJ) are different. I have no idea what these flags mean.
    > Thanks.
    >
    Please post the SQL statement.


    Mark A Guest

  3. #3

    Default Re: Can Someone Explain (No Pun Intended) this Explain Plan Output?

    Mark A wrote:
    >>I'm wondering whether the change in sequence matters. Also, some of the
    >>flags (NJ, CJ) are different. I have no idea what these flags mean.
    >>Thanks.
    >>
    >
    > Please post the SQL statement.
    >
    >


    Mark, I was looking for general information on the plans generated.
    Something like "The plan number tells you....the method number tells
    you..."


    Do you need the query to determine that?

    Richard Winston Guest

  4. #4

    Default Re: Can Someone Explain (No Pun Intended) this Explain Plan Output ?


    "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    news:gHm9b.4368822$cI2.617556news.easynews.com...
    > Mark A wrote:
    >
    > >>I'm wondering whether the change in sequence matters. Also, some of the
    > >>flags (NJ, CJ) are different. I have no idea what these flags mean.
    > >>Thanks.
    > >>
    > >
    > > Please post the SQL statement.
    > >
    > >
    >
    >
    >
    > Mark, I was looking for general information on the plans generated.
    > Something like "The plan number tells you....the method number tells
    > you..."
    >
    >
    > Do you need the query to determine that?
    >
    No. But it does take some experience to understand what the manuals are
    telling you. However, it is worth learning as much about this as possible.

    You can look in the DB2 for OS/390 V7 SQL manual starting on page 752. More
    comprehensive explanations may be available in some other manuals also (like
    DB2 Application Programming and DB2 System Administration).

    The column headings used in your report are abbreviations created by the
    person who wrote the query and refer to columns SORTN_JOIN and SORTC_JOIN.
    These are extra sorts that DB2 performs to satisfy the query.

    The main difference between the 2 explains is that the test environment is
    doing a nested loop join (method 1) and the prod is doing a merge scan join
    (method 2) and also the extras sorts. Usually nested loop join is faster,
    but it depends on the statements and the tables (including table size).

    If you reorg all the tables/indexes and execute runstats utility, and rebind
    the plans, the explains will probably be the same (unless the data
    distribution is wildly different between the 2 environments).


    Mark A Guest

  5. #5

    Default Re: Can Someone Explain (No Pun Intended) this Explain Plan Output?

    Mark A wrote:
    > "Richard Winston" <rwinstonNOPSPAM.ORG> wrote in message
    > news:gHm9b.4368822$cI2.617556news.easynews.com...
    >
    >>Mark A wrote:
    >>
    >>
    >>>>I'm wondering whether the change in sequence matters. Also, some of the
    >>>>flags (NJ, CJ) are different. I have no idea what these flags mean.
    >>>>Thanks.
    >>>>
    >>>
    >>>Please post the SQL statement.
    >>>
    >>>
    >>
    >>
    >>
    >>Mark, I was looking for general information on the plans generated.
    >>Something like "The plan number tells you....the method number tells
    >>you..."
    >>
    >>
    >>Do you need the query to determine that?
    >>
    >
    > No. But it does take some experience to understand what the manuals are
    > telling you. However, it is worth learning as much about this as possible.
    >
    > You can look in the DB2 for OS/390 V7 SQL manual starting on page 752. More
    > comprehensive explanations may be available in some other manuals also (like
    > DB2 Application Programming and DB2 System Administration).
    >
    > The column headings used in your report are abbreviations created by the
    > person who wrote the query and refer to columns SORTN_JOIN and SORTC_JOIN.
    > These are extra sorts that DB2 performs to satisfy the query.
    >
    > The main difference between the 2 explains is that the test environment is
    > doing a nested loop join (method 1) and the prod is doing a merge scan join
    > (method 2) and also the extras sorts. Usually nested loop join is faster,
    > but it depends on the statements and the tables (including table size).
    >
    > If you reorg all the tables/indexes and execute runstats utility, and rebind
    > the plans, the explains will probably be the same (unless the data
    > distribution is wildly different between the 2 environments).
    >
    >


    Thanks. I cracked up the guide and I can see there is a lot of good
    stuff in there but you're definitely right...I'll need a lot of
    experience before I can begin to understand what is going on. I'll
    probably have to take a class somewhere to get a headstart because our
    DBA's aren't very helpful when it comes to stuff like this.


    The test and prod tables should have the same data distribution since
    all we did was load one year from the 4 year prod enviroment. I guess
    the other things you mentioned (reorg, runstats) might not be the same
    across regions.

    This at least gives me a headstart if I encounter different performance
    times.



    Thanks a lot, Mark. You're been very helpful.

    Richard Winston Guest

Similar Threads

  1. Explain Plan for queries
    By ovkrishna in forum Informix
    Replies: 8
    Last Post: October 27th, 04:31 PM
  2. Marty,I Have Another Explain Plan Question
    By Richard Winston in forum IBM DB2
    Replies: 4
    Last Post: September 15th, 08:05 PM
  3. Way To Generate Readable Explain Plan?
    By Erik Hendrix in forum IBM DB2
    Replies: 1
    Last Post: July 17th, 11:54 PM
  4. Replies: 3
    Last Post: July 11th, 09:08 AM
  5. Explain plan for SQL stored procedure
    By Onno Ceelen in forum IBM DB2
    Replies: 5
    Last Post: June 30th, 08:04 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139