1

Sorry - I feel like this is a newb question, but I can't find anything on it. In optimizing a query, I was able to apparently reduce the total timerons from 3,700 to 38. However, on closer examination of the explain plan for the optimized query, I see one IXSCAN that reports 3,707.97 timerons, while the total is still reported as 38.61!

I'm not super familiar with DB2 LUW, and was wondering how this could be. Even with parallelism, I would not think we could get much smaller than, say, 1/4 of the largest #. Surely not 1/10th. (?)

This is DB2 LUW 9.5 on AIX. Very plain-vanilla installation for testing environment. No HA, no partitioning, nothing like that.

Explain plan from Control Center - Command Editor

6
  • Did you try using db2exfmt to display the plan?
    – mustaccio
    Commented Feb 7, 2014 at 0:28
  • I have, now :-) It appears to give the same results. I'm having trouble figuring out how to attach a file, so here are the highlights: RETURN: (Return Result) Cumulative Total Cost: 38.6147 IXSCAN: (Index Scan) Cumulative Total Cost: 3707.97
    – Joe Hayes
    Commented Feb 10, 2014 at 15:45
  • Normally this should not be so. There is a chance that the table and index statistics are out of sync (the db2exfmt report would show the respective data at the end). There is also a chance that there may be a bug in the explain facility. Are you using the most recent fix pack?
    – mustaccio
    Commented Feb 10, 2014 at 16:08
  • Any traction on this? Commented Feb 11, 2014 at 13:40
  • Sorry - been a crazy couple days. I think you might be onto something about the statistics. Let me check it out and get back to you. The FP won't be the most recent (it's a client-model), but it should have been updated from the base. The main thing I think I was looking for is confirmation that those results are crazy and not to be trusted, and it sounds like I'm getting that. Thanks :-) I'll check the statistics and update the thread.
    – Joe Hayes
    Commented Feb 11, 2014 at 20:12

1 Answer 1

0

Just want to tie this off. As was suggested in the comments, this simply should not be so. It must have been a bug in the level of DB2 I was using.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.