T2.2: Interprets Query Plans

Overview

InterSystems IRIS provides multiple methods to view and analyze SQL query execution plans.

• Timing: Query plans are generated when a query operation is prepared (no need to execute the query)

Available Methods

SQL EXPLAIN Command:

• Generates XML-formatted query plan for SELECT queries
• Can optionally include alternate query plans and SQL statistics

Management Portal:

• Show Plan button in System Explorer SQL interface
• SQL Performance Tools accessible through System Explorer Tools

SQL Shell:

• SHOW PLAN: Displays execution plan for most recently executed query
• SHOW PLANALT: Displays alternate execution plans

Programmatic Access:

• `$SYSTEM.SQL.Explain()`: Generates and displays XML-formatted query plans with optional alternate plans

Default Behavior

• All tools display what InterSystems IRIS considers to be the optimal query plan by default
• Most tools can also generate alternate execution plans for comparison
• Alternate Show Plans tool: Enables comparison of multiple plans with runtime statistics

SelectMode Behavior

• Show Plan by default returns values in Logical mode
• When invoked from Management Portal or SQL Shell, it uses Runtime mode

Overview

The Query Plan begins execution with one of three map type statements that indicate how data will be accessed.

Map Type Statements

"Read master map" (Inefficient):

• Indicates a full table scan as first bullet item in main module
• Master map contains all the data in the table
• Reads data itself rather than an index to the data
• Almost always indicates an inefficient query plan
• Recommendation: Define an appropriate index (unless table is relatively small)

"Read index map" (Efficient):

• Indicates the query will use an available index
• Generally much more efficient than master map access

"Generate a stream of idkey values using the multi-index combination":

• Indicates use of multiple indexes (Multi Index)

Table Storage Structure

An SQL table is stored as a set of maps:

• Master map: Contains all table data; RowID or IDKEY fields used as map subscripts
• Index maps: Other fields used as leading subscript(s), with RowID/IDKEY as additional lower-level subscripts
• Bitmaps: Optional specialized index structures

Red Flag Analysis

When analyzing query plans, the presence of "Read master map" should be a red flag prompting investigation of whether an appropriate index can be defined to improve query performance.

Overview

When a query plan shows "Read index map" as the first execution step, this indicates the query optimizer has selected an index-based access strategy rather than a full table scan.

Index Plan Details

The plan provides information about:

• Which index is being used
• The subscripts (fields) that comprise the index
• Index maps use table fields as leading subscript(s), with RowID/IDKEY as additional lower-level subscripts

Subscript Value Access Types

The plan indicates what subscript values will be accessed (efficiency decreases down the list):

• Single given value: Most efficient
• Set of given values: Less efficient
• Range of values: Less efficient
• All values present: Least efficient (full subscript scan)

Bitmap Index Considerations

• Additional bitmap layer can be thought of as an extra RowID subscript level
• Bitmaps can only be used for RowIDs that are positive integers

Map Access Patterns

The plan for a query may access:

• Single map: Index or master map alone
• Two maps: Index map followed by master map
• Index provides RowIDs of matching records
• Master map accessed to retrieve complete record data
• Multiple maps: Multi-index plan using several maps

Optimization Validation

Understanding index usage in query plans is essential for:

• Identifying optimization opportunities
• Validating that defined indexes are being utilized effectively

Overview

When accessing data from a table, it is often necessary to examine multiple rows iteratively, indicated by a loop in the query plan.

Loop Visualization

• Loop body: Instructions executed for each pass through the loop
• Visual indication: Indented in the show plan output
• Nested loops: Each loop level indicated by further indentation
• Hierarchy: Creates visual hierarchy showing nesting structure

When Loops Are Used

Loops are used when the query needs to:

• Iterate through a set of rows
• Scan a range of index values
• Process multiple matching records

Lookup Operations

In contrast, a lookup operation:

• Represents direct access to a specific row or small set of rows
• Typically uses an index to go directly to target data without iteration
• Generally much faster than loops

Performance Implications

• Lookups: Fast, direct access
• Loops: Multiple rows must be examined
• Deeply nested loops: Can indicate performance concerns, especially when outer loop processes many rows

Reading Plan Indentation

The indentation structure in the show plan makes it easy to:

• Identify loop nesting levels
• Understand the iteration pattern of query execution

Overview

The building of temporary files and other processing operations may be delegated to a separate unit of work called a module in the query execution plan.

Module Naming

• Named alphabetically starting with B (Module:B, Module:C, etc.)
• Listed in order of execution (not necessarily alphabetical order)
• When execution of a module finishes, processing resumes at next statement following module invocation

Execution Frequency

Understanding whether a module is executed once or repeatedly is critical for performance analysis:

• Executed once: Initial setup or one-time data preparation
• Executed repeatedly: Within a loop, multiplies cost by number of iterations

Subquery Module Naming

Subquery modules have special naming conventions:

• Named alphabetically with a skip sequence (skipping one or more letters before each named subquery)
• When end of alphabet is reached, additional subqueries are numbered
• Example sequence: F, I, L, O, R, U, X, 27, 30, 33 (every-third skip)

Module Invocation

• If a subquery calls a module, the module is placed in alphabetical sequence after the subquery with no skip
• Precise place where a subquery section is called from is not always indicated
• Often invoked as part of processing conditions or expressions

Performance Analysis

Identifying module execution frequency helps:

• Predict overall query performance
• Pinpoint optimization opportunities

Overview

Table statistics play a crucial role in query planning as the SQL compiler uses information such as table sizes and available indexes to generate the most efficient set of instructions.

TUNE TABLE Command

The TUNE TABLE command gathers statistics of an existing table based on the data currently in the table.

Statistics gathered:

• Map size
• Extent size
• Selectivity for each field

Data requirement: Should be representative of data expected when table is fully populated.

Cached Query Behavior

• When values change: TUNE TABLE recompiles all cached queries that use the table
• When values unchanged: Cached queries not purged, class definition not flagged for recompile

Query Plan Warnings

When the system prepares an SQL query and generates a plan, warnings are reported as part of Show Plan:

• "Table are not tuned": Raised when one or more referenced tables have not been tuned
  Resolution: Tune the specified tables using TUNE TABLE

  Optimizer Benefits

  Statistics allow the optimizer to make informed decisions about:

  • Access paths
  • Join orders
  • Other execution strategies

  Without Current Statistics

  • Optimizer must rely on default assumptions
  • May not match actual data distribution
  • Potentially leads to suboptimal query plans

  Documentation References

  • TUNE TABLE
  • Table Statistics for Query Optimizer

  7. Troubleshoots using query hints Report Issue

  Key Points

  • %ALLINDEX: Force use of all applicable indexes in query optimization
  • %FIRSTTABLE: Specify which table should be accessed first in join
  • %FULL: Force full table scan instead of using index
  • %IGNOREINDEX: Prevent specific index from being considered
  • %NOLOCK: Perform no locking on tables (READ UNCOMMITTED mode)
  • %NOFPLAN: Ignore frozen plan and generate new query plan
  • %PARALLEL: Enable parallel query processing for eligible queries

  Detailed Notes

  Overview

  Query hints (also called optimization hints) provide a mechanism to influence or override the query optimizer's default behavior when standard optimization produces suboptimal results.

  • Placement: Specified in FROM clause or as %keyword options after SELECT

  Common Query Hints

  Index Control:

  • %ALLINDEX: Force use of all applicable indexes in query optimization
  • %IGNOREINDEX: Prevent specific index from being considered

  Table Access:

  • %FULL: Force full table scan (beneficial for small tables or when most rows will be selected)
  • %FIRSTTABLE / %STARTTABLE: Specify which table should be accessed first in join

  Locking and Plans:

  • %NOLOCK: Perform no locking on tables (READ UNCOMMITTED mode)
  • %NOFPLAN: Ignore frozen plan and generate new query plan

  Processing Control:

  • %PARALLEL: Enable parallel query processing for eligible queries
  • %INORDER: Process joins in FROM clause order
  • %NOFLATTEN: Prevent subquery flattening
  • %NOMERGE: Prevent view merging
  • %NOREDUCE: Disable expression reduction
  • %NOSVSO: Disable sort-vector sequence optimization
  • %NOTOPOPT: Disable TOP optimization
  • %NOUNIONOROPT: Disable UNION/OR optimization

  Best Practices

  • Use judiciously and only after careful analysis
  • Hints override the optimizer's logic based on comprehensive statistics and algorithms
  • Particularly valuable when you have specific knowledge about data distribution or access patterns that the optimizer cannot infer from statistics alone

  Documentation References

  • Specify Optimization Hints in Queries
  • SELECT %keyword options

  8. Identifies index opportunities based on query plans Report Issue

  Key Points

  • Read master map indicator: Primary signal that index creation could improve performance
  • WHERE clause analysis: Conditions that filter data are candidates for index columns
  • JOIN condition columns: Fields used in join predicates benefit from indexing
  • ORDER BY columns: Sorting fields may benefit from index to avoid temp-file sort
  • Warning messages: "Index cannot be used" warnings identify collation or selectability issues

  Detailed Notes

  Overview

  Query plans provide valuable clues for identifying opportunities to create new indexes or modify existing ones to improve query performance.

  Primary Indicator: "Read master map"

  The most obvious indicator is "Read master map" as the first bullet item in the main module:

  • Signals no suitable index exists
  • Full table scan is being performed

  Index Candidate Identification

  WHERE Clause Columns:

  • Examine conditions to identify which fields filter data
  • Prime candidates for index creation

  JOIN Condition Columns:

  • Fields used in JOIN conditions between tables
  • Indexes on join columns dramatically improve join performance
  • Enable efficient lookups instead of nested loops

  ORDER BY Columns:

  • Fields specified in ORDER BY clauses may benefit from indexing
  • Appropriate index provides data in desired order
  • Avoids separate sort operation and temporary file creation

  Warning Messages

  The Warnings section can identify index opportunities:

  Collation Issues:

  • " cannot be used... because there is no index subscript collation"
  • " cannot be used... because the index subscript collation is "
  • Resolution: Define an index with appropriate collation

  Selectability Issues:

  • " are not selectable"
  • Indicates indexes that would improve performance but need to be rebuilt

  Temp File Indicators

  When query plans add empty nodes to a temp file:

  • Suggests query anticipates needing temporary results but has none to store
  • Adding an appropriate index can circumvent creation of unused temp files

  Best Practice

  Systematic review of query plans for frequently executed or performance-critical queries is an essential part of database tuning.

  Documentation References

  • Structure of the Plan - Warnings
  • Accessing Maps

  9. Interprets relative cost values in query plans Report Issue

  Key Points

  • Cost definition: Abstract integer computed from multiple factors for comparing execution plans
  • Cost factors: Query complexity, presence of indexes, and table size(s)
  • Same-query comparison: Useful only for comparing different execution plans of same query
  • Not cross-query comparable: Cannot compare cost values between two different queries
  • Alternate plans ordering: Listed in ascending order by cost (lowest cost first)
  • Cost not available: Certain aggregate queries like COUNT(*) without WHERE clause

  Detailed Notes

  Overview

  Relative cost is an integer value displayed in query plans that provides an abstract measure for comparing the efficiency of different execution plans for the same query.

  Cost Calculation Factors

  The calculation takes into account:

  • Complexity of the query
  • Presence of indexes
  • Size of the table(s) being accessed

  Important Limitations

  • Not cross-query comparable: Relative cost is NOT useful for comparing two different queries
  • Same-query only: Only meaningful when comparing alternative execution plans for the same query
  • Abstract measure: Does not directly correspond to execution time, resource consumption, or specific performance metric
  • Represents: Optimizer's estimate of total work required to execute the query plan

  Alternate Plans Display

  When using Alternate Show Plans tool or $SYSTEM.SQL.Explain() with "all" qualifier:

  • Multiple execution plans displayed for a single query
  • Listed in ascending order by cost (lowest cost first)
  • Lowest cost plan is what InterSystems IRIS considers optimal

  Cost Estimate Accuracy

  • Based on statistics and heuristics
  • Plans with slightly higher estimated cost might actually perform better
  • Optimizer cannot account for all factors

  Special Cases

  For certain aggregate queries (COUNT(*) or MAX(%ID) without WHERE clause):

  • Show plan returns "Relative cost not available"
  • These queries use special optimizations bypassing normal cost analysis

  Practical Comparison

  • Focus on magnitude of cost differences
  • Small cost difference may not translate to meaningful performance variation
  • Large cost differences typically indicate substantially different execution strategies
  • Stats feature: Provides actual runtime statistics (Time, Global Refs, Commands, Read Latency) to complement cost estimates

  Documentation References

  • Structure of the Plan - Query Plan
  • Alternate Show Plans - Stats

  Exam Preparation Summary

  Critical Concepts to Master:

  1. Query Plan Tools: Know EXPLAIN command, Management Portal Show Plan, SQL Shell commands, and $SYSTEM.SQL.Explain()
  2. Table Scan Recognition: Identify "Read master map" as indicator of inefficient full table scan
  3. Index Usage: Recognize "Read index map" and understand index vs. master map access patterns
  4. Loops vs. Lookups: Distinguish iterative loops from direct lookups, understand nested loop implications
  5. Module Execution: Identify whether modules execute once or repeatedly, understand performance impact
  6. Table Statistics: Understand TUNE TABLE role and impact on query optimization
  7. Query Hints: Know when and how to use %FULL, %IGNOREINDEX, %FIRSTTABLE, %NOLOCK hints
  8. Index Opportunities: Identify from "Read master map", WHERE clauses, JOIN conditions, and warnings
  9. Relative Cost: Use only for comparing alternate plans of same query, understand cost factor components

  Common Exam Scenarios:

  • Analyzing a query plan to identify full table scan vs. index usage
  • Recommending index creation based on query plan showing "Read master map"
  • Interpreting nested loops and estimating execution frequency
  • Identifying which query plan tool to use for a specific troubleshooting scenario
  • Understanding when table statistics need updating (TUNE TABLE)
  • Selecting appropriate query hints to resolve performance issues
  • Comparing relative costs of alternate query plans
  • Recognizing module execution patterns and their performance implications
  • Identifying index opportunities from query plan warnings

  Hands-On Practice Recommendations:

  • Generate query plans using multiple methods (EXPLAIN, Portal, SQL Shell)
  • Compare plans before and after creating indexes
  • Run TUNE TABLE and observe impact on query plan and cached queries
  • Use Alternate Show Plans to compare multiple execution strategies
  • Practice reading indented loop structures in query plans
  • Experiment with query hints and observe plan changes
  • Analyze query plan warnings and resolve index issues
  • Identify optimization opportunities in real query plans from Sample database

  Report an Issue

  Issue Type Confusing Content Incorrect Information Outdated Content Formatting Issue Typo Other Description

  Notify me by email when resolved

  Cancel Submit