Method R Discussion GroupMethod R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle. |
||
Discuss Method R products, techniques, and events. Ask new questions or post your own responses. |
Why focus on explain plan and compare with STAT lines? Shouldn't STAT lines provide a better picture than explain?
The explain plan shows the plan that the optimizer thought it would use along with the estimated row selectivity information, etc. The STAT lines show actually what happened. The comparison of the two can show you where the optimizer's calculations may have been "off" thus pointing you to a possible problem you need to address in order to get the best/correct plan.
Just knowing what the optimizer did (via the STAT lines) is important, but knowing why it thought that plan was a good idea and chose it can be seen more clearly by reviewing the explain plan output. For example, what if the STAT lines showed cnt=200000 for a particular operation, but the explain plan shows rows=2000 for that operation? Wow! That's a huge difference in that the optimizer thought it would return 2000 rows but it actually returned 200,000 rows. And, maybe this query was very slow. It's possible if the optimizer had gotten it's estimate more correct (i.e. nearer to 200,000), it would have made a different plan choice that would improve the response time. So, it's ideal to be able to compare the explain plan with the STAT lines to look for these kinds of discrepancies. |
Powered by FogBugz