Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Which EXPLAIN ??

    Hi Guys,

    When I explain the query with db2advis as below , it gives different results than the Explain Plan Graph via CE.

    db2advis -d gaming -i MyQuery.sql -a db2admin/****** -n db2admin -t 5

    The difference is huge. 2742.57 timeron in CE vs

    103 indexes in current solution
    [274252.2705] timerons (without recommendations)
    [177805.2490] timerons (with current solution)
    [35.17%] improvement

    Please help me be comfortable to use proper EXPLAIN.

    DBFinder

  2. #2
    Join Date
    Jul 2009
    Posts
    10
    Do not worry about timerons - they are only useful to compare costs of different parts of one query to each other.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by db2topgun
    Do not worry about timerons - they are only useful to compare costs of different parts of one query to each other.
    Well, what the explains are for ?? Huge difference 2742 vs 274252.

    Suppose I do visual Explain which tells me the query is Superfast. I tell my developer that query is superfast.He goes back and runs it. The query never returns.

    This has actually happened.


    Again if I do not worry about timerons, I am really interested to know what we do with visual explanation. May be I am missing something.

    Thanks anyway !

    DBFinder

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    you look at the path. See if you are doing tbscans and why. Sometimes they are warranted, most of the time they are not. See if you are using right indexes.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Jul 2009
    Posts
    10
    According to DB2 documentation, "The EXPLAIN statement captures information about the access plan chosen for the supplied explainable statement and places this information into the explain tables." On the basis the access path, given sufficient information about the environment, where the query will execute, you may be able to estimate run time. For example, on the basis of the EXPLAIN output, you discover how much I/O will be done, but that says nothing about how long that I/O will take - this depends on how well your I/O subsystem performs, which is something that EXPLAIN knows nothing about.

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Yes I do.

    But my concern is to know, which explain is right. With huge difference in both, I need to use the right one.

    In Command Editor, when I explained this query there were no tabscans, all indexes were being used properly.

    but with db2advis, it is suggesting 103 new indexes !!

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In Visual Explain - for Operators (TBSCAN, RETURN etc) in the Cumulative Cost section, you get estimated CPU and IO instructions ... They are better properties for comparison
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by db2topgun
    According to DB2 documentation, "The EXPLAIN statement captures information about the access plan chosen for the supplied explainable statement and places this information into the explain tables." On the basis the access path, given sufficient information about the environment, where the query will execute, you may be able to estimate run time. For example, on the basis of the EXPLAIN output, you discover how much I/O will be done, but that says nothing about how long that I/O will take - this depends on how well your I/O subsystem performs, which is something that EXPLAIN knows nothing about.

    Again should I never use CE ?

    CE showed me no index required. There may be any wrong setting of ENV that makes two different tools work different !!

    Any Idea ??

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by DBFinder
    Yes I do.

    But my concern is to know, which explain is right. With huge difference in both, I need to use the right one.

    In Command Editor, when I explained this query there were no tabscans, all indexes were being used properly.

    but with db2advis, it is suggesting 103 new indexes !!
    That is exact reason I do not like the adviser and barely ever use it.

    Are indexes built correctly? I am now trying to convince management to drop over 100gb worth of indexes from one db.

    Do you care to post screen shot of both of your explains?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Jul 2009
    Posts
    10
    Any form of EXPLAIN will give you exactly the same information.
    However, db2advis is NOT EXPLAIN. While EXPLAIN tells you how your SQL will work with the existing structures, db2advis makes recommendations on how to improve performance by modifying db design.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use db2pd to get the information on the indexes being used.

    Cheers
    Sathyaram

    Quote Originally Posted by Cougar8000
    That is exact reason I do not like the adviser and barely ever use it.

    Are indexes built correctly? I am now trying to convince management to drop over 100gb worth of indexes from one db.

    Do you care to post screen shot of both of your explains?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You will find a good discussion on using db2pd for knowing table and index usage at

    db2pd to monitor table access

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Just because they are used does not mean they are good

    db2pd is helpful, but you can't reply on it as solely.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    DBFinder, just curious. When you run the Explain in CE, do you just process an Explain or do you run the Query and Explain it? It may be possible to get different results with the different methods. The first method won't know the actual values of variables while the second method will have the actual values.

    But if I had to make a choice of which on of your two Explain methods to trust (CE or DB2ADVIS), I would believe the one that shows the correct indexes being used instead of the one that tries to tell me I need 103 additional indexes. (I can't imagine ANY query I would want to write that would even attempt to use 103 indexes).

  15. #15
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Agree with you, technically ... But when you say

    "I am now trying to convince management to drop over 100gb worth of indexes from one db."

    Isn't db2pd the tool you can use to justify dropping some of the indexes ???? Next stage, you have a smaller pool of indexes to challenge and tune - using Explain or whatever means , then you can rid of more unwanted indexes.

    My approach ... That's all ... Doesn't mean it is good


    Quote Originally Posted by Cougar8000
    Just because they are used does not mean they are good

    db2pd is helpful, but you can't reply on it as solely.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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