Hi there, any Query Plans tonight?


we're planning some work on our SQL Query Plan functionality for a future release of InterSystems IRIS and are interested to hear how you're using them today, or what'd keep you from using them. Rather than try and fit everything in a rigid survey, I thought a simple thread on our beloved DC might also reveal some use patterns that we support or could do a better job on. Some questions to get you started (but hoping they don't constrain your spontaneous creativity!):

  • When do you go look for a query plan? 
  • How are you currently accessing query plans? Using $SYSTEM.SQL.ShowPlan() in ObjectScript, through EXPLAIN or SHOW PLAN over SQL or just through the SMP?
  • Are there any pieces of information that are missing from the raw plan that you'd typically look up through catalog queries or class definitions? Any you're lacking but wouldn't even know how to find?
  • When you have that cunning plan right in front of you, what do you do with it? 
  • Do you ever look at alternate query plans?


Hi Ben,

I have query plans tonight. but not in SQL  ;-)

seriously. 90% from SMP, the rest from SQLshell.
Alternate Query never crossed my path.


When do you go look for a query plan?

Usually after monlbl indicating a problem.

How are you currently accessing query plans?  

SMP, 100%.

I think the most interesting thing to know is which query slows down the system. If there are two queries:

  • Takes a minute, runs daily
  • Takes a second, runs thousands of times per hour

I'm more interested in optimizing the second one.  SQL Runtime Statistics helps, but improvements in this area (and more visibility) would be great.

Embedded SQL queries, with variables, and INTO. When I would like to see query plan, I have to first clean it and replace with `?`.

Would be good, to be able to get query plans for such cases, a bit more easier.

Actually most queries run on a single server.

With Sharding It might be much more important to see possible effects of distributed queries.
Especially in the starting phase when optimizing distributions is still somehow like tapping in the fog.

I'm generally looking at the query plans either from the SMP or from the context menu in Studio while writing class queries/embedded sql statements.

One issue that I've seen is that while the query plan is very good, an in many cases better than what other dbms's provide, when a sub-query is part of your query statement it's not exactly clear when it is utilized in the query plan.  For example, I have this query plan

I cannot tell with 100% certainty where "subquery" is called.