Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2014
    Posts
    6

    Unanswered: Wrong access plan cost

    Hi guys

    I'm tuning a slow query but when I generated the estimated access plan I noticed a weird behavior
    As you can see from the attached file (this is just a part of the entire access plan), the nested loop join operator has a cost lower than the right branch of the below operators.
    You can not see but I can tell you that the estimated cardinality of the hast join is 183, so I'm expecting that for 183 times it executes the table scan which has a cost of 1700000.
    The cost of the entire access plan is 2124.
    Can anyone explain why?

    I'm using DB2 9.7 fix pack 7 on Red Hat 6.

    Thank you in advanced
    Alex
    Attached Thumbnails Attached Thumbnails access_plan.png  

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    Are your statistics up-to-date?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jan 2014
    Posts
    6

    Wrong access plan cost

    Yes they are up-to-date...........but even though they were not updated, the optimizer assigns a cost to the table scan operator and I'm expecting that upper operator cost is always higher than underlying operators.

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    Can you attach the db2exfmt output for this plan?
    Regards,
    Mark.

  5. #5
    Join Date
    Jan 2014
    Posts
    6

    Wrong access plan cost

    HI,

    I have attached the explain_plan (It does not include section actuals).

    Thanks for your help
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    yes, it looks strange.
    I'd suggest you to open PMR with IBM.
    Regards,
    Mark.

Posting Permissions

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