Chapter IX Freezing Plans

Most SQL statements have an associated query plan. Query plans are created when SQL statements are prepared. By default, operations such as adding indexes and recompiling classes clear this query plan. The next time the query is invoked, the query is reprepared and a new query plan is created. Freezing plans enables you to preserve (freeze) existing query plans across compilations. Query execution uses frozen plans instead of performing new optimizations and generating new query plans.

Changes to system software may also result in different query plans. Typically, these upgrades lead to better query performance, but software upgrades can degrade the performance of specific queries. Freezing a plan allows you to preserve (freeze) a query plan so that query performance does not change (degrade or improve) due to system software upgrades.

How to use the freezing plan

There are two strategies to use the freeze plan – optimistic strategy and pessimistic strategy:

  • Optimism: Use this policy if you assume that changing system software or class definitions will improve performance. Run the query and freeze the plan. Export (backup) frozen plans. Unfreeze the program. Change the software. Rerun the query. This will lead to a new plan. Compare the performance of the two queries. If the new plan does not improve performance, you can import the previously frozen plan from the backup file.
  • Pessimistic: Use this policy if you assume that changes to system software or class definitions may not improve the performance of a particular query. Run the query and freeze the plan. Change the software. use%NOFPLANKeyword to rerun the query (which causes frozen plans to be ignored). Compare the performance of the two queries. If ignoring a frozen plan does not improve performance, keep it frozen and delete it from the query%NOFPLAN.

Automatic freezing plan for software version upgrade

Existing query plans are automatically frozen when you upgrade the InterSystems IRIS®Data Platform to the new major version. This ensures that major software upgrades never degrade the performance of existing queries. After the software version is upgraded, perform the following steps for performance-critical queries:

  1. Execute the query whose schedule status is frozen/upgraded and monitor performance. This is an optimized query plan created prior to the software upgrade.
  2. will%NOFPLANKeywords are added to the query, then executed and monitored for performance. This will optimize the query plan using the SQL optimizer provided with the software upgrade. It does not unfreeze existing query plans.
  3. Compare performance indicators.
  • if%NOFPLANIf the performance is better, the software upgrade improves the query plan. Unfreeze query plans. delete%NOFPLANThe keyword.
  • if%NOFPLANIf the performance is poor, the software upgrade degrades the query plan. Keep the query plan frozen and upgrade the query plan from Frozen/Upgraded to Frozen/explicit. delete%NOFPLANThe keyword.
  1. After testing performance critical queries, you can unfreeze all remaining freeze/upgrade plans.

This automatic freeze occurs when a query is prepared/compiled under a newer version of InterSystems software than the one used when the plan was originally created. For example, consider an SQL statement prepared/compiled under system software version XXXX.1. Then upgrade to version XXXX.2 and prepare/compile the SQL statement again. The system will detect that this is the first preparation/compilation of the SQL statement on the new version, automatically mark the plan state as frozen/upgraded, and use the existing plan for the new preparation/compilation. This ensures that the query plan used is no worse than the previous version.

Only major InterSystems system software updates automatically freeze existing query plans. Maintenance release upgrades do not freeze existing query plans. For example, a major version upgrade (such as from 2018.1 to 2019.1) will perform this operation. Do not perform this operation for maintenance version upgrades (such as 2018.1.0 to 2018.1.1).

On the SQL page of the MANAGEMENT portal, the SQL statement plan status column indicates that these automatically frozen plans are frozen/upgraded, and the plan version indicates the inter-system software version of the original plan.

You can use the INFORMATION. The SCHEMA. The STATEMENTS Frozen = 2 the current namespace attribute lists all the freeze/upgrade program.

You can freeze a single query plan or multiple query plans using the following $system.sql. Statement method: FreezeStatement() for a single plan; FreezeRelation() for all plans for relationships; FreezeSchema() for all plans of the architecture; FreezeAll() is used for all plans in the current namespace. There are corresponding thawing methods.

  • The freeze method can promote (” freeze “) query plans marked freeze/upgrade to freeze/explicit. Typically, you can use this method to selectively upgrade the appropriate freeze/upgrade plans to freeze/explicit, and then unfreeze all remaining freeze/upgrade plans.
  • The unfreeze method can unfreeze/upgrade query plans within a specified range: namespace, schema, relationship (table), or individual query.

Freeze plan interface

The freeze plan interface has two different uses:

  • Management Portal SQL statement interface used to freeze (or unfreeze) plans for a single query.
  • $SYSTEM.SQL.StatementFreeze and unfreeze methods used to freeze or unfreeze all plans for a namespace, schema, table, or single query.

In the Management Portal SQL interface, select the Execute Query TAB. Write the query, and then click the Show Plan button to display the current query execution plan. If the plan is frozen, the first line in the query plan section is frozen Plan.

On the SQL page of the MANAGEMENT portal, select the SQL statement TAB. This displays a list of SQL statements. The schedule status column in this list specifies unfreeze, unfreeze/parallel, Freeze/explicit, or freeze/upgrade. (If the statement does not have an associated query plan, the plan status is null.)

You can use the INFORMATION. The SCHEMA. The STATEMENTS Frozen property values: lists the current namespace for all the SQL STATEMENTS in the program: UNFRECTED(0), Frozen/EXPLICIT(1), Frozen/Upgrade(2) or UNFORMATED/PARALLEL(3).

To freeze or unfreeze a plan, select an SQL statement in the SQL statement text column. This displays the SQL Statement Details box. The statement text and query plan are displayed at the bottom of this box. The background color of these cross-sections is green if the plan is not frozen, or blue if the plan is frozen. Under the statement action directly above it, you can select the Freeze plan or unfreeze plan button as required. Then select Close.

  • Freeze Schedule button: Click this button to freeze the query optimization plan for this statement. When you freeze the plan and compile the SQL statement, the SQL compilation uses the frozen plan information and skips the query optimization phase.
  • Unfreeze plan button: Clicking this button removes the frozen plan for the statement, and the new compilation of the statement goes into query optimization to determine the best plan to use.

You can also freeze or unfreeze one or more plans using the $system.sql. Statement freeze and unfreeze method. You can specify the scope of a freeze or unfreeze operation by specifying the appropriate method: FreezeStatement() for a single plan; FreezeRelation() for all planned relationships; All planned FreezeSchema() architecture; FreezeAll() for all plans in the current namespace. There are corresponding thawing methods.

permissions

Users can only view SQL statements for which they have EXECUTE permission. This applies to both Management Portal SQL statement list, also applies to INFORMATION. The SCHEMA. The query STATEMENTS.

The MANAGEMENT portal SQL statement access requirement has the USE permission on %Development resources. Any user who can see the SQL statement in the management portal can freeze or unfreeze the statement.

For directory access to SQL statements, you can see them if you have permission to execute the statement or “Use” permission on %Development resources.

For $system.sql. Statement freezing or unfreezing method calls, you must have “U” permission on the %Developer resource.

Different freeze plans

If the plan is frozen, you can determine whether unfreezing the plan will result in a different plan without actually unfreezing the plan. This information can help you determine which SQL statements are worth testing with %NOFPLAN to determine if unfreezing plans will result in better performance.

Can use the INFORMATION. The SCHEMA. The STATEMENTS FrozenDifferent property: lists the current namespace in which all types of freeze.

Frozen plans may differ from current plans due to any of the following actions:

  • Recompile the table or table referenced by the table
  • useSetMapSelecability()Enable or disable indexes
  • Run on a tableTuneTable
  • Upgrade the InterSystems software version

Recompilation automatically clears existing cached queries. For other operations, existing cached queries must be cleared manually for the new query plan to take effect.

These actions may or may not result in different query plans. There are two ways to determine if they do:

  • Manually check individual freeze plans
  • Automatically scans all freeze plans every day

If the plan has not been checked by either of these operations, or the plan has not been frozen, the SQL statement listing the new plan is null. Unfreezing the selected frozen plan resets the new plan column to empty.

Manually freeze plan check

There is a check freeze button at the top of the SQL statement details page for the freeze plan. Pressing this button will display the unfreeze different schedule check box. If this box is selected, unfreezing the plan will result in a different query plan.

After performing this check freeze test on a freeze plan:

  • If the box of different unfreeze plans is selected, the SQL statement column listing the new plan contains 1. This suggests that the thaw plan will lead to different plans.
  • If the unfreeze plan different box is not selected, the SQL statement column listing the new plan will contain 0. This suggests that the thawing plan will not produce a different plan.
    • The New Plan of the frozen cache query is 0. Clearing the cached query and then unfreezing the plan causes the SQL statement to disappear.
    • Has been frozenNaturaL The query is empty in the New Plan column.

After this test is performed, the check freeze button disappears. If you want to retest the frozen plan, select the Refresh page button. This will redisplay the check freeze button.

Daily freeze schedule automatic check

InterSystems SQL automatically scans all frozen statements in the SQL statement list at 2:00 every night. The scan lasts up to an hour. If the scan is not completed within an hour, the system notes where it stopped and continues the next daily scan from that point. You can use the management portal to monitor this daily scan or force it to scan immediately by selecting System Operations, Task Manager, Task Schedule, and then scanning freeze schedule tasks.

This scan checks all frozen plans:

  • If the frozen plan has the same InterSystems software version as the current version, the InterSystems IRIS®Data Platform computes the hash of the reference tables and timestamps of the two plans to create an internal list of plans that may have changed. For this subset, it then performs a string-by-string comparison of the two plans to determine which plans are actually different. If there is any difference between the two plans (no matter how small), it will be in the columnThe New PlanColumn in an SQL statement"1"Mark SQL statements. This indicates that the unfreezing plan will result in a different query plan.
  • If the frozen plan has the same InterSystems IRIS version as the current version, and the string-by-string comparison of the two plans perfectly matches, it marks the SQL statements in the SQL statement column listing the new plan as"0". This indicates that unfreezing plans do not result in different query plans.
  • If the frozen plan has a different version of InterSystems software than the current version (frozen/updated), InterSystems IRIS determines whether the change to the SQL optimizer logic will result in a different query plan. If so, it will use"1"Mark the SQL Statements in the “SQL Statements Listing New Plan” column. Otherwise, it will"0"Tag SQL statementNew PlanThe column.

By calling INFORMATION. SCHEMA. STATEMENTS to check the scan result. The following example returns SQL statements for all frozen plans, indicating whether frozen plans differ from unfrozen plans. Note that the unfreeze statement can be Frozen=0 or Frozen=3:

SELECT Frozen,FrozenDifferent,Timestamp,Statement FROM INFORMATION_SCHEMA.STATEMENTS 
WHERE Frozen=1 OR Frozen=2
Copy the code

Freeze plan gone wrong

An error occurs if the plan for a statement is frozen and the definition used by the plan changes in such a way that the plan is invalid. For example, if the index is removed from the class used by the statement PLAN:

  • Plans for the announcement remain frozen.
  • On the SQL Statement Details page, the Schedule Error field is displayed in the Compile Settings area. For example, if the query plan uses index namesindxdob, then you modify the class definition to drop the indexindxdob, the following message is displayed: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 shows that the plan could not be determined due to an error in the frozen plan.

InterSystems IRIS does not use a freeze plan if the query is re-executed while the freeze plan is in the wrong state. Instead, the system creates a new query plan that will work given the current definition and execute the query. This query plan is assigned the same cache query class name as the previous query plan.

A failed plan will remain in an error state until the plan is unfrozen or the definition is modified to return the plan to a valid state.

If you modify the definition to return the plan to a valid state, go to the SQL Statement details page and then press the Clear Error button to determine if the error has been corrected. If corrected, the plan error field disappears; Otherwise, the schedule error message will be displayed again. If the definition has been corrected, SQL can start using a frozen plan without explicitly clearing the plan error. If the definition has been corrected, the Clear error button causes the frozen query plan area of the SQL Statement details page to show the execution plan again.

The planning error may be soft error. This may occur when you plan to use an index, but the query optimizer cannot currently select it because SetMapSelecability() has set its selectivity to 0. This may be done to [rebuild] the index. When InterSystems IRIS encounters a soft error with a statement that has a freeze plan, the query processor tries to automatically clear the error and use the freeze plan. If the plan still fails, the plan is flagged as failing again, and the query execution uses the best plan possible.

% NOFPLAN keywords

Frozen plans can be overridden with the %NOFPLAN keyword. The SQL statement containing the %NOFPLAN keyword generates a new query plan. Frozen plans will remain, but will not be used. This allows you to test the generated plan behavior without losing frozen plans.

DECLARE <cursor name> CURSOR FOR SELECT %NOFPLAN ...  
SELECT %NOFPLAN ....  
INSERT [OR UPDATE] %NOFPLAN ...  
DELETE %NOFPLAN ...  
UPDATE %NOFPLAN 
Copy the code

In SELECT statements, the %NOFPLAN keyword can only be used immediately after the first SELECT in a query: it can only be used with the first branch of a UNION query, not in a subquery. The %NOFPLAN keyword must immediately follow the SELECT keyword and precede other keywords such as DISTINCT or TOP.

Export and import freezing plans

SQL statements can be exported or imported as text files in XML format. This makes it possible to move frozen plans from one location to another. SQL statement exports and imports include an encoded version of the associated query plan and flags indicating whether the plan is frozen.