T2.1: Manages Query Processing

Knowledge Review - InterSystems IRIS SQL Specialist

1. Understands query optimizer considerations and behavior

Key Points

  • Query Plan: Human-readable translation of execution instructions generated when SQL queries are compiled
  • Relative Cost: Abstract integer value comparing efficiency of different execution plans for the same query
  • Frozen Plans: Query plans that remain fixed despite code or schema changes (Frozen/Explicit or Frozen/Upgrade states)
  • Optimizer Inputs: Table sizes, available indexes, statistics, and data structure information guide optimization decisions
  • Read Master Map: Indicates inefficient plan without index usage; should define indexes for better performance

Detailed Notes

Overview

The InterSystems IRIS query optimizer generates execution plans when SQL queries are prepared, not when they execute.

  • Analysis during compilation: Table structures, available indexes, table sizes, and statistics
  • Output: Human-readable representation showing how data will be accessed

Relative Cost

The optimizer assigns each plan a relative cost value:

  • Computed from: Complexity, index availability, and table sizes
  • Purpose: Enables comparison between alternate execution plans for the same query
  • Important: Cost is only meaningful for comparing plans for the same query

Query Plan States

Query plans can be frozen to maintain consistent execution despite schema changes:

  • Frozen/Explicit: User-frozen plan
  • Frozen/Upgrade: Version upgrade frozen
  • Unfrozen: Normal, can be optimized
  • Unfrozen/Parallel: Uses %PARALLEL and cannot be frozen

Map Access Indicators

The optimizer attempts to use index maps rather than master maps:

  • "Read master map": First execution step indicates inefficient query needing indexes
  • "Read index map": Indicates efficient query using available indexes
  • Recommendation: Define appropriate indexes when "Read master map" appears

Optimizer Considerations

Factors the optimizer considers when generating plans:

  • Join order selection
  • Index selection
  • Whether to use columnar storage

Analysis Tools

  • Alternate Show Plans: View multiple alternate query plans
  • Comparison features: Compare relative costs and runtime statistics

Documentation References

2. Differentiates between syntax errors and runtime errors

Key Points

  • Syntax Errors: Detected at compile/prepare time (SQLCODE -1 to -99 range); include invalid SQL statements, missing keywords, malformed expressions
  • Runtime Errors: Occur during execution (SQLCODE -101 to -399 range); include constraint violations, locking conflicts, data validation failures
  • SQLCODE Values: Negative values indicate errors; 0 indicates success; 100 indicates no data found
  • Error Retrieval: Use $SYSTEM.SQL.Functions.SQLCODE(-nnn) to retrieve error message text
  • SQLCODE -400: Generic "Fatal error occurred" when specific error code unavailable

Detailed Notes

Overview

InterSystems IRIS SQL errors fall into two primary categories distinguished by when they occur in the query lifecycle.

Syntax Errors (Compile/Prepare Time)

Syntax errors are detected at compile time or prepare time when SQL statements are parsed and validated.

  • SQLCODE range: Typically -1 to -99
  • Common examples:
  • SQLCODE -1: Invalid SQL statement
  • SQLCODE -26: Missing FROM clause
  • SQLCODE -30: Table or view not found
  • SQLCODE -51: SQL statement expected
  • Effect: Prevent query execution; must be corrected before statement can run

Runtime Errors (Execution Time)

Runtime errors occur during query execution after successful compilation.

  • SQLCODE range: Typically -101 to -399
  • Common examples:
  • SQLCODE -104: Field validation failed in INSERT
  • SQLCODE -119: UNIQUE constraint failed on INSERT
  • SQLCODE -114: Row locked by another user
  • SQLCODE -102: Operation attempted on unopened cursor
  • Causes: Issues with data values, concurrent access, constraint enforcement, or execution state

Special SQLCODE Values

Two special SQLCODE values do not represent errors:

  • SQLCODE 0: Successful completion
  • SQLCODE 100: Operation succeeded but found no data to process
  • SQLCODE -400: Generic "Fatal error occurred" when specific error code unavailable

Retrieving Error Messages

To retrieve human-readable error messages:

  • ObjectScript method: `$SYSTEM.SQL.Functions.SQLCODE(-nnn)`
  • Stored procedure: `%SYSTEM_SQL.Functions_SQLCODE(-nnn)`
  • %msg variable: May contain additional error details for certain errors

Documentation References

3. Retrieves Statement Index metadata for query analysis

Key Points

  • Statement Index: Catalog of all prepared SQL statements with metadata including hash, frozen status, timestamps, and statement text
  • INFORMATION_SCHEMA.STATEMENTS: System view containing SQL Statement Index entries accessible to current user
  • Key Metadata: Hash (unique identifier), Frozen status, Timestamp, Execution count, Average time, Row count
  • Related Views: STATEMENT_LOCATIONS (routine locations), STATEMENT_RELATIONS (tables/views used), CURRENT_STATEMENTS (actively executing)
  • Query Capabilities: Filter by frozen status, table/view usage, statement text patterns, performance metrics

Detailed Notes

Overview

The Statement Index provides comprehensive metadata for analyzing and managing SQL query performance in InterSystems IRIS.

INFORMATION_SCHEMA.STATEMENTS View

Contains entries for all prepared SQL statements accessible to the current user in the current namespace.

  • Unique identifier: Hash value computed from normalized SQL text

Key metadata columns:

  • Hash: Unique statement identifier
  • Frozen: Status flag (0-3)
  • Timestamp: When plan was prepared
  • Statement: SQL text
  • Usage statistics: Execution count, total time, average time, row count, standard deviation of runtime

Frozen Status Values

The frozen status indicates whether a query plan is frozen:

  • 0 or 3: Unfrozen statements
  • 1: Frozen/Explicit (user action)
  • 2: Frozen/Upgrade (version upgrade)
  • NULL: No query plan exists

Related INFORMATION_SCHEMA Views

  • STATEMENT_LOCATIONS: Lists routine locations (class names or cached query names)
  • STATEMENT_RELATIONS: Lists tables and views used by each statement
  • CURRENT_STATEMENTS: Shows actively executing statements system-wide (requires %Admin_Operate resource access)

Query Capabilities

Administrators can query these views to:

  • Identify performance issues
  • Find all statements using specific tables
  • Locate frozen plans
  • Analyze query patterns
  • Identify slow-running queries by average time

Statistics Collection Timing

  • Prepare time statistics: Automatically tracked
  • Runtime statistics: Gathered continuously, written at intervals up to 30 minutes
  • Aggregation: System task aggregates process-specific statistics into global statistics hourly
  • Note: Recent statistics may not immediately appear in queries

Documentation References

Exam Preparation Summary

Critical Concepts to Master:

  1. Query Optimizer Behavior: Understand how relative cost is calculated, what "Read master map" indicates, and the difference between frozen and unfrozen plans
  2. Error Code Categories: Memorize that syntax errors are -1 to -99 range (compile time), runtime errors are -101 to -399 range (execution time)
  3. SQLCODE Values: Know that 0 = success, 100 = no data found, negative = error, and -400 = generic fatal error
  4. Statement Index Queries: Be able to write queries against INFORMATION_SCHEMA.STATEMENTS to find frozen plans, statements using specific tables, or performance statistics
  5. Frozen Plan States: Understand Frozen/Explicit (user action), Frozen/Upgrade (version upgrade), Unfrozen, and Unfrozen/Parallel

Common Exam Scenarios:

  • Interpreting query plans to identify inefficient queries (master map vs index map usage)
  • Determining whether an error is syntax or runtime based on SQLCODE value
  • Writing SQL queries to retrieve specific statement metadata from INFORMATION_SCHEMA views
  • Understanding when query plans are generated (prepare time vs execute time)
  • Analyzing frozen plan behavior and when plans can/cannot be frozen

Hands-On Practice Recommendations:

  • Use Management Portal SQL Statements tab to view query plans and metadata
  • Practice querying INFORMATION_SCHEMA.STATEMENTS with various WHERE clauses
  • Generate intentional syntax and runtime errors to observe SQLCODE values
  • Use EXPLAIN command or Show Plan to view query execution plans
  • Experiment with freezing/unfreezing query plans to observe behavior changes
  • Compare alternate query plans using Alternate Show Plans tool
  • Query STATEMENT_LOCATIONS and STATEMENT_RELATIONS to understand statement dependencies

Report an Issue