InterSystems SQL Optimization Guide
Most SQL Statements have an associated Query Plan. By default, a Query Plan is updated when an associated SQL statement is re-compiled. Frozen plans enable you to retain (freeze) a Query Plan across compiles.
When a query plan is not frozen (the default) operations such as adding an index and recompiling the class generate a new query plan. When you freeze a query plan, changes to the class do not generate a new query plan. If the statement is compiled, the query optimizer uses the frozen plan, rather than performing a new optimization.
There are two strategies for using frozen plans the optimistic strategy and the pessimistic strategy:
Pessimistic: use this strategy if your assumption is that a change to the class definition will probably not improve performance. Run the query and freeze the plan
. Change the class definition. Re-run the query with the %NOFPLAN
keyword (which causes the frozen plan to be ignored). Compare the performance of the two queries. If ignoring the frozen plan did not improve performance, keep the plan frozen and remove %NOFPLAN from the query.
When you upgrade your system to a new InterSystems software version, existing Query Plans are automatically frozen. This ensures that a software upgrade will never degrade the performance of an existing query. After a software version upgrade, perform the following steps for performance-critical queries:
Execute the query with the plan state as Frozen/Upgrade and monitor performance. This is the optimized Query Plan that was created prior to the software upgrade.
Add the %NOFPLAN
keyword to the query, then execute and monitor performance. This optimizes the Query Plan using the SQL optimizer provided with the software upgrade. It does not unfreeze the existing Query Plan.
Compare the performance metrics.
If the %NOFPLAN performance is better, the software upgrade improved the Query Plan. Unfreeze the Query Plan. Remove the %NOFPLAN keyword.
If the %NOFPLAN performance is worse, the software upgrade degraded the Query Plan. Keep the Query Plan frozen; promote it from Frozen/Upgrade to Frozen/Explicit. Remove the %NOFPLAN keyword.
After testing your performance-critical queries, you can unfreeze all remaining Frozen/Upgrade plans.
This automatic freeze occurs when you prepare/compile a query under an InterSystems software version newer than the version under which the plan was originally created. For example, consider an SQL statement that was prepared/compiled under system software version xxxx.1. You subsequently upgrade to version xxxx.2, and the SQL statement is prepared/compiled again. The system will detect this is the first prepare/compile of the SQL statement on the new version, and automatically marks the plan state as Frozen/Upgrade, and uses the existing plan for the new prepare/compile. This ensures the query plan used is no worse than the query plan of the previous version.
Only major version and minor version InterSystems system software upgrades automatically freeze existing query plans. A maintenance release version upgrade does not freeze existing query plans.
In the Management Portal SQL interface the SQL Statements Plan State
column indicates these automatically frozen plans as Frozen/Upgrade and the Plan Version
indicates the InterSystems software version of the original plan. Refer to SQL Statement Details
for further information. You can unfreeze individual plans using this interface.
You can use the FreezePlans()
method to unfreeze a single plan or multiple plans. You can also use FreezePlans()
to promote (freeze) plans flagged as Frozen/Upgrade to Frozen/Explicit. Commonly, you would use this method to selectively promote appropriate Frozen/Upgrade plans to Frozen/Explicit, then unfreeze all remaining Frozen/Upgrade plans.
There are two frozen plan interfaces, used for different purposes:
Management Portal SQL Statements
interface, used to freeze (or unfreeze) the plan for an individual query.
method interface, used to freeze (or unfreeze) all plans for a namespace, a schema, or a table.
In the Management Portal
SQL interface select the Execute Query
tab. Write a query, then click the Show Plan
button to display the current query execution plan.
button: Clicking this button will cause the query optimization plan for this statement to be frozen. When a plan is frozen, and that SQL statement is compiled, the SQL compilation will use the frozen plan information and skip the query optimization phase.
button: Clicking this button will delete the frozen plan for this statement and new compilations of this statement will go through query optimization phase to determine the best plan to use.
You can also freeze or unfreeze one or more plans using the FreezePlans()
Management Portal SQL Statements
access requires USE privilege on the %Development resource. Any user that can see an SQL Statement in the Management Portal
can freeze or unfreeze it.
For catalog access to SQL Statements, you can see the statements if you are privileged to execute the statement or you have USE privilege on the %Development resource.
For the $SYSTEM.SQL.FreezePlan() method call, you must have U privilege on the %Developer resource.
If a plan is frozen, you can determine if unfreezing the plan would result in a different plan without actually unfreezing the plan. This information can assist you in determining which SQL statements are worth testing using %NOFPLAN
to determine if unfreezing the plan would result in better performance.
A frozen plan may be different from the current plan due to any of the following operations:
These operations may or may not result in a different query plan. There are two ways to determine if they do:
Manually checking individual frozen plans
Automatically scanning all frozen plans on a daily basis
If the plan has not yet been checked by either of these operations, or a plan is not frozen, the SQL Statements listing New Plan
column is blank. Unfreezing a checked frozen plan resets the New Plan
column to blank.
When you have performed this Check frozen
test on a frozen plan:
If the Unfrozen plan different
box is checked, the SQL Statements listing New Plan
column contains a 1. This indicates that unfreezing the plan would result in a different plan.
If the Unfrozen plan different
box is not checked, the SQL Statements listing New Plan
column contains a 0. This indicates that unfreezing the plan would not result in a different plan.
A cached query
that has been frozen has a New Plan
of 0; purging the cached query and then unfreezing the plan causes the SQL statement to disappear.
Automatic Daily Frozen Plan Check
InterSystems SQL automatically scans all frozen statements in the SQL Statement listing every night at 2:00am. This scan lasts for, at most, one hour. If the scan is not completed in one hour, the system notes where it left off, and continues from that point on the next daily scan.
This automatic scan examines all frozen plans:
If the frozen plan has the same InterSystems software version as the current version, InterSystems IRIS Data Platform™ computes a hash on all the tables that this plan references and their timestamps. If any of these have changed, it flags the SQL statement in the SQL Statements listing New Plan
column with a 1. This indicates that unfreezing the plan would result in a different query plan.
If the frozen plan has the same InterSystems IRIS version as the current version, and no table timestamps have changed, it flags the SQL statement in the SQL Statements listing New Plan
column with a 0. This indicates that unfreezing the plan would not result in a different query plan.
If the frozen plan has a different InterSystems software version from the current version (Frozen/Update
), InterSystems IRIS determines if a change to the SQL optimizer logic would result in a different query plan. If so, , it flags the SQL statement in the SQL Statements listing New Plan
column with a 1. Otherwise, it flags the SQL statement New Plan
column with a 0.
If a statement's plan is frozen, and something changes to a definition used by the plan to cause the plan to be invalid, an error occurs. For example, if an index was deleted from the class that was used by the statement plan:
If the query is [re]compiled and the frozen plan is in an error state, InterSystems IRIS does not use the frozen plan. Instead, the system creates a new Query Plan that will work given the current definitions. However, this Query Plan is not preserved in a cached query or an SQL Statement if a frozen plan is in effect.
The plan in error remains in error until either the plan is unfrozen, or the definitions are modified to bring the plan back to a valid state.
If you modify the definitions to bring the plan back to a valid state, go to the SQL Statement Details page and press the Clear Error
button to determine if you have corrected the error. If corrected, the Plan Error
field disappears; otherwise the Plan Error
message re-displays. If you have corrected the definition, you do not have to explicitly clear the plan error for SQL to begin using the frozen plan. If you have corrected the definition, the Clear Error
button causes the SQL Statement Details page Frozen Query Plan
area to again display the execution plan.
A Plan Error
may be a soft error. This can occur when the plan uses an index, but that index is currently not selectable by the query optimizer because its selectability has been set to 0 by SetMapSelectability()
. This was probably done so the index could be [re]built. When InterSystems IRIS encounters a soft error for a statement with a frozen plan, the query processor attempts to clear the error automatically and use the frozen plan. If the plan is still in error, the plan is again marked in error and query execution uses the best plan it can.
You can use the %NOFPLAN keyword to override a frozen plan. An SQL statement containing the %NOFPLAN keyword generates a new query plan. The frozen plan is retained but not used. This allows you to test generated plan behavior without losing the frozen plan.
The syntax of %NOFPLAN is as follows:
DECLARE <cursor name> CURSOR FOR SELECT %NOFPLAN ...
SELECT %NOFPLAN ....
INSERT [OR UPDATE] %NOFPLAN ...
DELETE %NOFPLAN ...
In a SELECT
statement the %NOFPLAN keyword can only be used immediately after the first SELECT
in the query: it can only be used with the first leg of a UNION
query, and cannot be used in a subquery. The %NOFPLAN keyword must immediately follow the SELECT
keyword, preceding other keywords such as DISTINCT or TOP.
You can export or import SQL Statements. This enables you to move a frozen plan from one location to another. SQL Statement exports and imports include the associated query plan.
To import an SQL Statement or multiple SQL Statements from a file, use the ImportSQLStatement()