docs.intersystems.com
Home / InterSystems SQL Optimization Guide / Optimizing Query Performance

InterSystems SQL Optimization Guide
Optimizing Query Performance
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


InterSystems SQL automatically uses a Query Optimizer to create a query plan that provides optimal query performance in most circumstances. This Optimizer improves query performance in many ways, including determining which indices to use, determining the order of evaluation of multiple AND conditions, determining the sequence of tables when performing multiple joins, and many other optimization operations. You can supply “hints” to this Optimizer in the FROM clause of the query. This chapter describes tools that you can use to evaluate a query plan and to modify how InterSystems SQL will optimize a specific query.
InterSystems IRIS Data Platform™ supports the following tools for optimizing SQL queries:
You can direct the Query Optimizer by using the following options, either by setting configuration defaults or by coding optimizer “hints” in the query code:
The following SQL query performance tools are described in other chapters of this manual:
The following tools are used to optimize table data, and thus can have a significant effect on all queries run against that table:
This chapter also describes how to Write Query Optimization Plans to a File, and how to generate an SQL Troubleshooting Report to submit to InterSystems WRC.
Management Portal SQL Performance Tools
The InterSystems IRIS Management Portal provides access to the following SQL performance tools. There are two ways to access these tools from the Management Portal System Explorer option:
From either interface you can select one of the following SQL performance tools:
The %SYS.PTools Package
The %SYS.PTools package contains performance analysis classes and their methods. It includes:
It also contains several deprecated classes.
Methods in these classes can be invoked either from ObjectScript, or from the SQL CALL or SELECT command. The SQL naming convention is to specify the package name %SYS_PTools, then prefix “PT_” to the method name that begins with a lower-case letter. This is shown in the following examples:
ObjectScript:
  DO ##class(%SYS.PTools.UtilSQLAnalysis).indexUsage()
SQL:
  CALL %SYS_PTools.PT_indexUsage()
  SELECT %SYS_PTools.PT_indexUsage()
SQL Runtime Statistics
You can use SQL Runtime Statistics to measure the performance of SQL queries on your system. SQL Runtime Statistics measures the performance of SELECT, INSERT, UPDATE, and DELETE operations (collectively known as query operations). SQL runtime statistics (SQL Stats) are gathered when a query operation is Prepared.
Gathering of SQL runtime statistics is off by default. You must activate the gathering of statistics. It is highly recommended that you specify a timeout to end the gathering of statistics. After activate the gathering of statistics, you must recompile (Prepare) existing Dynamic SQL queries and recompile classes and routines that contain Embedded SQL.
Performance statistics include the ModuleName, ModuleCount (the number of times a module is called), RowCount (number of rows returned), TimeSpent (execution performance in seconds), GlobalRefs (number of global references), LinesOfCode (number of lines executed), and the ReadLatency (the disk read access time, in milliseconds). For details, see Stats Values.
You can explicitly purge (clear) SQL Stats data. Purging a cached query deletes any related SQL Stats data. Dropping a table or view deletes any related SQL Stats data.
Runtime Statistics Interfaces
InterSystems IRIS provides several interfaces you can use to gather and display SQL runtime statistics:
Using the SQL Runtime Statistics Tool
You can display performance statistics for SQL queries system-wide from the Management Portal using either of the following:
Settings
The Settings tab displays the current system-wide SQL Runtime Statistics setting and when this setting will expire.
the Change Settings button allows you to set the following statistics collection options:
Purge Cached Queries Button
The Purge Cached Queries button deletes all of cached queries in the current namespace. You may need to purge cached queries when changing the Collection Option, as described below.
Query Test
The Query Test tab allows you to input an SQL query text (or retrieve one from History) and then display the SQL Stats and Query Plan for that query. Query Test includes the SQL Stats for all module levels of the query, regardless of the Collection Option setting.
Input an SQL query text, or retrieve one using the Show History button. You can clear the query text field by clicking the round "X" circle on the right hand side.
Use the Show Plan With SQL Stats button to execute.
If the Run Show Plan process in the background check box is selected, you will see a progress bar displayed with a "Please wait..." message. While a long query is being run, the Show Plan With SQL Stats and Show History buttons disappear and a View Process button is shown. Clicking View Process opens the Process Details page in a new tab. From the Process Details page, you can view the process, and may Suspend, Resume or Terminate the process. The status of the process should be reflected on the Show Plan page. When the process is finished, the Show Plan shows the result. The View Process button disappears and the Show Plan With SQL Stats and Show History buttons reappear.
The Statement Text displayed using Query Test includes comments and does not perform literal substitution.
View Stats
The View Stats tab gives you an overall view of the runtime statistics that have been gathered on this system.
You can click on any one of the View Stats column headers to sort the query statistics. You can then click the SQL Statement text to view the detailed Query Statistics and the Query Plan for the selected query.
The Statement Text displayed using this tool includes comments and does not perform literal substitution. The Statement Text displayed by exportStatsSQL() and by Show Plan strips out comments and performs literal substitution.
Purge Stats Button
The Purge Stats button clears all of the accumulated statistics for all queries in the current namespace. It displays a message on the SQL Runtime Statistics page. If successful, a message indicates the number of stats purged. If there were no stats, the Nothing to purge message is displayed. If the purge was unsuccessful, an error message is displayed. For additional options, refer to Delete SQL performance statistics.
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 stats, 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.
Using Performance Statistics Methods
You can use %SYS.PTools.StatsSQL class methods to:
This section also contains program examples using these methods.
Activate the Gathering of Statistics
You activate statistics (Stats) code generation to collect performance statistics using the %SYS.PTools.StatsSQL class methods. The following methods are provided to gather performance statistics for:
These methods take an integer action option. They return a colon-separated string, the first element of which is the prior statistics action option. You can determine the current settings using the GetSQLStatsFlag() or GetSQLStatsFlagByPID() method.
You can invoke these method from ObjectScript or from SQL as shown in the following examples:
Action Option
For SetSQLStats() and SetSQLStatsFlagByNS() you specify one of the following Action options: 0 turn off statistics code generation; 1 turn on statistics code generation for all queries, but do not gather statistics (the default); 2 record statistics for just the outer loop of the query (gather statistics at the open and close of the query); 3 record statistics for all module levels of the query. Modules can be nested. If so, the MAIN module statistics are inclusive numbers, the overall results for the full query.
For SetSQLStatsFlagJob() and SetSQLStatsFlagByPID() the Action options differ slightly. They are: -1 turn off statistics for this job; 0 use the system setting value. The 1, 2, and 3 options are the same as SetSQLStats() and override the system setting. The default is 0.
To gather SQL Stats data, queries need to be compiled (Prepared) with statistics code generation turned on (option 1, the default):
Collect Option
If the Action option is 2 or 3, when you invoke one of these methods you can specify a Collect option value to specify which performance statistics to collect. The default is to collect all statistics.
You specify a Collect option by adding together the integer values associated with each type of statistic that you wish to collect. The default is 15 (1 + 2 + 4 + 8).
These methods return the prior value of this Collect option as the second colon-separated element. You can determine the current setting using the GetSQLStatsFlag() or GetSQLStatsFlagByPID() method. By default all statistics are collected, returning 15 as the second element value.
Refer to %SYS.PTools.StatsSQL for further details.
Terminate Option
Statistics collection continues until terminated. By default, collection continues indefinitely until it is terminated by issuing another SetSQLStats[nnn]() method. Or, if the Action option is 1, 2, or 3, you can specify a SetSQLStats[nnn]() terminate option, either an elapsed period (in minutes) or a specified timestamp. You then specify the Action option re-set when that period elapses. For example, the string "M:120:1" sets M (elapsed minutes) to 120 minutes, at the end of which the Action option re-sets to 1. All other options reset to the default values appropriate for that Action option.
These methods return the prior value of this Terminate option value as the fifth colon-separated element as an encoded value. See Get Statistics Settings.
Get Statistics Settings
The SetSQLStats[nnn]() methods return the prior statistics settings as a colon-separated value. You can determine the current settings using the GetSQLStatsFlag() or GetSQLStatsFlagByPID() method.
The 1st colon-separated value is the Action option setting. The 2nd colon-separated value is the Collect option. The 3rd and 4th colon-separated values are used for namespace-specific statistics gathering. The 5th colon-separated value encodes the Terminate option.
You can use the ptInfo array to display the Terminate option settings in greater detail, as shown in the following example:
  KILL
  DO ##class(%SYS.PTools.StatsSQL).clearStatsSQL()
  DO ##class(%SYSTEM.SQL).SetSQLStatsFlagByNS("USER",3,,7,"M:5:1")
DisplaySettings
  SET SQLStatsFlag = ##class(%SYS.PTools.StatsSQL).GetSQLStatsFlag(0,0,.ptInfo)
  WRITE "ptInfo array of SQL Stats return value:",!
  ZWRITE ptInfo,SQLStatsFlag
Export Query Performance Statistics
You can export query performance statistics to a file using the exportStatsSQL() method of %SYS.PTools.StatsSQL. This method is used to export statistics data from %SYS.PTools.StatsSQL classes to a file.
You can invoke exportStatsSQL() as shown in the following examples:
If you don't specify a filename argument, this method exports to the current directory. By default, this file is named PT_StatsSQL_exportStatsSQL_ followed by the current local date and time as YYYYMMDD_HHMMSS. You can specify $IO to output the data to the Terminal or Management Portal display. If you specify a filename argument, this method creates a file in the Mgr subdirectory for the current namespace, or in the path location you specify. This export is limited to data in the current namespace.
You can specify the output file format as P (text), D (comma-separated data), X (XML markup), H (HTML markup), or Z (user-defined delimiter).
By default this method exports the query performance statistics. You can specify that it instead export the SQL query text or the SQL Query Plan data, as shown in the following examples:
exportStatsSQL() modifies the query text by stripping out comments and performing literal substitution.
The same query text and query plan data can be returned by ExportSQLQuery().
Stats Values
The following statistics are returned:
Delete Query Performance Statistics
You can use the clearStatsSQL() method to delete performance statistics. By default, it deletes statistics gathered for all routines in the current namespace. You can specify a different namespace, and/or limit deletion to a specific routine.
You can use the clearStatsSQLAllNS() method to delete performance statistics from all namespaces. By default, it deletes statistics gathered for all routines. You can limit deletion to a specific routine.
Performance Statistics Examples
The following example gathers performance statistics on the main module of a query (Action option 2) that was prepared by the current process, then uses the exportStatsSQL() to display the performance statistics to the Terminal.
  DO ##class(%SYS.PTools.StatsSQL).clearStatsSQL()
  DO $SYSTEM.SQL.SetSQLStatsFlagJob(2)
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET pStatus = ##class(%SYS.PTools.StatsSQL).exportStatsSQL("$IO")
    IF pStatus'=1 {WRITE "Performance stats display failed:" DO $System.Status.DisplayError(qStatus) QUIT}
The following example gathers performance statistics on all modules of a query (Action option 3) that was prepared by the current process, then calls exportStatsSQL() from Embedded SQL to display the performance statistics to the Terminal:
  DO ##class(%SYS.PTools.StatsSQL).clearStatsSQL()
  DO $SYSTEM.SQL.SetSQLStatsFlagJob(3)
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  &sql(CALL %SYS_PTools.PT_exportStatsSQL('$IO'))
The following example gathers performance statistics on the main module of a query (Action option 2) that was prepared by the current process, then uses the StatsSQLView query to display these statistics:
  DO ##class(%SYS.PTools.StatsSQL).clearStatsSQL()
  DO ##class(%SYSTEM.SQL).SetSQLStatsFlagJob(2)
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
GetStats
  SET qStatus = tStatement.%Prepare("SELECT * FROM %SYS_PTools.StatsSQLView")
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rsstats = tStatement.%Execute()
  DO rsstats.%Display()
  WRITE !!,"End of SQL Statistics"
The following example gathers performance statistics on all modules (Action option 3) of all queries in the USER namespace. When the statistics collection time expires after 1 minute, it re-sets to Action option 2 and the scope of collecting defaults to 15 (all statistics) on all namespaces:
  DO ##class(%SYS.PTools.StatsSQL).clearStatsSQL()
  DO ##class(%SYSTEM.SQL).SetSQLStatsFlagByNS("USER",3,,7,"M:1:2")
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
GetStats
  SET qStatus = tStatement.%Prepare("SELECT * FROM %SYS_PTools.StatsSQLView")
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rsstats = tStatement.%Execute()
  DO rsstats.%Display()
  WRITE !!,"End of SQL Statistics",!
TerminateResetStats
  WRITE "returns:  ",##class(%SYS.PTools.StatsSQL).GetSQLStatsFlag(),!
  HANG 100
  WRITE "reset to: ",##class(%SYS.PTools.StatsSQL).GetSQLStatsFlag()
Using Indices
Indexing provides a mechanism for optimizing queries by maintaining a sorted subset of commonly requested data. Determining which fields should be indexed requires some thought: too few or the wrong indices and key queries will run too slowly; too many indices can slow down INSERT and UPDATE performance (as the index values must be set or updated).
What to Index
To determine if adding an index improves query performance, run the query from the Management Portal SQL interface and note in Performance the number of global references. Add the index and then rerun the query, noting the number of global references. A useful index should reduce the number of global references. You can prevent use of an index by using the %NOINDEX keyword as preface to a WHERE clause or ON clause condition.
You should index fields (properties) that are specified in a JOIN. A LEFT OUTER JOIN starts with the left table, and then looks into the right table; therefore, you should index the field from the right table. In the following example, you should index T2.f2:
   FROM Table1 AS T1 LEFT OUTER JOIN Table2 AS T2 ON T1.f1 = T2.f2
An INNER JOIN should have indices on both ON clause fields.
Run Show Plan and follow to the first map. If the first bullet item in the Query Plan is “Read master map”, or the Query Plan calls a module whose first bullet item is “Read master map”, the query first map is the master map rather than an index map. Because the master map reads the data itself, rather than an index to the data, this almost always indicates an inefficient Query Plan. Unless the table is relatively small, you should create an index so that when you rerun this query the Query Plan first map says “Read index map.”
You should index fields that are specified in a WHERE clause equal condition.
You may wish to index fields that are specified in a WHERE clause range condition, and fields specified in GROUP BY and ORDER BY clauses.
Under certain circumstances, an index based on a range condition could make a query slower. This can occur if the vast majority of the rows meet the specified range condition. For example, if the query clause WHERE Date < CURRENT_DATE is used with a database in which most of the records are from prior dates, indexing on Date may actually slow down the query. This is because the Query Optimizer assumes range conditions will return a relatively small number of rows, and optimizes for this situation. You can determine if this is occurring by prefacing the range condition with %NOINDEX and then run the query again.
If you are performing a comparison using an indexed field, the field as specified in the comparison should have the same collation type as it has in the corresponding index. For example, the Name field in the WHERE clause of a SELECT or in the ON clause of a JOIN should have the same collation as the index defined for the Name field. If there is a mismatch between the field collation and the index collation, the index may be less effective or may not be used at all. For further details, refer to Index Collation in the “Defining and Building Indices” chapter of this manual.
For details on how to create an index and the available index types and options, refer to the CREATE INDEX command in the InterSystems SQL Reference, and the Defining and Building Indices chapter of this manual.
Index Configuration Options
The following system-wide configuration methods can be used to optimize use of indices in queries:
For further details, refer to SQL configuration settings described in Advanced Configuration Settings Reference.
Index Usage Analysis
You can analyze index usage by SQL cached queries using either of the following:
Index Analyzer
You can analyze index usage for SQL queries from the Management Portal using either of the following:
The Index Analyzer provides an SQL Statement Count display for the current namespace, and five index analysis report options.
SQL Statement Count
At the top of the SQL Index Analyzer there is an option to count all SQL statements in the namespace. Press the Gather SQL Statements button. The SQL Index Analyzer displays “Gathering SQL statements ....” while the count is in progress, then “Done!” when the count is complete. SQL statements are counted in three categories: a Cached Query count, a Class Method count, and a Class Query count. These counts are for the entire current namespace, and are not affected by the Schema Selection option.
The corresponding method is getSQLStmts() in the %SYS.PTools.UtilSQLAnalysis class.
You can use the Purge Statements button to delete all gathered statements in the current namespace. This button invokes the clearSQLStatements() method.
Report Options
You can either examine reports for the cached queries for a selected schema in the current namespace, or (by not selecting a schema) examine reports for all cached queries in the current namespace. You can skip or include system class queries, INSERT statements, and/or IDKEY indices in this analysis. The schema selection and skip option check boxes are user customized.
The index analysis report options are:
When you select one of these options, the system automatically performs the operation and displays the results. The first time you select an option or invoke the corresponding method, the system generates the results data; if you select that option or invoke that method again, InterSystems IRIS redisplays the same results. To generate new results data you must use the Gather SQL Statements button to reinitialize the Index Analyzer results tables. To generate new results data for the %SYS.PTools.UtilSQLAnalysis methods, you must invoke getSQLStmts() to reinitialize the Index Analyzer results tables. Changing the Skip all system classes and routines or Skip INSERT statements check box option also reinitializes the Index Analyzer results tables.
indexUsage() Method
The following example demonstrates the use of the indexUsage() method:
  DO ##class(%SYS.PTools.UtilSQLAnalysis).indexUsage(1,1)
  SET utils = "SELECT %EXACT(Type), Count(*) As QueryCount "_
              "FROM %SYS_PTools.UtilSQLStatements GROUP BY Type"
  SET utilresults = "SELECT SchemaName, Tablename, IndexName, UsageCount "_
                    "FROM %SYS_PTools.UtilSQLAnalysisDB ORDER BY UsageCount"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(utils)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of utilities data",!!
  SET qStatus = tStatement.%Prepare(utilresults)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of results data"
Note that because results are ordered by UsageCount, indices with UsageCount > 0 are listed at the end of the result set.
Index Optimization Options
By default, the InterSystems SQL query optimizer uses sophisticated and flexible algorithms to optimize the performance of complex queries involving multiple indices. In most cases, these defaults provide optimal performance. However, in infrequent cases, you may wish to give “hints” to the query optimizer by specifying optimize-option keywords.
The FROM clause supports the %ALLINDEX and %IGNOREINDEX optimize-option keywords. These optimize-option keywords govern all index use in the query. They are described in detail in the FROM clause reference page of the InterSystems SQL Reference.
You can use the %NOINDEX condition-level hint to specify exceptions to the use of an index for a specific condition. The %NOINDEX hint is placed in front of each condition for which no index should be used. For example, WHERE %NOINDEX hiredate < ?. This is most commonly used when the overwhelming majority of the data is selected (or not selected) by the condition. With a less-than (<) or greater-than (>) condition, use of the %NOINDEX condition-level hint is often beneficial. With an equality condition, use of the %NOINDEX condition-level hint provides no benefit. With a join condition, %NOINDEX is supported for ON clause joins.
The %NOINDEX keyword can be used to override indexing optimization established in the FROM clause. In the following example, the %ALLINDEX optimization keyword applies to all condition tests except the E.Age condition:
  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
Show Plan
Show Plan displays the execution plan for SELECT, UPDATE, DELETE, TRUNCATE TABLE, and some INSERT operations. These are collectively known as query operations because they use a SELECT query as part of their execution. Show Plan is performed when a query operation is prepared; you do not have to actually execute the query operation to generate an execution plan.
Show Plan displays what InterSystems IRIS considers to be the optimal execution plan. For generated %PARALLEL and Sharded queries, Show Plan outputs all of the applicable execution plans.
Note that for most queries there is more than one possible execution plan. In addition to the execution plan that InterSystems IRIS deems as optimal, you can also display alternate show plans.
Displaying an Execution Plan
You can use Show Plan to display the execution plan for a query in any of the following ways:
Show Plan by default returns values in Logical mode. However, when invoking Show Plan from the Management Portal or the SQL Shell, Show Plan uses Runtime mode.
Execution Plan: Statement Text and Query Plan
The Show Plan execution plan consists of two components, Statement Text and Query Plan:
Statement Text replicates the original query, with the following modifications: The Show Plan button from the Management Portal SQL interface displays the SELECT query with comments and line breaks removed. Whitespace is standardized. The Show Plan button display also performs literal substitution, replacing each literal with a ?, unless you have suppressed literal substitution by enclosing the literal value in double parentheses. These modifications are not performed when displaying a show plan using the ShowPlan() method, or when displayed using the SQL Runtime Statistics or Alternate Show Plans tools.
Query Plan shows the plan that would be used to execute the query. A Query Plan can include the following:
Some operations create a Show Plan that indicates no Query Plan could be generated:
Alternate Show Plans
You can display alternate execution plans for a query using the Management Portal or the ShowPlanAlt() method.
To display alternate execution plans for a query from the Management Portal using either of the following:
Using the Alternate Show Plans tool:
  1. Input an SQL query text, or retrieve one using the Show History button. You can clear the query text field by clicking the round "X" circle on the right hand side.
  2. Press the Show Plan Options button to display multiple alternate show plans. It is recommended that you select the Run ... in the background check box for large or complex queries. While a long query is being run in background a View Process button is shown. Clicking View Process opens the Process Details page in a new tab. From the Process Details page, you can view the process, and may Suspend, Resume or Terminate the process.
  3. From the list of possible plans, select the plans that you wish to compare, then press the Compare Show Plans with Stats button to run them and display their SQL statistics.
The ShowPlanAlt() method shows all of the execution plans for a query. It first shows the plan the InterSystems IRIS considers optimal (lowest cost), the same Show Plan display as the ShowPlan() method. ShowPlanAlt() then allows you to select an alternate plan to display. Alternate plans are listed in ascending order of cost. Specify the ID number of an alternate plan at the prompt to display its execution plan. ShowPlanAlt() then prompts you for the ID of another alternate plan. To exit this utility, press the return key at the prompt.
The following example displays the same execution plan as the ShowPlan() example, then lists alternate plans and prompts you to specify an alternate plan for display:
  DO $SYSTEM.SQL.SetSQLStatsFlagJob(3)
  SET mysql=1
  SET mysql(1)="SELECT TOP 4 Name,DOB FROM Sample.Person ORDER BY Age"
  DO $SYSTEM.SQL.ShowPlanAlt(.mysql,0,1)
To display an alternate plan, specify the plan’s ID number from the displayed list and press Return. To exit ShowPlanAlt(), just press Return.
Also refer to the possiblePlans methods in the %SYS.PTools.StatsSQL class.
Stats
The Show Plans Options lists assigns each alternate show plan a Cost value, which enables you to make relative comparisons between the execution plans.
The Alternate Show Plan details provides for each Query Plan a set of stats (statistics) for the Query Totals, and (where applicable) for each Query plan module. The stats for each module include Time (overall performance, in seconds), Global Refs (number of global references), Commands (number of lines executed), and Read Latency (disk wait, in milliseconds). The Query Totals stats also includes the number of Rows Returned.
Writing Query Optimization Plans to a File
The following utility lists the query optimization plan(s) for one or more queries to a text file.
QOPlanner^%apiSQL(infile,outfile,eos,schemapath)
infile A file pathname to a text file containing a listing of cached queries. Specified as a quoted string.
outfile A file pathname where query optimization plans are to be listed. Specified as a quoted string. If the file does not exist, the system creates it. If the file already exists, InterSystems IRIS overwrites it.
eos Optional — The end-of-statement delimiter used to separate the individual cached queries in the infile listing. Specified as a quoted string. The default is “GO”. If this eos string does not match the cached query separator, no outfile is generated.
schemapath Optional — A comma-separated list of schema names that specifies a schema search path for unqualified table names, view names, or stored procedure names. Can include DEFAULT_SCHEMA, the current system-wide default schema. If infile contains #Import directives, QOPlanner adds these #Import package/schema names to the end of schemapath.
The following is an example of evoking this query optimization plans listing utility. This utility takes as input the file generated by the ExportSQL^%qarDDLExport() utility, as described in Listing Cached Queries to a File section of the “Cached Queries” chapter. You can either generate this query listing file, or write a query (or queries) to a text file.
  DO QOPlanner^%apiSQL("C:\temp\test\qcache.txt","C:\temp\test\qoplans.txt","GO")
When executed from the Terminal command line progress is displayed to the terminal screen, such as the following example:
Importing SQL Statements from file: C:\temp\test\qcache.txt
 
Recording any errors to principal device and log file: C:\temp\test\qoplans.txt
  
  SQL statement to process (number 1):
      SELECT TOP ? P . Name , E . Name FROM Sample . Person AS P , 
      Sample . Employee AS E ORDER BY E . Name
  Generating query plan...Done
 
  SQL statement to process (number 2):
      SELECT TOP ? P . Name , E . Name FROM %INORDER Sample . Person AS P 
      NATURAL LEFT OUTER JOIN Sample . Employee AS E ORDER BY E . Name
  Generating query plan...Done
 
Elapsed time: .16532 seconds
The created query optimization plans file contains entries such as the following:
<pln>
<sql>
 SELECT TOP ? P . Name , E . Name FROM Sample . Person AS P , Sample . Employee AS E ORDER BY E . Name
</sql>
Read index map Sample.Employee.NameIDX.
Read index map Sample.Person.NameIDX.
</pln>
######
<pln>
<sql>
 SELECT TOP ? P . Name , E . Name FROM %INORDER Sample . Person AS P 
    NATURAL LEFT OUTER JOIN Sample . Employee AS E ORDER BY E . Name
</sql>
Read master map Sample.Person.IDKEY.
Read extent bitmap Sample.Employee.$Employee.
Read master map Sample.Employee.IDKEY.
Update the temp-file.
Read the temp-file.
Read master map Sample.Employee.IDKEY.
Update the temp-file.
Read the temp-file.
</pln>
######
You can use the query optimization plan text files to compare generated optimization plans using different variants of a query, or compare optimization plans between different versions of InterSystems IRIS.
When exporting the SQL queries to the text file, a query that comes from a class method or class query will be preceded by the code line:
#import <package name>
This #Import statement tells the QOPlanner utility what default package/schema to use for the plan generation of the query. When exporting the SQL queries from a routine, any #import lines in the routine code prior to the SQL statement will also precede the SQL text in the export file. Queries exported to the text file from cached queries are assumed to contain fully qualified table references; if a table reference in a text file is not fully qualified, the QOPlanner utility uses the system-wide default schema that is defined on the system when QOPlanner is run.
Comment Options
You can specify one or more comment options to the Query Optimizer within a SELECT, INSERT, UPDATE, DELETE, or TRUNCATE TABLE command. A comment option specifies a option that the query optimizer uses during the compile of the SQL query. Often a comment option is used to override a system-wide configuration default for a specific query.
Syntax
The syntax /*#OPTIONS */, with no space between the /* and the #, specifies a comment option. A comment option is not a comment; it specifies a value to the query optimizer. A comment option is specified using JSON syntax, commonly a key:value pair such as the following: /*#OPTIONS {"optionName":value} */. More complex JSON syntax, such as nested values, is supported.
A comment option is not a comment; it may not contain any text other than JSON syntax. Including non-JSON text within the /* ... */ delimiters results in an SQLCODE -153 error. InterSystems SQL does not validate the contents of the JSON string.
The #OPTIONS keyword must be specified in uppercase letters. No spaces should be used within the curly brace JSON syntax. If the SQL code is enclosed with quote marks, such as a Dynamic SQL statement, quote marks in the JSON syntax should be doubled. For example: myquery="SELECT Name FROM Sample.MyTest /*#OPTIONS {""optName"":""optValue""} */".
You can specify a /*#OPTIONS */ comment option anywhere in SQL code where a comment can be specified. In displayed statement text, the comment options are always shown as comments at the end of the statement text.
You can specify multiple /*#OPTIONS */ comment options in SQL code. They are shown in returned Statement Text in the order specified. If multiple comment options are specified for the same option, the last-specified option value is used.
The following comment options are documented:
Display
The /*#OPTIONS */ comment options display at the end of the SQL statement text, regardless of where they were specified in the SQL command. Some displayed /*#OPTIONS */ comment options are not specified in the SQL command, but are generated by the compiler pre-processor. For example /*#OPTIONS {"DynamicSQLTypeList": ...} */.
The /*#OPTIONS */ comment options display in the Show Plan Statement Text, in the Cached Query Query Text, and in the SQL Statement Statement Text.
A separate cached query is created for queries that differ only in the /*#OPTIONS */ comment options.
Parallel Query Processing
Parallel query hinting directs the system to perform parallel query processing when running on a multi-processor system. This can substantially improve performance of certain types of queries. The SQL optimizer determines whether a specific query could benefit from parallel processing, and performs parallel processing where appropriate. Specifying parallel query hinting does not force parallel processing of every query, only those that may benefit from parallel processing. If the system is not a multi-processor system, this option has no effect. To determine the number of processors on the current system use the %SYSTEM.Util.NumberOfCPUs() method.
You can specify parallel query processing in two ways:
Parallel query processing is applied to SELECT queries. It is not applied to INSERT, UPDATE, or DELETE operations.
System-Wide Parallel Query Processing
You can configure system-wide automatic parallel query processing using either of the following options:
Note that changing this configuration setting purges all cached queries in all namespaces.
When activated, automatic parallel query hinting directs the SQL optimizer to apply parallel processing to any query that may benefit from this type of processing. At IRIS 2018.2 and subsequent, auto parallel processing is activated by default. Users upgrading from IRIS 2018.1 to IRIS 2018.2 will need to explicitly activate auto parallel processing.
One option the SQL optimizer uses to determine whether to perform parallel processing for a query is the auto parallel threshold. If system-wide auto parallel processing is activated (the default), you can use the $SYSTEM.SQL.SetAutoParallelThreshold() method to set the optimization threshold for this feature as an integer value. The higher the threshold value is, the lower the chance that this feature will be applied to a query. This threshold is used in complex optimization calculations, but you can think about this value as the minimal number of tuples that must reside in the visited map. The default value is 3200. The minimum value is 0.
When automatic parallel processing is activated, a query executed in a sharded environment will always be executed with parallel processing, regardless of the parallel threshold value.
The $SYSTEM.SQL.CurrentSettings() method displays the current Enable auto hinting for %PARALLEL and Threshold of auto hinting for %PARALLEL settings.
Parallel Query Processing for a Specific Query
The optional %PARALLEL keyword is specified in the FROM clause of a query. It suggests that InterSystems IRIS perform parallel processing of the query, using multiple processors (if applicable). This can significantly improve performance of some queries that uses one or more COUNT, SUM, AVG, MAX, or MIN aggregate functions, and/or a GROUP BY clause, as well as many other types of queries. These are commonly queries that process a large quantity of data and return a small result set. For example, SELECT AVG(SaleAmt) FROM %PARALLEL User.AllSales GROUP BY Region would likely use parallel processing.
A “one row” query that specifies only aggregate functions, expressions, and subqueries performs parallel processing, with or without a GROUP BY clause. However, a “multi-row” query that specifies both individual fields and one or more aggregate functions does not perform parallel processing unless it includes a GROUP BY clause. For example, SELECT Name,AVG(Age) FROM %PARALLEL Sample.Person does not perform parallel processing, but SELECT Name,AVG(Age) FROM %PARALLEL Sample.Person GROUP BY Home_State does perform parallel processing.
If a query that specifies %PARALLEL is compiled in Runtime mode, all constants are interpreted as being in ODBC format.
Specifying %PARALLEL may degrade performance for some queries. Running a query with %PARALLEL on a system with multiple concurrent users may result in degraded overall performance.
For further details, refer to the FROM clause in the InterSystems SQL Reference.
%PARALLEL in Subqueries
%PARALLEL is intended for SELECT queries and their subqueries. An INSERT command subquery cannot use %PARALLEL.
%PARALLEL is ignored when applied to a subquery that is correlated with an enclosing query. For example:
SELECT name,age FROM Sample.Person AS p 
WHERE 30<(SELECT AVG(age) FROM %PARALLEL Sample.Employee where Name = p.Name)
%PARALLEL is ignored when applied to a subquery that includes a complex predicate, or a predicate that optimizes to a complex predicate. Predicates that are considered complex include the FOR SOME and FOR SOME %ELEMENT predicates.
Parallel Query Processing Ignored
Regardless of the auto parallel option setting or the presence of the %PARALLEL keyword in the FROM clause, some queries may use linear processing, not parallel processing. InterSystems IRIS makes the decision whether or not to use parallel processing for a query after optimizing that query, applying other query optimization options (if specified). InterSystems IRIS may determine that the optimized form of the query is not suitable for parallel processing, even if the user-specified form of the query would appear to benefit from parallel processing. You can determine if and how InterSystems IRIS has partitioned a query for parallel processing using Show Plan.
In the following circumstances specifying %PARALLEL does not perform parallel processing. The query executes successfully and no error is issued, but parallelization is not performed:
Shared Memory Considerations
For parallel processing, InterSystems IRIS supports multiple InterProcess Queues (IPQ). Each IPQ handles a single parallel query. It allows parallel work unit subprocesses to send rows of data back to the main process so the main process does not have to wait for a work unit to complete. This enables parallel queries to return their first row of data as quickly as possible, without waiting for the entire query to complete. It also improves performance of aggregate functions.
Parallel query execution uses shared memory from the generic memory heap (gmheap). Users may need to increase gmheap size if they are using parallel SQL query execution. As a general rule, the memory requirement for each IPQ is 4 x 64k = 256k. InterSystems IRIS splits a parallel SQL query into the number of available CPU cores. Therefore, users need to allocate this much extra gmheap:
<Number of concurrent parallel SQL requests> x <Number cores> x 256 = <required size increase (in kilobytes) of gmheap>
Note that this formula is not 100% accurate, because a parallel query can spawn sub queries which are also parallel. Therefore it is prudent to allocate more extra gmheap than is specified by this formula.
Failing to allocate adequate gmheap results in errors reported to messages.log. SQL queries may fail. Other errors may also occur as other subsystems try to allocate gmheap.
To review gmheap usage by an instance, including IPQ usage in particular, from the home page of the Management Portal choose System Operation then System Usage, and click the Shared Memory Heap Usage link; see Generic (Shared) Memory Heap Usage in the “Monitoring InterSystems IRIS Using the Management Portal” chapter of the Monitoring Guide for more information.
To change the size of the generic memory heap or gmheap (sometimes known as the shared memory heap or SMH), from the home page of the Management Portal choose System Administration then Configuration then Additional Settings then Advanced Memory; see Advanced Memory Settings in the “InterSystems IRIS Additional Configuration Settings” chapter of the Additional Configuration Settings Reference for more information.
Cached Query Considerations
If you are running a cached SQL query which uses %PARALLEL and while this query is being initialized you do something that purges cached queries, then this query could get a <NOROUTINE> error reported from one of the worker jobs. Typical things that causes cached queries to be purged are calling $SYSTEM.SQL.Purge() or recompiling a class which this query references. Recompiling a class automatically purges any cached queries relating to that class.
If this error occurs, running the query again will probably execute successfully. Removing %PARALLEL from the query will avoid any chance of getting this error.
SQL Statements and Plan State
An SQL query which uses %PARALLEL can result in multiple SQL Statements. The Plan State for these SQL Statements is Unfrozen/Parallel. A query with a plan state of Unfrozen/Parallel cannot be frozen by user action. Refer to the SQL Statements chapter for further details.
Generate Report
You can use the Generate Report tool to submit a query performance report to InterSystems Worldwide Response Center (WRC) customer support for analysis. You can run the Generate Report tool from the Management Portal using either of the following:
To use this reporting tool, perform the following steps:
  1. You must first get a WRC tracking number from the WRC. You can contact the WRC from the Management Portal by using the Contact button found at the top of each Management Portal page. Enter this tracking number in the WRC Number area. You can use this tracking number to report the performance of a single query or multiple queries.
  2. In the SQL Statement area, enter a query text. An X icon appears in the top right corner. You can use this icon to clear the SQL Statement area. When the query is complete, select the Save Query button. The system generates a query plan and gathers runtime statistics on the specified query. Regardless of the system-wide runtime statistics setting, the Generate Report tool always collects with Collection Option 3: record statistics for all module levels of the query. Because gathering statistics at this level may take time, it is strongly recommended that you select the Run Save Query process in the background check box. This check box is selected by default.
    When a background job is started, the tool displays the message "Please wait...", disables all the fields on the page, and show a new View Process button. Clicking the View Process button will open the Process Details page in a new tab. From the Process Details page, you can view the process, and may "Suspend", "Resume" or "Terminate" the process. The status of the process is reflected on the Save Query page. When the process is finished, the Currently Saved Queries table is refreshed, the View Process button disappears, and all the fields on the page are enabled.
  3. Perform Step 2 with each desired query. Each query will be added to the Currently Saved Queries table. Note that this table can contain queries with the same WRC tracking number, or with different tracking numbers. When finished with all queries, proceed to Step 4.
    For each listed query, you can select the Details link. This link opens a separate page that displays the full SQL Statement, the Properties (including the WRC tracking number and the IRIS software version), and the Query Plan with performance statistics for each module.
  4. Use the query check boxes to select the queries you wish to report to the WRC. To select all queries associated with a WRC tracking number, select a row from the Currently Saved Queries table, rather than using the check boxes. In either case, you then select the Generate Report button. The Generate Report tool creates a xml file that includes the query statement, the query plan with runtime statistics, the class definition, and the sql int file associated with each selected query.
    If you select queries associated with a single WRC tracking number, the generated file will have a default name such as WRC12345.xml. If you select queries associated with more than one WRC tracking number, the generated file will have the default name WRCMultiple.xml.
    A dialog box appears that asks you to specify the location to save the report to. After the report is saved, you can click the Mail to link to send the report to WRC customer support. Attach the file using the mail client's attach/insert capability.


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-05-21 11:15:03