Configure Frozen Plans
Most SQL statements have an associated Query Plan. A query plan is created when an SQL statement is prepared. By default, operations such as adding an index and recompiling the class purge this Query Plan. The next time the query is invoked it is re-prepared and a new Query Plan is created. Frozen plans enable you to retain (freeze) a existing Query Plan across compiles. Query execution uses the frozen plan, rather than performing a new optimization and generating a new query plan.
Changes to system software may also result in a different Query Plan. Usually, these upgrades result in better query performance, but it is possible that a software upgrade may worsen the performance of a specific query. Frozen plans enable you to retain (freeze) a Query Plan so that query performance is not changed (degraded or improved) by a system software upgrade.
How to Use Frozen Plans
There are two strategies for using frozen plans — the optimistic strategy and the pessimistic strategy:
-
Optimistic: use this strategy if your assumption is that a change to the system software or to a class definition will improve performance. Run the query and freeze the plan. Export (backup) the frozen plan. Unfreeze the plan. Make the software change. Re-run the query. This generates a new plan. Compare the performance of the two queries. If the new plan did not improve performance, you can import the prior frozen plan from the backup file.
-
Pessimistic: use this strategy if your assumption is that a change to the system software or to a class definition will probably not improve performance of a specific query. Run the query and freeze the plan. Make the software change. 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.
Frozen Plans After Software Version Upgrade
By default, when you upgrade InterSystems IRIS® data platform to a new major version, existing query plans are invalidated and the system generates a new optimized query plan upon the statement’s first execution on the new system. This new query plan employs any enhancements made to SQL processing, such as improvements to the query optimizer, Runtime Plan Choice, and code generation. This behavior applies when you upgrade an instance that has Adaptive Mode enabled, which is the default for new installations.
However, if Adaptive Mode is off when you execute a query for the first time after upgrading, the query is marked as Frozen/Upgrade. Further invocations of the query continue to use this plan. This behavior may be desirable in highly controlled environments, as it ensures query performance remains the same across upgrades, even though it may imply missed opportunities to take advantage of SQL processing enhancements. After such an upgrade, you can manually unfreeze query plans immediately or test their performance using %NOFPLAN. The following steps describe how to compare the performance of a frozen query plan against the performance of a new query plan generated by the new version of InterSystems IRIS:
-
Execute the frozen query plan and monitor its performance.
-
Add the %NOFPLAN keyword to the query, then execute and monitor performance. This keyword 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 and remove the %NOFPLAN keyword.
-
-
After testing your performance-critical queries, you can unfreeze all remaining frozen plans.
Query plans that had been marked as Frozen/Upgrade can be promoted to Frozen/Explicit by using any of the methods described in Frozen Plans Interface. Commonly, you would perform this upgrade to selectively promote Frozen/Upgrade plans that you want to retain, then unfreeze all remaining Frozen/Upgrade plans.
The behavior described in this section applies to upgrades to InterSystems 2023.3 or above, where the instance being upgraded from has Adaptive Mode enabled, as introduced in InterSystems IRIS 2022.2. When upgrading to a release below 2023.3 or upgrading from a release below 2022.2, Adaptive Mode is considered to be off and the corresponding automatic freezing of query plans applies as described above.
Frozen Plans Interface
You can use the FREEZE PLANS and UNFREEZE PLANS commands to freeze and unfreeze query plans individually, by table, by schema, or by namespace. To freeze or unfreeze an individual plan, find the Hash for the desired Statement by querying INFORMATION_SCHEMA.STATEMENTS. You can then use FREEZE PLANS or UNFREEZE PLANS to change the plan state of a specific statement by providing the Statement’s Hash.
You can list the plan state for all SQL Statements in the current namespace by querying the INFORMATION_SCHEMA.STATEMENTS table for the Frozen property. The values in the Frozen column can be: Unfrozen (0), Frozen/Explicit (1), Frozen/Upgrade (2), or Unfrozen/Parallel (3). You can also use EXPLAIN on a specific query to determine whether it is frozen or not.
You can also freeze or unfreeze one or more plans using the $SYSTEM.SQL.Statement Freeze and Unfreeze methods. You can specify the scope of the freeze or unfreeze operation by specifying the appropriate method: FreezeStatement()Opens in a new tab for a single plan; FreezeRelation()Opens in a new tab for all plans for a relation; FreezeSchema()Opens in a new tab for all plans for a schema; FreezeAll()Opens in a new tab for all plans in the current namespace. There are corresponding Unfreeze methods.
Privileges
A user can view only those SQL Statements for which they have execute privileges, including for INFORMATION.SCHEMA.STATEMENTSOpens in a new tab class queries. 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 $SYSTEM.SQL.Statement Freeze or Unfreeze method calls, you must have “U” privilege on the %Developer resource.
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.
Frozen Plan Different
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.
You can list all frozen plans of this type in the current namespace using the INFORMATION.SCHEMA.STATEMENTSOpens in a new tab FrozenDifferent property.
A frozen plan may be different from the current plan due to any of the following operations:
-
Recompiling the table or a table referenced by the table.
-
Using SetMapSelectability()Opens in a new tab to activate or deactivate an index. You can view whether or not an index is active by querying the INFORMATION_SCHEMA.INDEXES catalog table and viewing the value of the MAP_SELECTABLE column.
Recompiling automatically purges existing cached queries. For other operations, you must manually purge existing cached queries for a new query plan to take effect.
These operations may or may not result in a different query plan. You can scan all frozen plans to determine whether a new query plan will be generated.
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.
Additionally, you can use the Management Portal to force the scan to occur: select System Operation, Task Manager, Task Schedule, then select the Scan frozen plans task.
You can check the results of this scan by invoking INFORMATION.SCHEMA.STATEMENTSOpens in a new tab. The following example returns the SQL Statements for all frozen plans, indicating whether the frozen plan is different from what the plan would be if not frozen. Note that an unfrozen statement may be Frozen=0 or Frozen=3:
SELECT Frozen,FrozenDifferent,Timestamp,Statement FROM INFORMATION_SCHEMA.STATEMENTS
WHERE Frozen=1 OR Frozen=2
Frozen Plan in Error
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:
-
The statement's plan remains frozen.
-
On the SQL Statement Details page the Compile Settings area displays a Plan Error field. For example, if a query plan used an index name indxdob and then you modified the class definition to drop index indxdob, a message such as the following displays: Map 'indxdob' not defined in table 'Sample.Mytable', but it was specified in the frozen plan for the query.
-
On the SQL Statement Details page the Query Plan area displays Plan could not be determined due to an error in the frozen plan.
If the query is re-executed while 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 and executes the query. This Query Plan is assigned the same cached query class name as the prior Query Plan.
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()Opens in a new tab. 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.
%NOFPLAN Keyword
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 ... UPDATE %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.
Exporting and Importing Frozen Plans
You can export or import SQL Statements as an XML-formatted text file. This enables you to move a frozen plan from one location to another. SQL Statement exports and imports include an encoded version of the associated query plan and a flag indicating whether the plan is frozen. For details, refer to Exporting and Importing SQL Statements.