Factors that affect the query plan

When the optimizer determines the query plan, it assigns a cost to each possible plan and then chooses the plan with the lowest cost. The optimizer analyzes several factors to determine the cost of each query plan.

Some of the factors that the optimizer uses to determine the cost of each query plan are:
  • The number of I/O requests that are associated with each file system access
  • The CPU work that is required to determine which rows meet the query predicate
  • The resources that are required to sort or group the data
  • The amount of memory available for the query (specified by the DS_TOTAL_MEMORY and DS_MAX_QUERIES parameters)
To calculate the cost of each possible query plan, the optimizer:
  • Uses a set of statistics that describes the nature and physical characteristics of the table data and indexes
  • Examines the query filters
  • Examines the indexes that can be used in the plan
  • Uses the cost of moving data to perform joins locally or remotely for distributed queries

For queries that access remote tables in cross-server operations, certain characteristics can significantly degrade performance relative to the corresponding DML operations on tables and views in the local database. Query specifications that can potentially limit performance with remote tables include the following specifications:

  • ANSI LEFT OUTER JOIN syntax
  • Derived tables based on remote tables
  • TEMP tables as materialized views that reference remote tables.
Limitations on remote views
Reoptimization can occur with multiple executions of queries involving remote views. The optimizer does not pick up the query plans from statement cache even if the statement cache is enabled.