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 that, 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 clicking 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.
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.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|