25.08.2017 11:38, Paul Reeves wrote:
In FB 3.0 the plan output for stored procs is always PLAN (NATURAL)
whereas with FB 2.5 the plan for each sql statement to be executed
within the SP are returned.
Not having the plan available in FB 3.0 makes it quite difficult to see
what is actually happening in the SP. However, if we take any SP source,
remove the 'create proc myproc' and insert 'execute block' and then
prepare it with input param markers the plan (or plans) is/are actually
returned.
So obviously this information is available internally. How come it is
supressed for SPs in FB 3.0? Is this a bug?
It was suppressed intentionally.
First of all, there's no such thing as SP plan -- the output is purely
artificial, listing all the access paths inside SP. But different code
paths may be executed depending on parameters, so that "plan" becomes
somewhat useless (except when you want something as simple as finding
the unexpected "NATURAL" there).
Second, you didn't get all the access info anyway if your SP calls (or
selects from) another SPs. Only the first level was printed in the plan,
you need to dig deeper manually.
Then, the plan becomes unreadable if the SP sub-plan is injected in the
middle of the outer plan (e.g. join of procedure and table -- it's hard
to get where that table belongs to). There more complex is the outer
query the less sense we have in "embedded" SP plans there.
Finally, if applied to the "explained plan" feature, its verbosity
becomes overcomplicated.
EXECUTE BLOCK shares some of these issues with SP, but its internal
implementation is different so it wasn't affected by the change.
I agree it was handy in simple cases, but it had too many conceptual
problems. I believe we need some other tool for this purpose, maybe
INSPECT or ANALYZE command for PSQL objects. Any better idea?
Dmitry
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
