Obtaining Plan data on individual queries within a large stored procedure

[Former Member]

One of the items listed as a feature in the Introduction to the SQL Anywhere debugger is the following:

"Execute queries You can execute queries when execution is stopped at a breakpoint in a SQL procedure. This permits you to look at intermediate results held in temporary tables, check values in base tables, and to view the query execution plan."

I don't see any explanation anywhere of how to do that. Can anyone provide some insight into this?

[Breck_Carter]

These two articles were written from actual experience capturing plans inside large, complex stored procedures:

Capture Plans With GRAPHICAL_PLAN()

Capturing and Storing Plans With GRAPHICAL_PLAN()

[Former Member]

Good point Breck. I was thinking there might have been a way to pop directly into a plan viewer but one would need to capture the plan first and then later use dbisql for that.

[Breck_Carter]

Well, the execution profiler could capture the graphical plan at a breakpoint if there was a feature to do that...

and apparently such a feature exists!...

Execute queries You can execute queries when execution is stopped at a breakpoint in a SQL procedure. This permits you to look at intermediate results held in temporary tables, check values in base tables, and to view the query execution plan.

[VolkerBarth]

So we're back to the original question, right?

[Breck_Carter]

Yes... one of us should go looking to see if it actually exists and is simply well-hidden 🙂

[Former Member]

I'm still researching this and if I find out anything different from the information below I will report back here ....

My current understanding is you can see the result set of any query in the debugger's Query Database utility. So you can execute a select plan( ...) or a select graphical_plan( ... ) and see the result set representation of that. The latter query is less useful because of some severe length restrictions.

So not the answer hoped for. Plan( ) or even a Sort_Plan( ) might fit into the length restrictions of the result set ... but no graphical plan viewer; at least not built into this utility.

As such that much does match the article referenced.

[Former Member]

You can run queries from the Debug menu using the "Query Database" pick; when you are in the debugger after hitting a breakpoint.

I do remember seeing the graphical plan part of this demonstrated once but will have to research it more. It may have involved running a query using the graphical_plan function (possibly embedded in the procedure) ...