Caché SQL Optimization Guide
Frozen Plans
[Home] [Back] 
InterSystems: The power behind what matters   
Class Reference   
Search:    

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.
How to Use Frozen Plans
There are two strategies for using frozen plans — the optimistic strategy and the pessimistic strategy:
Caché Version Upgrade Automatically Freezes Plans
When you upgrade Caché to a new version, existing Query Plans are automatically frozen. This ensures that a software upgrade will not degrade the performance of an existing query. In other words, a pessimistic frozen plan strategy. After an upgrade, you can use the %NOFPLAN keyword in queries to determine if the upgrade improved the performance of an individual query. You can unfreeze individual queries or multiple queries as warranted.
This automatic freeze occurs when you prepare/compile a query under a Caché version newer than the version under which the plan was originally created. For example, consider an SQL statement that was prepared/compiled under Caché version 2016.2. You then upgrade Caché to version 2017.1, 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 Caché upgrades automatically freeze existing query plans. The earliest upgrade that performs this operation is an upgrade to 2017.1.0. For example, a major version upgrade, such as from 2016.2 to 2017.1, or a minor version upgrade, such as from 2017.1 to 2017.2 performs this operation. A maintenance release version upgrade, such as 2017.1.0 to 2017.1.1 does not perform this operation.
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 Caché 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.
Note:
An upgrade from Caché version 2016.2 to a later version does not change INSERT ... SELECT query plans to Frozen/Upgrade. If the plan state was Unfrozen, the plan state for these statements will remain Unfrozen. If the plan state was Frozen, the plan will be put in an error state and will be unusable; you must explicitly unfreeze the plan. Following upgrade, you should recompile the container code (routine, class) that contains the INSERT ... SELECT statement, or purge and re-prepare cached queries. You can then re-freeze formerly frozen plans, if desired.
Frozen Plans Interface
There are two frozen plan interfaces, used for different purposes:
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.
In the Management Portal SQL interface select the SQL Statements tab. This displays a list of SQL Statements. The Plan State column of this list specifies Unfrozen, Unfrozen/Parallel, Frozen/Explicit, or Frozen/Upgrade. (The Plan State column is blank if the statement has no associated Query Plan.)
To freeze or unfreeze a plan, choose an SQL statement in the SQL Statement Text column. This displays the SQL Statement Details box. At the bottom of this box it displays the Statement Text and Query Plan. The background color for these sections is green if the plan is not frozen, and blue if the plan is frozen. Just above that, under Statement Actions, you can select the Freeze Plan or Un-Freeze Plan button, as appropriate. You then select Close.
You can also freeze or unfreeze one or more plans using the FreezePlans() method.
The meaning and use of the other fields in the SQL Statement Details box are described in the “SQL Statements” chapter of this guide.
Privileges
A user can view only those SQL Statements for which they have execute privileges. This applies both to Management Portal SQL Statements listings and to INFORMATION_SCHEMA.STATEMENTS class queries.
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.
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.
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:
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.
Manual Frozen Plan Check
At the top of the SQL Statement Details page for a frozen plan there is a Check frozen button. Pressing this button displays the Unfrozen plan different check box. If this box is checked, unfreezing the plan would result in a different query plan.
When you have performed this Check frozen test on a frozen plan:
After performing this test, the Check frozen button disappears. If you wish to re-test a frozen plan, select the Refresh Page button. This re-displays the Check frozen button.
Automatic Daily Frozen Plan Check
Caché 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, Caché notes where it left off, and continues from that point on the next daily scan.
This automatic scan examines all frozen plans:
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:
If the query is [re]compiled and the frozen plan is in an error state, Caché 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 Caché 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 Caché 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. This enables you to move a frozen plan from one location to another. SQL Statement exports and imports include the associated query plan.
To export an SQL Statement to a file, use the ExportSQLStatement() method.
To export all of the SQL Statement entries in this namespace to a file, use the ExportAllSQLStatements() method.
To import an SQL Statement or multiple SQL Statements from a file, use the ImportSQLStatement() method.