Skip to main content

Analyze SQL Statements and Statistics

This page discusses how to view and read SQL Statements and SQL Runtime Statistics to analyze query performance. Both tools are useful for understanding at a high level how well queries are performing on your system.

SQL Statements provide a record of SQL queries and other operations for each table, including insert, update, and delete. These statements are linked to a query plan, and this link provides the option to freeze this query plan. The system creates an SQL Statement for each SQL DML operation and stores them in a list, viewable in the Management Portal. If you change the table definition, you can use this list to determine whether the query plan for each SQL operation will be affected by this DML change or an SQL operation may need to be modified or both. You can then:

  • Determine which query plan to use for each SQL operation. You can decide to use a revised query plan that reflects changes made to the table definition or you can freeze the current query plan, retaining the query plan generated prior to making changes to the table definition.

  • Determine whether to make code changes to routines that perform SQL operations against that table, based on changes made to the table definition.

Note:

SQL Statements are a listing of SQL routines that may be affected by a change to a table definition. It should not be used as a history of changes to either the table definition or table data.

SQL Runtime Statistics can track basic metrics of the execution of all SQL queries to provide you with a historical overview of the performance of your queries. When the collection of these statistics is turned on, the system will automatically collect metrics on the runtime performance of your queries until the collection process times out. Analyzing these statistics can provide insights into how your schemas are performing and determine which queries may need to be examined more thoroughly using the SQL Performance Analysis Toolkit.

Operations that Create SQL Statements

The following SQL operations create corresponding SQL Statements:

  • Data management (DML) operations include queries against the table, and insert, update, and delete operations. Each data management (DML) operation (both Dynamic SQL and Embedded SQL) creates an SQL Statement when the operation is executed. Dynamic SQL SELECT commands create an entry in the Management Portal Cached Queries listing.

  • Embedded SQL cursor-based SELECT commands create an SQL Statement when the OPEN command invokes a DECLARED query. No separate entry is created in the Management Portal Cached Queries listing.

If a query references more than one table, a single SQL Statement is created in the namespace’s SQL Statements listing that lists all of the referenced tables in the Table/View/Procedure Name(s) column. The Table’s SQL Statements listing for each referenced table contains an entry for that query.

An SQL Statement is created when the query is prepared for the first time. If more than one client issues the same query only the first prepare is recorded. For example, if JDBC issues a query and then ODBC issues an identical query, the SQL Statement index would only have information about the first JDBC client and not the ODBC client.

Most SQL Statements have an associated Query Plan. When created, this Query Plan is unfrozen; you can subsequently designate this Query Plan as a frozen plan. SQL Statements with a Query Plan include DML commands that involve a SELECT operation. Also see SQL Statements without a Query Plan.

Note:

SQL Statements only list the most recent version of an SQL operation. Unless you freeze the SQL Statement, InterSystems IRIS® data platform replaces it with the next version. Thus rewriting and invoking the SQL code in a routine causes the old SQL code to disappear from SQL Statements.

Other SQL Statement Operations

The following SQL commands perform more complex SQL Statement operations:

  • CREATE TRIGGER: No SQL Statement is created in the table in which the trigger is defined, either when the trigger is defined or when it is pulled. However, if the trigger performs a DML operation on another table, defining a trigger creates an SQL Statement for the table modified by the trigger code. The Location specifies the table in which the trigger is defined. The SQL Statement is defined when the trigger is defined; dropping a trigger deletes the SQL Statement. Pulling a trigger does not create an SQL Statement.

  • CREATE VIEW does not create an SQL Statement, because nothing is compiled. It also does not change the Plan Timestamp of the SQL Statements of its source table. However, compiling a DML command for a view creates an SQL Statement for that view.

Listing SQL Statements

From the Management Portal SQL interface, you can list SQL Statements as follows:

  • SQL Statements tab: this lists all SQL Statements in the namespace, in collation sequence by schema then by table/view name within each schema. This listing only includes those tables/views for which the current user has privileges. If a SQL Statement references more than one table, the Table/View/Procedure Name(s) column lists all the referenced tables in alphabetical order.

    By clicking a column heading you can sort the list of SQL Statements by Table/View/Procedure Name(s), Plan State, Location(s), SQL Statement Text, or any other column of the list. These sortable columns enable you to quickly find, for example, all frozen plans (Plan State), all cached queries (Location(s)), or the slowest queries (Average time).

    You can use the Filter option provided with this tab to narrow the listed SQL Statements to a specified subset. A specified filter string filters on all data in the SQL Statements listing, most usefully on schema or schema.table name, routine location, or a substring found in the SQL Statement texts. A filter string is not case-sensitive, but must follow statement text punctuation whitespace (name , age, not name,age). If a query references more than one table, the Filter includes the SQL Statement if it selects for any referenced table in the Table/View/Procedure Name(s) column. The Filter option is user customized.

    The Max rows option defaults to 1,000. The maximum value is 10,000. The minimum value is 10. To list more than 10,000 SQL Statements, use INFORMATION_SCHEMA.STATEMENTS. The Page size and Max rows options are user customized.

  • Catalog Details tab: select a table and display its catalog details. This tab provides an Table’s SQL Statements button to display the SQL Statements associated with that table. Note that if a SQL Statement references more than one table, it will be listed in the Table’s SQL Statements listing for each referenced table, but only the currently selected table is listed in the Table Name column.

    By clicking a column heading you can sort the list of the table’s SQL Statements by any column of the list.

You can use INFORMATION_SCHEMA.STATEMENTS to list SQL Statements selected by various criteria, as described in Querying the SQL Statements, below.

Listing Columns

The SQL Statements tab lists all SQL statements in the namespace. The Catalog Details tab Table’s SQL Statements button lists the SQL Statements for the selected table. Both listings contain the following column headings:

  • #: a sequential numbering of the list rows. These numbers are not associated with specific SQL Statements.

  • Table/View/Procedure Name(s): the qualified SQL table (or view or procedure) name(s). If a query references multiple tables or views:

    • The SQL Statements tab for the namespace lists all of the tables and views in Table/View/Procedure Name(s) in collation sequence (case-insensitive alphabetical order). To determine which are tables and which are views, select the SQL Statement to display the SQL Statement Details, Statement Uses the Following Relations.

    • The Table’s SQL Statements lists all of the SQL Statements that reference that table. Table/View/Procedure Name(s) lists only the specified table. Therefore, the same SQL Statement can appear in the Table’s SQL Statements listings for multiple tables.

  • Plan State

  • New plan

  • Execution count

  • Execution count/day

  • Total Time

  • Average Time

  • StdDev time

  • Row Count

  • Row Count/day

  • Location(s): the location of the compiled query:

    • Dynamic SQL: the cached query name. For example %sqlcq.USER.cls2.1.

    • Embedded SQL: the routine name. For example MyESQL.

    • Stored Procedure: the class name for the stored procedure. For example Sample.procNamesJoinSP.1.

  • SQL Statement Text: the SQL Statement text (truncated to 128 characters) in normalized format, which may differ from the command text, as specified in SQL Statement text below.

You can use the Filter option provided with this tab to filter by a Location(s) column value

Plan State

The Plan State lists one of the following:

  • Unfrozen: not frozen, can be frozen.

  • Unfrozen/Parallel: not frozen, cannot be frozen.

  • Frozen/Explicit: frozen by user action, can be unfrozen.

  • Frozen/Upgrade: frozen by InterSystems IRIS version upgrade, can be unfrozen.

  • blank: no associated Query Plan:

    • An INSERT... VALUES() command creates an SQL Statement that does not have an associated Query Plan, and therefore cannot be unfrozen or frozen (the Plan State column is blank). Even though this SQL command does not produce a Query Plan, its listing in SQL Statements still is useful, because it allows you to quickly locate all the SQL operations against this table. For example, if you add a column to a table, you may want to find out where all of the SQL INSERTs are for that table so you can update these commands to include this new column.

    • A cursor-based UPDATE or DELETE command does not have an associated Query Plan, and therefore cannot be unfrozen or frozen (the Plan State column is blank). Executing the OPEN command for a declared CURSOR generates an SQL Statement with an associated Query Plan. Embedded SQL statements that use that cursor (FETCH cursor, UPDATE...WHERE CURRENT OF cursor, DELETE...WHERE CURRENT OF cursor, and CLOSE cursor) do not generate separate SQL Statements. Even though a cursor-based UPDATE or DELETE does not produce a Query Plan, its listing in SQL Statements is still useful, because it allows you to quickly locate all the SQL operations against this table.

SQL Statement Text

The SQL Statement text commonly differs from the SQL command because SQL statement generation normalizes lettercase and whitespace. Other differences are as follows:

  • If you issue a query from the Management Portal interface or the SQL Shell interface, the resulting SQL Statement differs from the query by preceding the SELECT statement with DECLARE QRS CURSOR FOR (where “QRS” can be a variety of generated cursor names). This allows the statement text to match that of the Dynamic SQL cached query.

  • If the SQL command specifies an unqualified table or view name, the resulting SQL Statement provides the schema by using either a schema search path (for DML, if provided) or the default schema name.

  • SQL Statement Text is truncated after 1024 characters. To view the complete SQL Statement Text, display the SQL Statement Details.

  • A single SQL command may result in more than one SQL Statement. For example, if a query references a view, SQL Statements displays two statement texts, one listed under the view name, the other listed under the underlying table name. Freezing either statement results in Plan State of Frozen for both statements.

  • When SQL statements are prepared via a database driver, SQL statement generation appends SQL Comment Options (#OPTIONS) to the statement text if the options are needed to generate the statement index hash. This is shown in the following example:

    DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) , :%col(5)
FROM SAMPLE . COMPANY /*#OPTIONS {"xDBCIsoLevel":0} */

Stale SQL Statements

When a routine or class associated with an SQL Statement is deleted, the SQL Statement listing is not automatically deleted. This type of SQL Statement listing is referred to as Stale. Since it is often useful to have access to this historic information and the usage statistics associated with the SQL Statement, these stale entries are preserved in the Management Portal SQL Statement listing.

You can remove these stale entries by using the Clean Stale button. Clean Stale removes all non-frozen SQL Statements for which the associated routine or class (table) is no longer present or no longer contains the SQL Statement query. Clean Stale does not remove frozen SQL Statements. You can perform the same clean stale operation using the $SYSTEM.SQL.Statement.Clean()Opens in a new tab method.

If you delete a table (persistent class) associated with an SQL Statement, the Table/View/Procedure Name(s) column is modified, as in the following example: SAMPLE.MYTESTTABLE - Deleted??; the name of the deleted table is converted to all uppercase letters and is flagged as “Deleted??”. Or, if the SQL Statement referenced more than one table: SAMPLE.MYTESTTABLE - Deleted?? Sample.Person.

  • For a Dynamic SQL query, when you delete the table the Location(s) column is blank because all cached queries associated with the table have been automatically purged. Clean Stale removes the SQL Statement.

  • For an Embedded SQL query, the Location(s) column contains the name of the routine used to execute the query. When you change the routine so that it no longer executes the original query, the Location(s) column is blank. Clean Stale removes the SQL Statement. When you delete a table used by the query, the table is flagged as “Deleted??”; Clean Stale does not remove the SQL Statement.

Note:

A system task is automatically run once per hour in all namespaces to clean up indexes for any SQL Statements that might be stale or have stale routine references. This operation is performed to maintain system performance. This internal clean-up is not reflected in the Management Portal SQL Statements listings.

Data Management (DML) SQL Statements

The Data Management Language (DML) commands that create an SQL Statements are: INSERT, UPDATE, INSERT OR UPDATE, DELETE, TRUNCATE TABLE, SELECT, and OPEN cursor for a declared cursor-based SELECT. You can use Dynamic SQL or Embedded SQL to invoke a DML command. A DML command can be invoked for a table or a view, and InterSystems IRIS creates a corresponding SQL Statement.

Note:

The system creates an SQL Statement when Dynamic SQL is prepared or when an Embedded SQL cursor is opened, not when the DML command is executed. The SQL Statement timestamp records when this SQL code invocation occurred, not when (or if) the query was executed. Thus an SQL Statement may represent a change to table data that was never actually performed.

Preparing a Dynamic SQL DML command creates a corresponding SQL Statement. The Location associated with this SQL Statement is a cached query. Dynamic SQL is prepared when SQL is executed from the Management Portal SQL interface, from the SQL Shell interface, or imported from a .txt file. Purging an unfrozen cached query flags the corresponding SQL Statement for Clean Stale deletion. Purging a frozen cached query removes the Location value for the corresponding SQL Statement. Unfreezing the SQL Statement flags it for Clean Stale deletion.

Executing a non-cursor Embedded SQL Data Management Language (DML) command creates a corresponding SQL Statement. Each Embedded SQL DML command creates a corresponding SQL Statement. If a routine contains multiple Embedded SQL commands, each Embedded SQL command creates a separate SQL Statement. (Some Embedded SQL commands create multiple SQL Statements.) The Location column of the SQL Statement listing specifies the routine that contains the Embedded SQL. In this way, SQL Statements maintains a record of each Embedded SQL DML command.

Opening a cursor-based Embedded SQL Data Management Language (DML) routine creates an SQL Statement with a Query Plan. Associated Embedded SQL statements (FETCH cursor, CLOSE cursor) do not generate separate SQL Statements. Following a FETCH cursor, an associated UPDATE table WHERE CURRENT OF cursor or DELETE FROM table WHERE CURRENT OF cursor does generate a separate SQL Statement, but no separate Query Plan.

An INSERT command that inserts literal values creates a SQL Statement with the Plan State column blank. Because this command does not create a Query Plan, the SQL Statement cannot be frozen.

SELECT Commands

Invoking a query creates a corresponding SQL Statement. It can be a simple SELECT, or a CURSOR-based SELECT/FETCH operation. The query can be issued against a table or a view. Some important details about these sorts of queries:

  • A query containing a JOIN creates an identical SQL Statement for each table. The Location is the same stored query in the listing for each table. The Statement Uses the Following Relations lists all of the tables, as described in the SQL Statement Details Routines and Relations Sections.

  • A query containing a selectItem subquery creates an identical SQL Statement for each table. The Location is the same stored query in the listing for each table. The Statement Uses the Following Relations lists all of the tables, as described in the SQL Statement Details Routines and Relations Sections.

  • A query that references an external (linked) table cannot be frozen.

  • A query containing the FROM clause %PARALLEL keyword may create more than one SQL Statement. You can display these generated SQL Statements by invoking:

    SELECT * FROM INFORMATION_SCHEMA.STATEMENT_CHILDREN

    This displays the Statement column containing the statement hash of the original query and the ParentHash column containing the statement hash of a generated version of the query.

    SQL Statements for a %PARALLEL query have a Plan State of Unfrozen/Parallel, and cannot be frozen.

  • A query containing no FROM clause, and therefore not referencing any table, still creates an SQL Statement. For example: SELECT $LENGTH('this string') creates a SQL Statement with the Table column value %TSQL_sys.snf.

SQL Statement Details

There are two ways to display the SQL Statement Details:

  • From the SQL Statements tab, select an SQL Statement by clicking the Table/View/Procedure Name(s) link in the left-hand 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 that displays the SQL Runtime Statistics page.

  • From the table’s Catalog Details tab (or the SQL Statements tab), select an SQL Statement by clicking the Statement Text link in the right-hand column. This displays the SQL Statement Details in a pop-up window.

You can use either SQL Statement Details display to view the Query Plan and to freeze or unfreeze the query plan.

SQL Statement Details provides buttons to Freeze or Unfreeze the query plan. It also provides a Clear SQL Statistics button to clear the usage statistics listed under Statement Details, an Export button to export one or more SQL Statements to a file, as well as a buttons to Refresh and to Close the page.

The SQL Statement Details display contains the following sections. Each of these sections can be expanded or collapsed by selecting the arrow icon next to the section title:

Statement Details Section

Statement Details section:

  • Plan state: Frozen/Explicit, Frozen/Upgrade, Unfrozen, or Unfrozen/Parallel. Frozen/Explicit means that this statement's plan has been frozen by an explicit user action and this frozen plan is the query plan that will be used, regardless of changes to the code that generated this SQL Statement. Frozen/Upgrade means that this statement's plan has been automatically frozen by an InterSystems IRIS version upgrade. Unfrozen means that the plan is currently unfrozen and may be frozen. Unfrozen/Parallel mean that the plan is unfrozen and uses %PARALLEL processing, and therefore cannot be frozen. A NULL (blank) plan state means that there is no associated query plan.

  • Total time: the amount of time (in seconds) that running this query has taken.

  • Version: the InterSystems IRIS version under which the plan was created. If the Plan state is Frozen/Upgrade, this is an earlier version of InterSystems IRIS. When you unfreeze a query plan, the Plan state is changed to Unfrozen and the Version is changed to the current InterSystems IRIS version.

  • Execution count: an integer count of the number of times this query has been run. A change that results in a different Query Plan for this query (such as adding an index to a table) will reset this count.

  • Average time: the average amount of time (in seconds) that running this query has taken. If the query is a cached query, the first execution of the query likely took significantly more time than subsequent executions of the optimized query from the query cache.

  • Row Count: the total number of rows returned or modified by this query.

  • Date first seen: the date the query was first run (executed). This may differ from the Last Compile Time, which is when the query was prepared.

  • Execution count/day: the average number of times this query is run per day.

  • Stddev time: the standard deviation of this query’s total runtime from the average runtime. The standard deviation measures the variability of the time for this query.

  • Row count/day: the average number of rows this query returns or modifies per day.

  • Timestamp: Initially, the timestamp when the plan was created. This timestamp is updated following a freeze / unfreeze to record the time the plan was unfrozen, not the time the plan was re-compiled. You may have to click the Refresh Page button to display the unfreeze timestamp. Comparing the Plan Timestamp with the datetime value of the routine/class that contain the statement will let you know if the routine/class is not using the same query plan if it was recompiled again.

  • Frozen plan different: if you freeze the plan, this additional field is displayed, displaying whether the frozen plan is different from the unfrozen plan. When you freeze the plan, the Statement Text and Query Plan displays the frozen plan and the unfrozen plan side-by-side for easier comparison.

  • Statement hash: an internal hash representation of the statement definition that is used as the key of the SQL Statement Index (for internal use only). Occasionally, what appear to be identical SQL statements may have different statement hash entries. Any difference in settings/options that require different code generation of the SQL statement result in a different statement hash. This may occur with different client versions or different platforms that support different internal optimizations.

Usage Statistics

In the Statement Details section, these fields provide usage statistics for the SQL statement:

  • Total time

  • Execution count

  • Average time

  • Date first seen

  • Execution count/day

  • Stddev time

  • Row Count

  • Row Count/day

This information can be used to determine which queries are the slowest and which queries are executed the most. By using this information you can determine which queries would provide significant benefits by being optimized.

Query usage statistics are periodically updated for completed query executions. You can use the Clear SQL Statistics button to clear the values of these fields.

InterSystems IRIS does not separately record usage statistics for %PARALLEL subqueries. %PARALLEL subquery statistics are summed with the statistics for the outer query. Queries generated by the implementation to run in parallel do not have their usage statistics tracked individually.

You can view these query usage statistics for multiple SQL statements in the SQL Statements tab display. You can sort the SQL Statements tab listing by any column. This makes it easy to determine, for example, which queries have the largest average time.

You can also access these query usage statistics by querying the INFORMATION.SCHEMA.STATEMENTSOpens in a new tab class properties, as described in Querying the SQL Statements. In addition, the Management Portal’s SQL Activity page provides these usage statistics for queries in progress.

Compile Settings Section

Compile Settings section:

  • Select mode: the SelectMode the statement was compiled with. For DML commands this can be set using #sqlcompile select; the default is Logical. If #sqlcompile select=Runtime, a call to the SelectMode option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method can change the query result set display, but does not change the Select Mode value, which remains Runtime.

  • Default schema(s): the default schema name that were set when the statement was compiled. This is commonly the default schema in effect when the command was issued, though SQL may have resolved the schema for unqualified names using a schema search path (if provided) rather than the default schema name. However, if the statement is a DML command in Embedded SQL using one or more #import macro directives, the schemas specified by #import directives are listed here.

  • Schema path: the schema path defined when the statement was compiled. This is the schema search path, if specified. If no schema search path is specified, this setting is blank. However, for a DML Embedded SQL command with a search path specified in an #import macro directive, the #import search path is shown in the Default schema(s) setting and this Schema path setting is blank.

  • Plan Error: This field only appears when an error occurs when using a frozen plan. For example, if a query plan uses an index, the query plan is frozen, and then the index is dropped from the table, a Plan Error occurs such as the following: Map 'NameIDX' not defined in table 'Sample.Person', but it was specified in the frozen plan for the query. Dropping or adding an index causes a recompile of the table, changing the Last Compile Time value. The Clear Error button can be used to clear the Plan Error field once the condition that caused the error has been corrected — for example, by re-creating the missing index. Using the Clear Error button after the error condition has been corrected causes both the Plan Error field and the Clear Error button to disappear. For further details, refer to Frozen Plan in Error.

Routines and Relations Sections

Statement is Defined in the Following Routines section:

  • Routine: the class name associated with the cached query (for Dynamic SQL DML), or the routine name (for Embedded SQL DML).

  • Type: Class Method or MAC Routine (for Embedded SQL DML).

  • Last Compile Time: the last compile time or prepare time for the routine. If the SQL Statement is Unfrozen, recompiling a MAC routine updates both this timestamp and the Plan Timestamp. If the SQL Statement is Frozen, recompiling a MAC routine updates only this timestamp; the Plan Timestamp is unchanged until you unfreeze the plan; the Plan Timestamp then shows the time the plan was unfrozen.

Statement Uses the Following Relations section lists one or more defined tables used to create the query plan. For an INSERT that uses a query to extract values from another table, or an UPDATE or DELETE that uses a FROM clause to reference another table, both tables are listed here. For each table the following values are listed:

  • Table or View Name: the qualified name of the table or view.

  • Type: Table or View.

  • Last Compile Time: The time the table (persistent class) was last compiled.

  • Classname: the classname associated with the table.

This section includes a Compile Class option to re-compile the class. If you re-compile an unfrozen plan, all three time fields are updated. If you re-compile a frozen plan, the two Last Compile Time fields are updated, but the Plan Timestamp is not. When you unfreeze the plan and click the Refresh Page button, the Plan Timestamp updates to the time the plan was unfrozen.

Querying SQL Statements

You can use the INFORMATION_SCHEMA package tables to query the list of SQL Statements. InterSystems IRIS supports the following classes:

  • INFORMATION_SCHEMA.STATEMENTS: Contains SQL Statement Index entries that can be accessed by the current user in the current namespace.

  • INFORMATION_SCHEMA.STATEMENT_LOCATIONS: Contains each routine location from which an SQL statement is invoked: the persistent class name or the cached query name.

  • INFORMATION_SCHEMA.STATEMENT_RELATIONS: Contains each table or view entry use by an SQL statement.

  • INFORMATION_SCHEMA.CURRENT_STATEMENTS: Contains SQL Statement Index entries which are currently executing in any namespace on the system. Users with the access to the %Admin_Operate resource can explore the contents of this table at any time via the Management Portal, on the SQL Activity page.

Some example queries that use these classes are as follows:

  1. SELECT Hash,Frozen,Timestamp,Statement
FROM INFORMATION_SCHEMA.STATEMENTS

    This example returns all of the SQL Statements in the namespace, listing the hash value (a computed Id that uniquely identifies the normalized SQL statement), the frozen status flag (values 0 through 3), the local timestamp when the statement was prepared and the plan saved, and the statement text itself.

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

    This 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. A statement such as a single row INSERT, that cannot be frozen, displays NULL in the Frozen column.

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

    This example returns all the SQL Statements and the routines the statements are located in for a given SQL table. (Note that the table name (SAMPLE.PERSON) must be specified with the same letter case used in the SQL Statement text: all uppercase letters).

  4. 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

    This example returns all the SQL Statements in the current namespace that have frozen plans.

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

    This example returns all the SQL Statements in the current namespace that contain a COUNT(*) aggregate function. Note that the statement text (COUNT ( * )) must be specified with the same whitespace used in the SQL Statement text.

Exporting and Importing SQL Statements

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 the associated query plan.

You can export a single SQL Statement or export all of the SQL Statements in the namespace.

You can import a previously-exported XML file containing one or more SQL Statements.

Note:

This import of SQL Statements as XML should not be confused with the import and execution of SQL DDL code from a text file.

Exporting SQL Statements

Export a single SQL Statement:

  • Use the SQL Statement Details page Export button. From the Management Portal System Explorer SQL interface, select the SQL Statements tab and click on a statement to open up the SQL Statement Details page. Select the Export button. This opens a dialog box, allowing you to select to export the file to Server (a data file) or Browser.

    • Server (the default): Enter the full path name of the export xml file. The first time you export, this file has a default name of statementexport.xml. You can, of course, specify a different path and file name. After you have successfully exported an SQL Statement file, the last used file name becomes the default.

      The Run export in the background check box is not selected by default.

    • Browser: Exports the file statementexport.xml to a new page in the user’s default browser. You can specify another name for the browser export file, or specify a different software display option.

  • Use the $SYSTEM.SQL.Statement.ExportFrozenPlans()Opens in a new tab method.

Export all SQL Statements in the namespace:

  • Use the Export All Statements Action from the Management Portal. From the Management Portal System Explorer SQL interface, select the Actions drop-down list. From that list select Export All Statements. This opens a dialog box, allowing you to export all SQL Statements in the namespace to Server (a data file) or Browser.

    • Server (the default): Enter the full path name of the export xml file. The first time you export, this file has a default name of statementexport.xml. You can, of course, specify a different path and file name. After you have successfully exported an SQL Statement file, the last used file name becomes the default.

      The Run export in the background check box is selected by default. This is the recommended setting when exporting all SQL Statements. When Run export in the background is checked, you are provided with a link to view the background list page where you can see the background job status.

    • Browser: Exports the file statementexport.xml to a new page in the user’s default browser. You can specify another name for the browser export file, or specify a different software display option.

  • Use the $SYSTEM.SQL.Statement.ExportAllFrozenPlans()Opens in a new tab method.

Importing SQL Statements

Import an SQL Statement or multiple SQL Statements from a previously-exported file:

  • Use the Import Statements Action from the Management Portal. From the Management Portal System Explorer SQL interface, select the Actions drop-down list. From that list select Import Statements. This opens a dialog box, allowing you to specify the full path name of the import XML file.

    The Run import in the background check box is selected by default. This is the recommended setting when importing a file of SQL Statements. When Run import in the background is checked, you are provided with a link to view the background list page where you can see the background job status.

  • Use the $SYSTEM.SQL.Statement.ImportFrozenPlans()Opens in a new tab method.

Viewing and Purging Background Tasks

From the Management Portal System Operation option, select Background Tasks to view the log of export and import background tasks. You can use the Purge Log button to clear this log.

SQL Runtime Statistics

You can use SQL Runtime Statistics to monitor general performance statistics, such as query runtime or commands executed, of SELECT queries, as well as DDL and DML statements running on your system. SQL runtime statistics are gathered when a query operation is prepared and are viewable from the INFORMATION_SCHEMA.STATEMENTS, STATEMENT_DAILY_STATS, and STATEMENT_HOURLY_STATS tables. In addition, if parameter sampling is enabled, you can view statistics collected about runtime parameters in the INFORMATION_SCHEMA.STATEMENT_PARAMETER_STATS table.

The gathering of SQL runtime statistics is always on and cannot be turned off. To ensure that the gathering of statistics is as efficient as possible, the statistics are only written at set intervals. As a result, it may take up to 30 minutes to see gathered runtime statistics. To see which statements are currently running, query the INFORMATION_SCHEMA.CURRENT_STATEMENTS table.

Runtime statistics include:

  • Avg Time: the average length of time the query takes in seconds

  • Run Count: the number of times the query has been run

  • Avg Rows: the average number of rows returned

  • Avg Commands: the average number of commands executed

You can explicitly purge (clear) SQL runtime statistics. Purging a cached query deletes any related SQL runtime statistics. Dropping a table or view deletes any related SQL runtime statistics.

Note:

A system task is automatically run once per hour in all namespaces to aggregate process-specific SQL query statistics into global statistics. Therefore, the global statistics may not reflect statistics gathered within the hour.

Runtime Statistics and Show Plan

The SQL Runtime Statistics tool can be used to display the Show Plan for a query with runtime statistics.

The Alternate Show Plans tool can be used to compare show plans with statistics, displaying runtime statistics for a query. The Alternate Show Plans tool in its Show Plan Options displays estimated statistics for a query. If gathering runtime statistics is activated, its Compare Show Plans with Stats option displays actual runtime statistics; if runtime statistics are not active, this option displays estimate statistics.

Interpreting an SQL Query PlanNext section
FeedbackOpens in a new tab