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.
Following are the abbreviations that are used in the short plan, and in the short name form of the graphical plan:
|EAH||Hash except all|
|EAM||Merge except all|
|GrByH||Hash group by|
|GrByHClust||Hash group by clustered|
|GrByHP||Parallel hash group by|
|GrByHSets||Hash group by sets|
|GrByO||Ordered group by|
|GrByOSets||Ordered group by sets|
|GrByS||Single row group by|
|GrBySSets||Sorted group by sets|
|HFP||Parallel hash filter|
|HTS||Hash table scan|
|IAH||Hash intersect all|
|IAM||Merge intersect all|
In a short plan, it is table-name followed by either <rowID>, <seq>, or <rowID>. In a graphical plan, it is just table-name.
|ISP||Parallel index scan|
|JHE||Exists hash join|
|JHEP||Exists parallel hash join|
|JHFO||Full outer hash join|
|JHNE||Not exists hash join|
|JHNEP||Not exists parallel hash join|
|JHO||Left outer hash join|
|JHP||Parallel hash join|
|JHPO||Parallel left outer hash join|
|JHR||Recursive hash join|
|JHRO||Recursive left outer hash join|
|JMFO||Full outer merge join|
|JMO||Left outer merge join|
|JNL||Nested loops join|
|JNLFO||Full outer nested loops join|
|JNLO||Left outer nested loops join|
|PC||Procedure call (table function)|
Row identifier scan
In a short plan, it is table-name <rowID>. In a graphical plan, it is just table-name.
|Sort||Sort (indexed or merge)|
|SrtN||Sort top n|
In a short plan, it is table-name <seq>. In a graphical plan, it is just table-name.
|TSP||Parallel table scan|
For an explanation of the algorithms, see Query execution algorithms.
The following statistics are actual, measured amounts.
|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.|
|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.|
|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]|
|Select list||List of expressions selected by the query.|
List of materialized views considered by the optimizer. Each entry in the list is a tuple in the following format:
Values for view-matching-outcome include:
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
|Locked tables||List of all locked tables and their isolation levels.|
|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.|
|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].|
|Predicate||Search condition that is evaluated in this node, along with selectivity estimates and measurement. See Selectivity in the plan|
|Build values||Estimated number of distinct values in the input.|
|Probe values||Estimated number of distinct values in the input when checking the predicate.|
|Bits||Number of bits selected to build the hash map.|
|Pages||Number of pages required to store the hash map.|
|Union List||Columns involved in a UNION statement.|
|Aggregates||All the aggregate functions.|
|Group-by list||All the columns in the group by clause.|
|Distinct list||All the columns in the distinct clause.|
|In List||All the expressions in the specified set.|
|Expression SQL||Expressions to compare to the list.|
|Order-by||List of all expressions to sort by.|
|Row limit count||Maximum number of rows returned as specified by FIRST or TOP n.|
Accessing the execution plan