By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

estimated execution plan

P: n/a
Hi NG,

does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the *estimated*
execution plan? Didn't find any distinction of actual or estimated execution
plans in the information center.

Thanks for help,
Ina
Feb 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Ina Schmitz" <we*@inalein.net> wrote in message
news:dt*************@news.t-online.com...
Hi NG,

does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the
*estimated* execution plan? Didn't find any distinction of actual or
estimated execution plans in the information center.

Thanks for help,
Ina


The explain should show you the actual execution plan if the dynamic SQL was
submitted at that same point in time, assuming that the optimization level
is the same for the explain and the client that runs the SQL.

If the SQL statement is statically bound, then the execution plan was
created at bind time and will not change unless a rebind is performed.
Feb 27 '06 #2

P: n/a
Mark A wrote:
"Ina Schmitz" <we*@inalein.net> wrote in message
news:dt*************@news.t-online.com...
Hi NG,

does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the
*estimated* execution plan? Didn't find any distinction of actual or
estimated execution plans in the information center.
The explain should show you the actual execution plan if the dynamic SQL
was submitted at that same point in time, assuming that the optimization
level is the same for the explain and the client that runs the SQL.


In short: DB2 does have only one plan for a SQL statement: the actual one.
There is no mechanism to come up with an "estimated plan".
If the SQL statement is statically bound, then the execution plan was
created at bind time and will not change unless a rebind is performed.


And for dynamic SQL DB2 will take the execution plan from the statement
cache if it is still there (that's why you should flush that cache after
the statistics are collected).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.