The graphical plan provides execution plan information using visual cues (shapes, colors, and so on). You can choose to see either a graphical plan, or a graphical plan with statistics. Both allow you to view rapidly which parts of the plan have been estimated as the most expensive. The graphical plan with statistics, though more expensive to view, also provides the actual query execution statistics as monitored by the database server when the query is executed, and permits direct comparison between the estimates used by the query optimizer in constructing the access plan with the actual statistics monitored during execution. Note, however, that the optimizer is often unable to estimate precisely a query's cost, so expect there to be differences.
By default, SQL Anywhere displays the graphical plan without statistics. However, to change between graphical plans with or without statistics, choose Tools > Options, click the Plan tab, and then choose the type of plan. To access the plan with SQL functions, see Accessing the execution plan with SQL functions.
For more information about text plans, see Text plans.
The graphical plan is designed to provide some key information visually. For example, each operation that appears in the graphical plan is displayed in a container. A container is also referred to as a node. The shape of the container indicates the type of operation performed:
operations that materialize data are represented by hexagons
index scans are represented by trapezoids
table scans are represented by rectangles with square corners
other operations are represented by rectangles with round corners
You can often determine query performance by looking for thick lines and red borders in the graphical plan. For example:
the number of rows that an operation passes to the next operation in the plan is indicated by the thickness of the line joining the operations. This provides a visual indicator of the operations performed on most data in the query.
the container for an operation that is particularly slow has a red border.
The appearance of items in the graphical plan is customizable. To change the appearance of the graphical plan, right-click the plan in the lower left pane of Interactive SQL, select Customize, and change the settings accordingly. While you must already have executed a plan in order to customize the appearance of it, your changes are applied to subsequent graphical plans that are displayed.
You can also print a graphical plan by right-clicking the plan, and choosing Print.
Following is a query presented with its corresponding graphical plan. The diagram is in the form of a tree, indicating that each node requests rows from the nodes beneath it.
You can obtain detailed information about the nodes in the plan by clicking the node in the graphical diagram and reading the corresponding information in the right pane. In this example, the nested loops join node (JNL) is selected. The information in the right pane pertains only to that node. For example, the Predicate description is
TRUE, indicating that at this stage in the query execution no predicate is being applied. If you click the Customers table query node, however, the Predicate value changes to be something similar to
Customers.ID > 100 : 100% Index; true 126/126 100%.
To obtain context-sensitive help for each node in the graphical plan, select the node, right-click it and choose Help.
For more information about the abbreviations used in the plan, see Abbreviations used in the plan.
If a query is recognized as a simple query, some optimization steps are bypassed and neither the Query Optimizer section nor the Predicate section appear in the graphical plan. For more information on bypassed queries, see How the optimizer works.
The graphical plan with statistics shows you all the estimates that are provided with the graphical plan, but also shows actual runtime costs of executing the statement. To do this, the statement must be executed. This means that there may be a delay in accessing the plan for expensive queries. It also means that any parts of your query, such as deletes or updates, are actually executed; you can perform a rollback to undo these changes.
Use the graphical plan with statistics when you are having performance problems and if the estimated row counts, or run times, differ from your expectations. The graphical plan with statistics provides estimates and actual statistics for you to compare. A large difference between estimated and actual statistics can be a warning sign that the optimizer does not have sufficient information to choose a good access plan.
The database options and other global settings that affect query execution are displayed for the root operator node only.
Following are some of the key statistics you can check in the graphical plan with statistics, and some possible remedies:
Selectivity statistics The selectivity of a predicate (conditional expression) is the percentage of rows that satisfy the condition. The estimated selectivity of predicates provides the information on which the optimizer bases its cost estimates. If the selectivity estimates are poor, the query optimizer may generate a poor access plan. For example, if the optimizer mistakenly estimates a predicate to be highly selective (for example, a selectivity of 5%), but the actual selectivity is much lower (50%), then it may choose an inappropriate plan. Selectivity estimates are not precise, but a significantly large deviation can indicate a problem.If you determine that the selectivity information for a key part of your query is inaccurate, you can use CREATE STATISTICS to generate a new set of statistics for the column(s) in question. In rare cases, you may want to supply explicit selectivity estimates, although this approach can introduce other problems later when the statistics are updated. For more information about selectivity, see Selectivity in the plan. For more information about creating statistics, see CREATE STATISTICS statement. For more information about user estimates, see Explicit selectivity estimates. Selectivity statistics are not displayed if the query is determined to be a simple query, so that the optimizer is bypassed. For more information on simple queries, see How the optimizer works. Indicators of poor selectivity occur in the following places:
RowsReturned actuals and estimates RowsReturned is the number of rows in the result set. The RowsReturned statistic appears in the table for the root node at the top of the tree. A significant difference between the estimated rows returned and the actual number returned is a warning sign that the optimizer is working on poor selectivity information.
Predicate selectivity actuals and estimates Look for the Predicate subheading to see predicate selectivities. For information about reading the predicate information, see Selectivity in the plan.
If the predicate is over a base column for which there is no histogram, executing a CREATE STATISTICS statement to create a histogram may correct the problem. See CREATE STATISTICS statement.
If selectivity error remains a problem, you may want to consider specifying a user estimate of selectivity along with the predicate in the query text.
Estimate source The source of selectivity estimates is also listed under the Predicate subheading in the statistics pane.
An estimate source of Guess indicates that the optimizer has no selectivity information to use. If the estimate source is Index and the selectivity estimate is incorrect, your problem may be that the index is skewed; you may benefit from defragmenting the index with the REORGANIZE TABLE statement. See REORGANIZE TABLE statement.
For a complete list of the possible sources of selectivity estimates, see ESTIMATE_SOURCE function [Miscellaneous].
Cache reads and hits If the number of cache reads and cache hits are exactly the same, this indicates that all the information needed to execute the query is in cache. When reads are greater than hits, it means that the database server is attempting to go to cache but failing, and that it must read from disk. In some cases, such as hash joins, this is expected. In other cases, such as nested loops joins, a poor cache-hit ratio may indicate a performance problem, and you may benefit from increasing your cache size.For more information about cache management, see Increase the cache size.
Lack of effective indexes It is often not obvious from query execution plans whether an index would help provide better performance or not. Some of the scan-based algorithms used in SQL Anywhere provide excellent performance for many queries without using indexes.For more information about indexes and performance, see Use indexes effectively and Index Consultant.
Data fragmentation problems The Runtime actual and estimated values are provided in the root node statistics. Runtime measures the time to execute the query. If the runtime is incorrect for a table scan or index scan, you may improve performance by executing the REORGANIZE TABLE statement.For more information, see REORGANIZE TABLE statement and Reducing table fragmentation.
Following is an example of the graphical plan with statistics. Again, the nested loops join node is selected. The statistics in the right pane indicate the resources used by that part of the query.
For more information about code words used in the plan, see Abbreviations used in the plan.
Following is an example of the Predicate showing selectivity of a search condition. In this example, the selected node represents a scan of the Departments table, and the statistics pane shows the Predicate as the search condition, its selectivity estimation, and its real selectivity.
Selectivity information may not be displayed for simple queries that qualify for optimization bypass. For more information on simple queries, see How the optimizer works.
The access plan depends on the statistics available in the database, which, in turn, depends on what queries have previously been executed. You may see different statistics and plans from those shown here.
This predicate description is
Departments.DepartmentName = 'Sales' : 20% Column; true 1/5 20%
This can be read as follows:
Departments.DepartmentName = 'Sales' is the predicate.
20% is the optimizer's estimate of the selectivity. That is, the optimizer is basing its query access selection on the estimate that 20% of the rows satisfy the predicate.
This is the same output as is provided by the ESTIMATE function. For more information, see ESTIMATE function [Miscellaneous].
Column is the source of the estimate. This is the same output as is provided by the ESTIMATE_SOURCE function. For a complete list of the possible sources of selectivity estimates, see ESTIMATE_SOURCE function [Miscellaneous].
true 1/5 20% is the actual selectivity of the predicate during execution. The predicate was evaluated five times, and was true once, hence its real selectivity is 20%.
If the actual selectivity is very different from the estimate, and if the predicate was evaluated a large number of times, it is possible that the incorrect estimates are causing a significant problem in query performance. Collecting statistics on the predicate may improve performance by giving the optimizer better information on which to base its choices.
If you select the graphical plan, but not the graphical plan with statistics, the final two statistics are not displayed.