How can I programmatically retrieve a SQL query execution plan and analyze its results?

You can retrieve and analyze SQL query execution plans programmatically and through interactive commands using InterSystems tools. Here are the approaches available:

1. EXPLAIN Command:
   Use the EXPLAIN command in SQL to display the query execution plan for a SELECT query without actually executing the query. Example:

   SQL]USER>>EXPLAIN SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'
   

   This command also allows for generating performance statistics with EXPLAIN STAT or alternate query plans with EXPLAIN ALT [1][2].

2. SHOW PLAN Command:
   After executing your query, use the SHOW PLAN command in the SQL Shell to display its plan. Alternatively, you can set the execution mode to deferred to view the plan without running the query:

   SQL]USER>>SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'
   SQL]USER>>SHOW PLAN
   

   SHOW PLAN works for other statement types, including INSERT, UPDATE, and DELETE operations [1][2].

3. SHOW PLANALT:
   The command SHOW PLANALT can display alternate execution plans for your query. This is useful for comparing different strategies proposed by the SQL optimizer [2].

4. ObjectScript Method ($SYSTEM.SQL.Explain):
   Generate query execution plans programmatically using ObjectScript and the $SYSTEM.SQL.Explain() method. This can be integrated into your application workflows for automated analysis purposes [1][2].

These tools provide comprehensive ways to programmatically retrieve and analyze SQL query plans alongside their associated performance metrics, suppressing the need for SQL execution when desired and offering functionalities for debugging and optimization.

Sources:

Explain command is what you are looking for:

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...