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 » Reading execution plans

Long text plan Next Page

Graphical plans


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:

You can often determine query performance by looking for thick lines and red borders in the graphical plan. For example:

Customizing the appearance of graphical plans

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.

The Plan tab in Interactive SQL, showing the graphical plan for a query.
Context sensitive help

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.

Note

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.

Graphical plan with statistics

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:

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.

The Plan tab in Interactive SQL, showing the graphical plan with statistics for a query.

For more information about code words used in the plan, see Abbreviations used in the plan.

Selectivity 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.

Graphical plan with statistics showing the predicate.

This predicate description is

Departments.DepartmentName = 'Sales' : 20% Column; true 1/5 20%

This can be read as follows:

Note

If you select the graphical plan, but not the graphical plan with statistics, the final two statistics are not displayed.