Page History
...
The table below shows from which tabs and entities it is possible to launch to the SQL tab, in context.
Table 9- 1 Launching in context
| Tab | Entities |
|---|---|
| Current | Session, Statement, Batch |
| Activity | Statement, Batch |
| SQL | Statement entered manually by the user through a New action. |
| Info |
|---|
If the SQL tab is opened with no statement in context, an error message is displayed requesting you open a statement. |
...
The table below describes the available operation options.
Table 9- 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. Sorting 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
The table below describes the operators that are available in the Plan tree.
Table 9- 3 Operation options
| 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. |
About the costs that are available in the plan tree
...
The table below shows the I/O Cost information operators available in the Plan tree.
Table 9- 4 I/O cost information operators
| Counter | Description |
|---|---|
| Estimated Cost | Logical Reads * 2 milliseconds (which is the assumption of how long a logical read takes) + Physical Reads * 18 milliseconds (which is the assumption of how long a physical read takes). |
| Estimated Subtree Cost | Total estimated cost of all the operator's Subtree operators. |
| Logical Reads | Number of logical reads performed on the specified table. The number of logical reads estimated by the optimizer is based on the access method and the size of cache. In this case, the optimizer is estimating how many pages are likely to be in cache. |
| Physical Reads | Physical reads are estimated based on the number of logical reads and the cache size. The optimizer is estimating how many pages will not be available in cache and will need to be read from disk. |
| Scan Count | A "scan" can represent any of these access methods:
|
| Estimated Rows | Number of rows from the current table that is estimated to satisfy the join. |
About viewing a statement's text
...
The table below describes the information displayed in the tables used in the plan.
Table 9- 5 Tables used in the plan
| Column | Description |
|---|---|
 | Click to launch the Objects tab and focus on the specified table. |
 | Click to locate and highlight all the operators in the Execution Plan that access the specified table. |
| Used | Indicates whether the specified table is used in the selected operator in the Execution Plan tree. |
| Table | Displays the full table name ([Database Name].[Owner].[Table Name]). |
| Cost | Total cost of the operators that accessed the specified table. |
| Cost (%) | Total cost in percentage of the operator that accessed the specified table. |
| Rows | Number of rows in the specified table. |
| Pages Allocated | The number of pages allocated in the disk for the table records. The value might be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the sp_flushstats command to flush current space usage information from memory or run the update statistics command to recalculate the space usage fields. |
| Pages Used | The number of pages used in the disk for the table records. The value might be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the sp_flushstats command to flush current space usage information from memory or run the update statistics command to recalculate the space usage fields. |
About indexes of table
Displays a list of all the indexes of the selected table displayed in Tables Used in the Plan. The table below describes the information displayed in indexes of table.
Table 9- 6 Indexes of table
| Column | Description |
|---|---|
| Click to launch the Objects tab and focus on the specified index. |
| Click to locate and highlight all the operators in the Execution Plan that access the specified index. |
| Used | Indicates whether the specified index is used in the selected operator in the Execution Plan tree. |
| Index | Displays the index name. |
| Pages Allocated | The number of pages allocated in the disk for the index records. The value may be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the sp_flushstats command to flush current space usage information from memory or run the update statistics command to recalculate the space usage fields. |
| Pages Used | The number of pages used in the disk for the index records. The value may be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the sp_flushstats command to flush current space usage information from memory or run the update statistics command to recalculate the space usage fields. |
| Cost | Total cost of the operators that accessed the specified index. |
| Cost (%) | Total cost in percentage of the operators that accessed the specified index. |
| Index Depth | Number of levels in the index. |
| Unique | Indicates whether the index is unique. |
| Clustered | Indicates whether the index is clustered. |
About Columns of Table
Displays a list of all columns in the selected table displayed in Tables Used in Plan. When an index is selected in Indexes of Table, the first column constitutes the index column sorted by the position of the column in the index, and marked with an Ascending or Descending icon.
The table below describes the information displayed in the columns of the table.
Table 9- 7 Columns of table
| Column | Description |
|---|---|
| Indicates whether the column is part of the selected index and whether it is sorted in ascending or descending order. |
| Column | Displays the column name. |
| Key Number | Displays the key number of the column in the index. |
| Type | Displays the physical storage type of the column. |
Displaying statistical information on operators in the execution plan tree
...
The table below shows the statistical information displayed for Operator ID and Type.
Table 9- 8 About viewing statistics on Operator ID and Type
| Column | Description |
|---|---|
| Click to locate and highlight the operator in the Execution Plan tree that matches the selected operator in the grid. |
| Operator ID and Type | Displays the operator ID and type. |
| Logical Reads | Number of logical reads performed on the specified table. The number of logical reads estimated by the optimizer is based on the access method and the size of cache. In this case, the optimizer is estimating how many pages are likely to be in cache. |
| Physical Reads | Physical reads are estimated based on the number of logical reads and the cache size. The optimizer is estimating how many pages will not be available in cache and will need to be read from disk. |
| Subtree Cost | Displays the total estimated cost of all the current operation's sons. |
| Estimated Cost | Displays the estimated cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation. Estimated cost is calculated as followed: Logical Reads * 2 milliseconds (which is the assumption of how long a logical read takes) + Physical Reads * 18 milliseconds (which is the assumption of how long a physical read takes). |
| Estimated Cost (%) | Displays the estimated cost (in percentage) of the current operator. |
| Estimated Rows | Number of rows from the current table that is estimated to satisfy the join. |
| Scan Count | A "scan" can represent any of these access methods:
|
| More | Displays additional information according to the specified operation. For example, in a table scan operation, the scan type (forward/backward), I/O size and buffer replacement strategy will be displayed. |
| Parallel | Indicates whether the operator is running in parallel. |
About viewing statistical information on operation types
...
The table below describes the information displayed for major operation types .
Table 9- 9 Major operations
| Column | Description |
|---|---|
| Click to locate and highlight the operators in the Execution Plan tree that match the selected major operation type. |
| Major Operation Type | Displays the major types of the operators.
|
| Estimated Cost | Displays the total estimated cost breakdown according to major operation types. |
| Operations | Indicates the number of operators of a specific major operation type. |
| Objects | Indicates the number of referenced tables/indexes of the specified operation type. This column is only relevant for table and index operation types. |
The table below describes the information displayed for regular operation types.
Table 9- 10 Operations
| Column | Description |
|---|---|
| Click to locate and highlight the operators in the Execution Plan tree that match the selected operation type. |
| Operation Type | Indicates type of operator, such as, Clustered Index Scan, Index Seek, Nested Loop, and so on. |
| Major Operation Type | Displays the major types of the operators. The operators in the Execution Plan tree are separated into the following major types:
|
| Estimated Cost | Total estimated cost breakdown according to operation types. |
| Operations | Indicates the number of operators of the specified operation type. |
| Objects | Indicates the number of referenced tables/indexes of the specified operation type. |
About viewing general properties of a selected statement
The Properties item under the More tab displays general details of the selected statement. The table below describes the properties displayed for a selected statement.
Table 9- 11 Selected statement properties
| Column | Description |
|---|---|
| Statement ID | Displays the name of the statement or batch that the SQL tab was launched with. Can also be the name of the statement opened manually. |
| Database | Indicates the database that was in use when the statement was run. |
| Parsing User | Indicates the user that was in use when the statement was run. |
| Cabinet | Indicates the cabinet in which the statement was saved. |
| Folder | Indicates the folder in which the statement was saved. |
| Estimate Cost | Indicates the total estimate cost of the statement as calculated for the last access plan. |
| Most Recent Show Plan | Indicates the last time the statement or batch was explained. |
| Plan Time | Indicates when the Access Plan was saved. An Access Plan is saved in two cases:
|
Observing a history of resource consumption
...
The Access Plan Changes view displays a list of all the Access Plans of the statement as detected by the Explain Statement process.
Table 9- 12 Access Plan Changes table
| Column | Description |
|---|---|
 | Click to switch to Plan view and display the selected Access Plan. |
| Date | Indicates the date the specified Access Plan was detected by the Explain Statements process. |
| Estimate Cost | Indicates the estimated cost of the specified Access Plan. |
| Table Scans | Displays the number of table scans performed in the specified Access Plan. |
| Index Scans | Displays the number of index scans performed in the specified Access Plan. |
| Parallel | Displays the number of parallel operations performed in the specified Access Plan. |
| Nested Loops | Displays the number of nested loops performed in the specified Access Plan. |
| Merge Joins | Displays the number of merge joins performed in the specified Access Plan. |
| Sorts | Displays the number of sort operations performed in the specified Access Plan. |
Breaking down a batch's access plan into statements
...
The table below describes the information displayed in the Statement's View table.
Table 9- 13 Statement's View table
| Column | Description | ||
|---|---|---|---|
| Click to switch to Plan view, in-context with the selected statement.
| ||
| ID | Indicates the ID of the statement in the batch. | ||
| Collector Statement | Displays the ID of the statement and batch as assigned by the Precise for Sybase Collector agent. | ||
| Physical Operator | Describes how a query or update was executed. The physical operator describes the physical implementation used to process a statement, for example: Batch level, Select, Update, Cond, and so on. | ||
| Operations | Displays the number of operations in the specified statement. | ||
| Table Scans | Displays the number of Table Scans performed in the specified statement. | ||
| Index Scans | Displays the number of Index Scans performed in the specified statement. | ||
| Joins | Displays the number of join operations performed in the specified statement. | ||
| Sorts | Displays the number of sort operations performed in the specified statement. | ||
| Estimated Cost | Displays the estimated cost for the specified statement. |
About the Actions tab
The Actions tab, on the Precise bar, lets you perform additional actions. You can perform the following actions:
...
- 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.
...
- .
...
...