The Plan tab displays a GUI representation of execution engine’s plan for the currently running SQL text, and helps you understand the performance and statistic characteristics of the currently running query.
The Plan tab only appears if you connect to Adaptive
Server version 15.0 and later.
The top half of the Plan tab shows the logical flow of the operators used in the plan in a tree-based, hierarchal structure, with each operator a separate node of the tree. The cost of each operator is based on the cost model used by the query processor. Each operator node in the tree is costed relative to other nodes, which makes it easier to identify operators based on their costs.
Each node includes tooltip text (text that appears when you move your mouse over the node) that provides details about each operator, so you do not have to select the nodes to compare details between operators.
The Plan tab includes the following tabs:
Details – shows the details of the operator statistics as two tables:
Node Statistics – shown for all the operators, and includes statistics like row count, logical I/O, and physical I/O.
Subtree Statistics – the aggregate sum of all the operators below, and are shown for the non-leaf operators, and include statistics on row count, logical I/O, and physical I/O.
XML – shows the result set as XML output.
Text – displays the text version of the query plan (the same as the output of showplan).
Advanced – includes the following:
Abstract query plan – shows the abstract query plan used by the query.
Resource utilization – describes the resources used by the plan, including number of threads and the auxiliary session descriptors (SDESs, every table scan requires one session descriptor to track the scan).
Cost – lists costs associated with the plan, including logical I/O, Physical I/O, and CPU usage.
Optimizer Metrics – lists the query-plan statistics, including the run time for the query, run time for the first plan, the number of plans evaluated, the number of plans that were valid, and the amount of procedure cache used.
Optimizer Statistics – lists the last time you ran update statistics on the table, any missing histogram steps, and the density of the steps.
