Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Query Optimization and Execution

Window Functions algorithm Next Page

Reading execution plans


An execution plan is the set of steps the database server uses to access information in the database related to a statement. The execution plan for a statement can be saved and reviewed, regardless of whether it was just optimized, whether it bypassed the optimizer, or whether its plan was cached from previous executions. A query execution plan may not correspond exactly to the syntax used in the original statement. It is, however, be semantically equivalent, and may use materialized views instead of the base tables explicitly specified in the query.

For more information about phases a statement goes through until it is executed, see Phases of query processing.

For more information about the rules that the database server follows when rewriting your query, see Semantic query transformations, and Improving performance with materialized views.

The optimizer's job is to understand the semantics of your query and to construct a plan that computes its result. The access plan may not correspond exactly to the syntax you used. The optimizer is free to rewrite your query in any semantically equivalent form.

For more information about the rules SQL Anywhere obeys when rewriting your query, see Rewriting subqueries as EXISTS predicates and Semantic query transformations.

For information about the methods that the optimizer uses to implement your query, see Query execution algorithms.

You can view the execution plan in Interactive SQL or using SQL functions. You can choose to retrieve the execution plan in several different formats:

You can also obtain plans for SQL queries with a particular cursor type by using the GRAPHICAL_PLAN and EXPLANATION functions. See GRAPHICAL_PLAN function [Miscellaneous], and EXPLANATION function [Miscellaneous].

For more information about how to save and see the plan, see Accessing the execution plan.

For information about how to read execution plans, see Text plans, and Graphical plans.

Following is an explanation of the statistics and other items that appear in access plans.

Abbreviations used in the plan

Following are the abbreviations that are used in the short plan, and in the short name form of the graphical plan:

Abbreviation Name
DELETEDelete
DistHHash distinct
DistOOrdered distinct
DTDerived table
EAHHash except all
EAMMerge except all
EHHash except
EMMerge except
ExchangeExchange
FilterFilter
FSFile scan
GrByHHash group by
GrByHClustHash group by clustered
GrByHPParallel hash group by
GrByHSetsHash group by sets
GrByOOrdered group by
GrByOSetsOrdered group by sets
GrBySSingle row group by
GrBySSetsSorted group by sets
HF Hash filter
HFPParallel hash filter
HTSHash table scan
IAHHash intersect all
IAMMerge intersect all
IHHash intersect
IMMerge intersect
INIn list
INSENSITIVEInsensitive
INSERTInsert
IS

Index scan

In a short plan, it is table-name followed by either <rowID>, <seq>, or <rowID>. In a graphical plan, it is just table-name.

ISPParallel index scan
JEExists join
JHHash join
JHEExists hash join
JHEPExists parallel hash join
JHFOFull outer hash join
JHNENot exists hash join
JHNEPNot exists parallel hash join
JHOLeft outer hash join
JHPParallel hash join
JHPOParallel left outer hash join
JHRRecursive hash join
JHRORecursive left outer hash join
JMMerge join
JMFOFull outer merge join
JMOLeft outer merge join
JNLNested loops join
JNLFOFull outer nested loops join
JNLOLeft outer nested loops join
KEYSETKeyset
LOADLoad
PCProcedure call (table function)
PreFilterPre filter
RowID Scan

Row identifier scan

In a short plan, it is table-name <rowID>. In a graphical plan, it is just table-name.

ROWSRow constructor
RLRow limit
RRRow replicate
RTRecursive table
RURecursive union
SELECTSelect
SortSort (indexed or merge)
SrtNSort top n
TS

Table scan

In a short plan, it is table-name <seq>. In a graphical plan, it is just table-name.

TSPParallel table scan
UAUnion all
UPDATEUpdate
WindowWindow
WorkWork table

For an explanation of the algorithms, see Query execution algorithms.

Common statistics used in the plan

The following statistics are actual, measured amounts.

Statistic Explanation
Invocations Number of times a row was requested from the sub tree.
RowsReturned Number of rows returned for the current node.
RunTime Time required for execution of the sub-tree, including time for children.
CacheHits Number of successful reads of the cache.
CacheRead Number of database pages that have been looked up in the cache.
CacheReadTable Number of table pages that have been read from the cache.
CacheReadIndLeaf Number of index leaf pages that have been read from the cache.
CacheReadIndInt Number of index internal node pages that have been read from the cache.
DiskRead Number of pages that have been read from disk.
DiskReadTable Number of table pages that have been read from disk.
DiskReadIndLeaf Number of index leaf pages that have been read from disk.
DiskReadIndInt Number of index internal node pages that have been read from disk.
DiskWrite Number of pages that have been written to disk (work table pages or modified table pages).
IndAdd Number of entries that have been added to indexes.
IndLookup Number of entries that have been looked up in indexes.
FullCompare Number of comparisons that have been performed beyond the hash value in an index.
Common estimates used in the plan
Statistic Explanation
EstRowCount Estimated number of rows that the node will return each time it is invoked.
AvgRowCount Average number of rows returned on each invocation. This is not an estimate, but is calculated as RowsReturned / Invocations. If this value is significantly different from EstRowCount, the selectivity estimates may be poor.
EstRunTime Estimated time required for execution (sum of EstDiskReadTime, EstDiskWriteTime, and EstCpuTime).
AvgRunTime Average time required for execution (measured).
EstDiskReads Estimated number of read operations from the disk.
AvgDiskReads Average number of read operations from the disk (measured).
EstDiskWrites Estimated number of write operations to the disk.
AvgDiskWrites Average number of write operations to the disk (measured).
EstDiskReadTime Estimated time required for reading rows from the disk.
EstDiskWriteTime Estimated time required for writing rows to the disk.
EstCpuTime Estimated processor time required for execution.
Items in the plan related to SELECT, INSERT, UPDATE, and DELETE
Item Explanation
Optimization Goal Determines whether query processing is optimized towards returning the first row quickly, or minimizing the cost of returning the complete result set. See optimization_goal option [database].
Optimization workload Determines whether query processing is optimized towards a workload that is a mix of updates and reads or a workload that is predominantly read-based. See optimization_workload option [database].
ANSI update constraints Controls the range of updates that are permitted (options are Off, Cursors, and Strict). See ansi_update_constraints option [compatibility]
Optimization level Reserved.
Select list List of expressions selected by the query.
Materialized views

List of materialized views considered by the optimizer. Each entry in the list is a tuple in the following format: view-name [ view-matching-outcome ] [ table-list ] where view-matching-outcome reveals the usage of a materialized view; if the value is COSTED, the view was used during enumeration. The table-list is a list of query tables that were potentially replaced by this view.

Values for view-matching-outcome include:

  • Base table mismatch
  • Permissions mismatch
  • Predicate mismatch
  • Select list mismatch
  • Costed
  • Stale mismatch
  • Snapshot stale mismatch
  • Cannot be used by optimizer
  • Cannot be used internally by optimizer
  • Cannot build definition
  • Cannot access
  • Disabled
  • Options mismatch
  • Reached view matching threshold
  • View used

For more information about restrictions and conditions that prevent the optimizer from using a materialized view, see Improving performance with materialized views, and Restrictions when managing materialized views

Items in the plan related to locks
Item Explanation
Locked tables List of all locked tables and their isolation levels.
Items in the plan related to scans
Item Explanation
Table name Actual name of the table.
Correlation name Alias for the table.
Estimated rows Estimated number of rows in the table.
Estimated pages Estimated number of pages in the table.
Estimated row size Estimated row size for the table.
Page maps YES when a page map is used to read multiple pages.
Items in the plan related to index scans
Item Explanation
Index name Name of the index.
Key type Can be one of PRIMARY KEY, FOREIGN KEY, CONSTRAINT (unique constraint), or UNIQUE (unique index). The key type does not appear if the index is a non-unique secondary index.
Depth Height of the index. See Table and page sizes.
Estimated leaf pages Estimated number of leaf pages.
Cardinality Cardinality of the index if it is different from the estimated number of rows. This applies only to SQL Anywhere databases version 6.0.0 and earlier.
Selectivity Estimated number of rows that match the range bounds.
Direction FORWARD or BACKWARD.
Range bounds Range bounds are shown as a list (col_name=value) or col_name IN [low, high].
Items in the plan related to joins, filter, and pre-filter
Item Explanation
Predicate Search condition that is evaluated in this node, along with selectivity estimates and measurement. See Selectivity in the plan
Items in the plan related to hash filter
ItemExplanation
Build valuesEstimated number of distinct values in the input.
Probe valuesEstimated number of distinct values in the input when checking the predicate.
BitsNumber of bits selected to build the hash map.
PagesNumber of pages required to store the hash map.
Items in the plan related to Union
ItemExplanation
Union ListColumns involved in a UNION statement.
Items in the plan related to GROUP BY
Item Explanation
Aggregates All the aggregate functions.
Group-by list All the columns in the group by clause.
Items in the plan related to DISTINCT
Item Explanation
Distinct list All the columns in the distinct clause.
Items in the plan related to IN LIST
Item Explanation
In List All the expressions in the specified set.
Expression SQL Expressions to compare to the list.
Items in the plan related to SORT
Item Explanation
Order-by List of all expressions to sort by.
Items in the plan related to row limits
Item Explanation
Row limit count Maximum number of rows returned as specified by FIRST or TOP n.

Text plans
Graphical plans
Accessing the execution plan