Explain Plan - Final & Other Plan

[GianTD]

Hi All !

I usually see this in o/p of Diagnostic Helpstats:

<- END RECOMMENDED STATS FOR FINAL PLAN

BEGIN RECOMMENDED STATS FOR OTHER PLANS ->

So, what are Final & Other Plans?

Which stats we should choose for our queries from these two plans?

[dnoeth]

This is based on a new feature in TD14.10 named "Incremental Planning and Execution".

Check the SQL Request and Transaction Processing manual for details. 

[GianTD]

Thanks Dieter.

Should we use stats recomendations provided under both plans?

[GianTD]

What is indicated when difference between Estimated time/Rows and actual time/Rows?

Also, how does it matter if Actuals are lower or higher? What they indicate?

[dnoeth]

If there's a huge difference between estimated and actual (according to Viewpoint's QueryMonitor or DBQLStepInfoV) stats might be missing or the optimizer couldn't use them or he did a really wrong calculation.

If actual is magnitudes higher than estimated this step might have a very bad execution, e.g. seconds estimated, but minutes/hours actual for a Product Join with 100,000 instead of 10 rows.