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

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Query optimization and execution » Reading execution plans


Reading graphical plans

The graphical plan feature in Interactive SQL displays the execution plan for a query in the Plan Viewer window. The execution plan consists of a tree of relational algebra operators which, starting at the leaves of the tree, consume the base inputs of the query (usually rows from a table) and process the rows from bottom to top, so that the root of the tree yields the final result. Nodes in this tree correspond to specific algebraic operators, though not all query evaluation performed by the server is represented by nodes. For example, the effects of subquery and function caching are not directly displayed in a graphical plan.

Nodes displayed in the graphical plan are different shapes that indicate the type of operation performed:

  • Hexagons represent operations that materialize data.

  • Trapezoids represent index scans.

  • Rectangles with square corners represent table scans.

  • Rectangles with round corners represent operations not listed above.

You can use a graphical plan to diagnose performance issues with specific queries. For example, the information in the plan can help you decide if a table requires an index to improve the performance of this specific query. You can save the graphical plan for a query for future reference by pressing the Save button in the Plan Viewer. SQL Anywhere graphical plans are saved with the extension .saplan.

Possible performance issues are identified by thick lines and red borders in the graphical plan. For example:

  • Thicker lines between nodes in a plan indicate a corresponding increase in the number of rows processed. The presence of a thick line over a table scan may indicate that the creation of an index might be required.

  • Red borders around a node indicate that the operation was expensive in comparison with the other operations in the execution plan.

Node shapes and other graphical components of the plan can be customized within Interactive SQL. See Customizing the appearance of graphical plans.

You can view either a graphical plan, a graphical plan with a summary or a graphical plan with detailed statistics. All three plans allow you to view the parts of the plan that are estimated to be the most expensive. Generating a graphical plan with statistics is more expensive because it provides the actual query execution statistics as monitored by the database server when the query is executed. Graphical plans with statistics 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 differences between the estimated and actual values.

To view a graphical plan, see Viewing graphical plans. Graphical plans are also available using the Application Profiling mode in Sybase Central. For more information about the Application Profiling features of Sybase Central, see Application profiling.

For more information about text plans, see Reading text plans.

Graphical plan with statistics
Analyzing performance using the graphical plan with statistics
Viewing detailed graphical plan node information
Viewing selectivity in the graphical plan
Customizing the appearance of graphical plans
Viewing graphical plans
Node Statistics field descriptions
Optimizer Statistics field descriptions