Page History
...
| Info |
|---|
If the SQL tab is opened with no statement in context, an error message is displayed requesting you open a statement. |
See About the Current tab on page 40, About the Activity tab on page 49, and About the Objects tab on page 67.
| Anchor | ||||
|---|---|---|---|---|
|
...
The Plan view is divided into two panes. The Execution Plan tree is displayed in the left pane. The Details area is displayed in the right pane. The information displayed in the Details area is controlled by the information tabs (Highlights, Objects, Statistics, Operations, Properties) located above the Details area.
| Anchor | ||||
|---|---|---|---|---|
|
The Plan view displays the Execution Plan tree of the specified statement. When you analyze the access plan of a selected statement, you can examine the access path that was chosen by the Adaptive Server Query Optimizer. The Explain result is displayed as an Explain tree. Each branch in the Explain tree represents one operation. Each operation is displayed in the following format:
...
Table 2 Operation options
| Operator | Description |
|---|---|
| Sort | The Sort operator presents the sorting of the rows returned from the previously executed operation (appears just below this operator) in a specific order, optionally eliminating duplicate entries. Sorting is required when no index that satisfies the requested ordering exists, or when an Index Scan is more expensive than sorting. It is usually used as the final step to retrieve the fetched data or a prior step for the Join or Stream Aggregate (Group By) operators. In some cases, the Sort operation requires more than the available memory, in which case a temporary table is used. This involves Disk I/O, which has an impact on performance. SortingSorting a large number of rows makes heavy use of the CPU, which can also affect overall server performance. The amount of memory needed for sorting can be estimated by multiplying the number of rows by the average row size. Therefore, minimizing the row size by selecting only the necessary fields will decrease the memory required for the Sort operation. |
| Nested Joins | Nested Joins (also called Nested Loops) use the Nested algorithm to join two tables. The Nested algorithm works as follows: For each row in the outer table (the first table, appearing directly below the Nested Loop operator in the Explain tree), Sybase executes the Access Plan for the inner table (the second one). A large number of iterations may indicate a problem in choosing the Access Plan due to out-of-date statistics. |
| Merge Joins | A Merge Join algorithm is used to join two tables by merging them. Merge Join algorithms read both tables only once; therefore it is required that the input be sorted according to the Join predicates. Merge Joins are available in Precise for Sybase from version 12.0 and above. This option is grayed-out by default. To enable it on session level, execute the set sort_merge on command. See the Sybase Help for more details on how to enable Merge Joins on server level. |
About the operators that are available in the plan tree
...
| Operator | Description |
|---|---|
| Step | This operator indicates the start of the specified step Subtree. |
| Table Scan | This operator indicates that a table scan was performed on the specified table to receive the required data. The More column displayed in the ToolTip and in the Statistics tab specify the scan direction (forward/backward), the I/O size and the buffer replacement strategy. When there is no useful index, the optimizer performs a table scan to receive the required data. |
| Index Scan | This operator indicates that an index scan was performed on the specified index to receive the required data. The More column displayed in the ToolTip and in the Statistics tab specify the scan direction (forward/backward), the I/O size and the buffer replacement strategy. |
| Index Seek | This operator indicates that an index seek was performed on the specified index to receive the required data. The More column displayed in the ToolTip and in the Statistics tab specify the scan direction (forward/backward), the I/O size and the buffer replacement strategy. |
| Clustered Index Scan | This operator indicates that a clustered index scan was performed on the specified index to receive the required data. The More column displayed in the ToolTip and in the Statistics tab specify the scan direction (forward or backward), the I/O size and the buffer replacement strategy. |
| Clustered Index Seek | This operator indicates that a clustered index seek was performed on the specified index to receive the required data. The More column displayed in the ToolTip and in the Statistics tab specify the scan direction (forward or backward), the I/O size and the buffer. |
| Table Lookup | This operator indicates that in addition to an access to the index pages, there is an access to the data pages of the table to receive the required data which is not included in the index itself. The More column displayed in the ToolTip and in the Statistics tab specify the I/O size and the buffer replacement strategy. |
| Nested Loops | This operator indicates that the join between the two result sets under this operator is of type Nested Loops. The first operator under the Nested Loops operator is the outer table in the join and the second operator under the Nested Loops operator is the inner table in the join. |
| Merge Join | This operator indicates that the join between the two result sets under this operator is of type Merge Join. |
| Sort | This operator indicates that a sort operation was performed on the rows returned from the previously executed operation (appears just below this operator) in a specific order, optionally eliminating duplicate entries. |
| Aggregate | This operator indicates that an Aggregate operation was performed on the rows returned from the previously executed operation (appears just below this operator). This operator appears in steps, which perform an evaluation of grouped or ungrouped aggregate functions such as SUM, AVERAGE and COUNT. |
| Subquery | This operator indicates that a subquery was executed. The Subquery tree will appear under this operator. The More column displayed in the ToolTip and in the Statistics tab specify the nesting level of the subquery, the predicate type (IN, ANY, EXISTS, or EXPRESSION), and the type of query (correlated/ non-correlated). |
| Spooling | This operator indicates that a temporary worktable was created to hold intermediate results. Temporary worktables are created in cases when the statement cannot be executed in one step. For example, if the statement contains an Order By clause and the optimizer cannot use an index to order the resulting rows, it creates a worktable to sort the resulting rows before returning them. The More column displayed in the ToolTip and in the Statistics tab specify why the temporary worktable was created (Order By, Distinct, Reformatting, and so on). |
| Join | This operator indicates that a join operation was performed between a subquery output and another result set. |
| Parallelism Merge | This operator indicates that a parallelism merge was done to the result sets returned by all the worker processes involved in the parallel execution. The More column displayed in the ToolTip and in the Statistics tab specify the way in which the results of parallel scans were merged. |
| Log Scan | This operator indicates that a log scan operation was performed. |
...
The Highlights tab displays the statement's text.
| Anchor | ||||
|---|---|---|---|---|
|
The Objects tab displays three tables (Tables used in the plan, Indexes of table, and Columns of table) that list all referenced objects in the Execution Plan, including their indexes and columns. Statistical details and general details are displayed for each object and its sub-entities.
...
- If the operator is a Table Scan, try creating an index.
- If the operator is of a Join type (such as Nested Loop or Merge), examine a different join method.
- If the operator is a Sort operation, check if the sort is required by the application or can be removed. For example, remove
distinctor change theunionclause tounion all.
See “About About the Execution plan tree” on page 100tree.
| Anchor | ||||
|---|---|---|---|---|
|
...
- Launch to the SQL tab with a statement in-context or open the statement you want to analyze in the SQL tab.
- On the Plan tab, click Objects.
- Click the Locator icon (to locate and highlight all the operators in the execution plan that access the specified table).
- Examine the cost of these operators (to locate the problematic operators).
- Identify the objects that are referenced by the problematic operators and try to tune those objects.
See “About About viewing which objects are referenced by the execution plan” on page 103plan.
| Anchor | ||||
|---|---|---|---|---|
|
...
- Launch to the SQL tab with a statement in-context or open the statement you want to analyze in the SQL tab.
- On the Statement tab, observe the access plan statements and their estimated cost.
- Determine which of the statements has the highest estimated cost and focus your analysis on that statement.