Chapter 8 Explaining THE SQL Query Plan (2)

Details of SQL statements

There are two ways to display the details of SQL statements:

  • On the SQL Statements TAB, select an SQL Statement by clicking the Table/View/Procedure Name link in the left column.

This displays the SQL statement details in a separate TAB. This interface allows you to open multiple tabs for comparison. It also provides a Query Test button to display the SQL Runtime Statistics page.

  • From the Catalog Details TAB (or SQL Statements TAB) of the table, select an SQL Statement by clicking the Statement Text link in the right column.

This will display the SQL statement details in a pop-up window.

You can use the SQL Statement Details display to view query plans and to freeze or unfreeze query plans.

SQL Statement Details provides buttons to freeze or unfreeze query plans. It also provides a Clear SQL Statistics button to Clear performance Statistics, an Export button to Export one or more SQL statements to a file, and a Refresh and Close page button.

The SQL statement details display contains the following sections. Each section can be expanded or collapsed by selecting the arrow icon next to the section title:

  • Statement details, including performance statistics
  • Compiler Settings
  • The statement is defined in the following routine
  • Statement uses the following relationship
  • Statement text and query plan (described elsewhere)

The details of the statement

  • Statement hash: An internal hash representation defined by a Statement that is used as the key of the SQL Statement index (for internal use only).

Sometimes, seemingly identical SQL statements may have different statement hashes. Any differences in Settings/options for code that needs to generate different SQL statements will result in different statement hashes. This can happen on different client versions or platforms that support different internal optimizations.

  • The time stampTimestamp: Originally, the timestamp when the plan was created.

This timestamp is updated after freeze/thaw to record when the plan was unfrozen, not when the plan was recompiled. You may have to click the Refresh Page button to display the unfreeze timestamp. Plan Timestamp is compared to the datetime value of the routine/class containing the statement to see if it uses the same query Plan if the routine/class is compiled again.

  • Version: Create the InterSystems IRIS Version of the plan.

If the Plan status is Freeze/Upgrade, this is an earlier version of InterSystems IRIS. When you unfreeze a query plan, The Status of Plan changes to Unfreeze and Version changes to the current InterSystems IRIS version.

  • Plan State: Freeze/explicit, Freeze/Upgrade, Unfreeze, unfreeze/Parallel.

Frozen/Explicit means that the Frozen plan for the statement has been Frozen by an Explicit user action and that the Frozen plan will be the query plan to be used regardless of any changes to the code that generated the SQL statement. Freeze/upgrade means that the plan for this statement has been automatically frozen by the InterSystems IRIS version upgrade. Unfreezing means that the program is currently unfrozen and may be frozen. Unfrozen/Parallel indicates that the plan is Unfrozen and processed with %Parallel, so it cannot be frozen. NULL(blank) plan status means that there is no associated query plan.

  • Natural Query: A Boolean flag indicating whether the query is a “Natural query.”

If this parameter is selected, the query is a natural query and the performance statistics will not be recorded. If not checked, performance statistics may be recorded. Other factors determine whether statistics are actually recorded. Natural queries are defined as embedded SQL queries, which are very simple and the overhead of recording statistics affects query performance. There is no benefit in keeping statistics on a natural query because the query is already very simple. A good example of a natural query is SELECT Name INTO:n FROM Table WHERE %ID=? The WHERE clause of this query is an equality condition. This query does not involve any loops or any index references. Dynamic SQL queries (cached queries) are not marked as natural queries; Statistics for cached queries may or may not be logged.

  • Frozen plan different: When a Frozen plan is Frozen, this field is displayed to check whether the Frozen plan is different from the unfrozen plan.

When a plan is frozen, the statement text and query plan display frozen and unfrozen plans side by side for comparison.

This section also includes five query performance statistics fields, which are described in the next section.

Performance statistics

Executing the query adds performance statistics to the corresponding SQL statement. This information can be used to determine which queries are executed slowest and which are executed the most. Using this information, you can determine which queries will provide significant benefits through optimization.

In addition to the SQL statement name, plan status, location, and text, the following additional information is provided for the cached query:

  • Count: Integer Count of the number of times this query has been run.

If a different query plan is produced for the query (for example, adding indexes to a table), the count is reset.

  • Average Count: The Average number of times this query is run per day.
  • Total time Total Time: time (in seconds) spent running the query.
  • Average Time: The Average time (in seconds) taken to run this query.

If the query is a cached query, it is likely that the first execution of the query will take much longer than the optimized query from the query cache.

  • Standard deviation: Indicates the Standard deviation of the total time and average time.

The standard deviation for queries that are run only once is 0. Queries that are run multiple times typically have lower standard deviations than queries that are run only a few times.

  • Date First seen: Query the first run (execution) Date.

This may be different from Last Compile Time, which is the Time when the query is prepared.

The UpdateSQLStats task periodically updates query performance statistics for completed query executions. This minimizes the overhead involved in maintaining these statistics. Therefore, currently running queries do not appear in query performance statistics. Queries that were recently completed (within the last hour or so) may not immediately show up in the query performance statistics.

You can Clear the values of these six fields using the Clear SQL Statistics button.

InterSystems IRIS does not record performance statistics for %PARALLEL subqueries separately. %PARALLEL subquery statistics are added to external query statistics. Queries generated by implementations running in parallel do not track their performance statistics separately.

InterSystems IRIS does not record performance statistics for “natural” queries. If the system collects statistics, it will degrade query performance, and the natural query is already optimal, so there is no possibility of optimization.

You can view query performance statistics for multiple SQL statements in the SQL statement TAB display. You can sort the SQL Statements TAB list by any column. This makes it easy to determine, for example, which query has the largest average time.

You can query INFORMATION. SCHEMA. STATEMENTS class attribute to access these query performance statistics, such as described in the query SQL STATEMENTS.

Compilation setup section

  • Select the schemaSelect mode: used when compiling statementsSelectMode.

For DML commands, use #SQLCompile Select; The default is Logical. If # SQLCompile Select = Runtime, call $SYSTEM. SQL. Util. SetOption () method of SelectMode options can change the display of query result set, but will not change the SelectMode value, it is still the Runtime.

  • Default Schema (S): specifies the name of the Default schema set when the statement is compiled.

This is usually the default schema in effect when the command is issued, although SQL may resolve schemas with unqualified names using the schema search path (if provided) instead of the default schema name. However, if the statement is a DML command in embedded SQL that uses one or more #Import macro directives, the pattern specified by the #Import directive will be listed here.

  • Schema path: Specifies the Schema path defined when the statement is compiled.

If specified, this is the pattern search path. If no schema search path is specified, this setting is null. However, for DML Embedded SQL commands that specify the search path in the #Import macro directive, the #Import search path is displayed in the default mode Settings, and the mode path setting is blank.

  • Plan Error: This field only appears when an Error occurs while using a frozen Plan.

For example, if a query plan uses an index, the query plan is frozen and the index is then dropped from the table, Map ‘NameIDX’ not defined in table ‘sample. Person’, but it was specified in the frozen plan for the query. Deleting or adding indexes causes the table to be recompiled, changing the last compile time value. Once the condition that caused the Error has been corrected, the Clear Error button can be used to Clear the Plan Error field — for example, by recreating the missing index. Using the Clear Errors button after the error condition has been corrected causes the Scheduled Errors field and the Clear Errors button to disappear.

Routines and relationships section

The statement is defined in the following routine section:

  • routineRoutine: The class name associated with the cache query (for dynamic SQL DML), or the routine name (for embedded SQL DML).
  • Type: class method orMACRoutines (for embedded SQL DML)
  • Last compilation timeLast Compile Time: Time when the routine was last compiled or prepared. If the SQL statement is unfrozen, the recompile MAC routine updates both this timestamp and the Plan timestamp. If the SQL statement is frozen, the recompile MAC routine updates only this timestamp; The Plan timestamp does not change until you unfreeze the Plan; The Plan timestamp will then show when the planned thawing occurred.

The statement lists one or more definition tables used to create a query plan using the following relationship section. For INSERTS that use a query to extract values FROM another table, or UPDATE or DELETE that references another table using the FROM clause, both tables are listed here. Each table lists the following values:

  • Table or view nameTable or View Name: Qualified name of a table or view.
  • typeType: table or view.
  • Last compilation timeLast Compile Time: Time when the table (persistent class) was last compiled.
  • Classname: The name of the class associated with the table.

This section includes compiler class options for recompiling classes. If you recompile the unfreeze plan, all three time fields are updated. If you recompile a frozen plan, the two last compile time fields are updated, but the plan timestamp is not updated. When you unfreeze a schedule and click the Refresh page button, the schedule timestamp is updated to the scheduled unfreeze time.

Query SQL statement

SQLTableStatements() can be used to store SQL statements that a query returns for a specified table. The following example shows this:

/// w ##class(PHA.TEST.SQL).SQLTableStatements()
ClassMethod SQLTableStatements(a)
{
	SET mycall = "CALL %Library.SQLTableStatements('Sample','Person')"
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus=tStatement.%Prepare(mycall)
	IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} SET rset=tStatement.%Execute() IF rset.%SQLCODE '= 0 {WRITE "SQL error=",rset.%SQLCODE QUIT}
	DO rset.%Display()
}
Copy the code
DHC-APP>w ##class(PHA.TEST.SQL).SQLTableStatements(a)Dumping result# 1SCHEMA  RELATION_NAME   PLAN_STATE      LOCATION        STATEMENT
SAMPLE  PERSON0%sqlcq.DHCdAPP.cls2281.DECLARE C CURSOR FOR SELECT * INTO: %col(1) : %col(2) : %col(3) : %col(4) : %col(5) : %col(6) : %col(7) : %col(8) : %col(9) : %col(10) : %col(11) : %col(12) : %col(13) : %col(14) : %col(15) FROM SAMPLE . PERSON
SAMPLE  PERSON  0       Sample.Person1.SELECT AGE , DOB , FAVORITECOLORS , HOME , NAME , OFFICE , SSN , SPOUSE , X__CLASSNAME , HOME_CITY , HOME_STATE , HOME_STREET , HOME_ZIP , OFFICE_CITY , OFFICE_STATE , OFFICE_STREET , OFFICE_ZIP INTO: %e ( ) FROM %IGNOREINDEX * SAMPLE . PERSON WHERE ID = :%rowid
...
CURSOR FOR SELECT P . NAME , P . AGE , E . NAME , E . AGE FROM %ALLINDEX SAMPLE . PERSON AS P LEFT OUTER JOIN SAMPLE . EMPLOYEE AS E ON P . NAME = E . NAME WHERE P . AGE > 21 AND %NOINDEX E . AGE < 65
SAMPLE  PERSON  0       PHA.TEST.SQL1.  SELECT NAME , SPOUSE INTO :name , :spouse FROM SAMPLE . PERSON WHERE SPOUSE IS NULL
SAMPLE  PERSON  0       PHA.TEST.ObjectScript1. SELECT NAME , DOB , HOME INTO :n , :d , :h FROM SAMPLE . PERSON
 
70 Rows(s) Affected
Copy the code

The INFORMATION_SCHEMA package table can be used to query the list of SQL statements. InterSystems IRIS supports the following classes:

  • INFORMATION_SCHEMA.STATEMENTS: contains the SQL statement index entries accessible to the current user in the current namespace.
  • INFORMATION_SCHEMA.STATEMENT_LOCATIONS: contains the location of each routine that calls the SQL statement: persistent class name or cache query name.
  • INFORMATION_SCHEMA.STATEMENT_RELATIONS: contains each table or view entry used by the SQL statement.

Here are some sample queries using these classes:

The following example returns all SQL statements in the namespace, listing the hash value (the calculation ID that uniquely identifies the normalized SQL statement), the frozen status flag (values 0 to 3), the local timestamp when the statement was prepared and saved, and the statement text itself:

SELECT Hash,Frozen,Timestamp,Statement FROM INFORMATION_SCHEMA.STATEMENTS
Copy the code

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. Statements such as single rows of INSERT that cannot be frozen display NULL in frozen columns:

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

The following example returns all SQL statements for a given SQL table and the routines in which the statements reside. Note that the table name (sample.person) must be specified with the same uppercase as in the SQL statement text: all uppercase) :

SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE STATEMENT_RELATIONS->Relation='SAMPLE.PERSON'
Copy the code

The following example returns all SQL statements with freeze plans in the current namespace:

SELECT Statement,Frozen,Frozen_Different,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Frozen=1 OR Frozen=2
Copy the code

The following example returns all SQL statements in the current namespace that contain the COUNT(*) aggregate function. Note that the statement text (COUNT(*)) must be specified using the same space as the SQL statement text:

       SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Statement [ ' COUNT ( * )
Copy the code

Export and import SQL statements

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 associated query plans.

You can export a single SQL statement or all SQL statements in a namespace.

You can import previously exported XML files containing one or more SQL statements.

Note: Importing SQL statements as XML should not be confused with importing and executing SQL DDL code from a text file.

Exporting SQL statements

Export a single SQL statement:

  • Export the button using the SQL statement details page. In the SQL interface of the MANAGEMENT Portal System Explorer, select the SQL statements TAB, and then click Statements to open the SQL Statement details page. Select the Export button. This will open a dialog box that allows you to choose whether to export the file to a server (data file) or a browser.

    • Server (default) : Input/exportXMLThe full pathname of the file. When exported for the first time, the default name of this file isstatementexport.xml. Of course, you can specify different paths and file names. After the SQL statement file is successfully exported, the file name used last time becomes the default value.

    By default, the Run export in the background check box is not selected.

    • Browser: will filestatementexport.xmlExport to a new page in the user’s default browser. You can specify a different name for the browser export file, or specify other software display options.

  • Use $SYSTEM. SQL. Statement. ExportFrozenPlans () method.

Export all SQL statements in the namespace:

  • Export all statement operations using the admin portal. On the SQL page of the MANAGEMENT Portal system Explorer, select the operation drop-down list. Select Export All Statements from the list. This opens a dialog box that allows you to export all SQL statements in the namespace to the server (data file) or browser.
    • Server (default) : Enter the full path name of the exported XML file. When exported for the first time, the default name of this file isstatementexport.xml. Of course, you can specify different paths and file names. After the SQL statement file is successfully exported, the file name used last time becomes the default value.

By default, the Run Export in the background check box is selected. This is the recommended setting for exporting all SQL statements. If you select run export in the background, the system provides a link to view the background job status on the background list page.

  • Browser: will filestatementexport.xmlExport to a new page in the user’s default browser. You can specify a different name for the browser export file, or specify other software display options.

Use $SYSTEM. SQL. Statement. ExportAllFrozenPlans () method.

Importing SQL statements

Import one or more SQL statements from a previously exported file:

  • Use the import statement operation in the admin portal. On the SQL page of the MANAGEMENT Portal system Explorer, select the operation drop-down list. Select Import Statements from the list. This opens a dialog box that allows you to specify the full pathname of the imported XML file.

By default, the run import in the background check box is selected. This is the recommended setting when importing SQL statement files. If you select import in the background, the system provides a link to view background job status on the page.

Use $SYSTEM. SQL. Statement. ImportFrozenPlans () method.

View and clear background tasks

On the MANAGEMENT Portal, select background tasks to view logs about exporting and importing background tasks. You can use the Clear Log button to clear this log.