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. |
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:
...
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 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.
...
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
...
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 Statistics tab displays statistical information of all operators in the Execution Plan tree. When you sort a table, you can locate operators by their estimated cost or by any other column in the table.
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
...
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 Operations item under the Menu tab displays two tables that show statistical information broken down into operation types of all operators in the Execution Plan tree.
The table below describes the information displayed for major operation types .
Table 9-9 Major operations
Column Description
...
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
...
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 History view tab displays Resource Consumption Over Time vs. Access Plan Changes, to determine how the changes affected the performance of the statements or batches.
...
- Main area
- Change history area
About the Main area
The Main area (upper area) displays Resource Consumption Over Time vs. Cost and Changes Over Time.
About viewing a history of resource consumption over time
The History view displays Resource Consumption Over Time vs. Cost and Access Plan Changes Over Time in a number of overtime graphs.
- In
...
- Sybase. Displays the Resource Consumption of the selected statement over a specified time period.
...
- Cost. Displays the Estimated Cost of the selected statement over a specified time period.
...
- Changes. Displays Access Plan Change indications for the statement vs. Average Duration over a specified time period.
...
- Executions. Displays the number of executions of the selected statement over a specified time period.
About viewing execution plan text
The Text view displays the Execution Plan's text.
About the Change History area
The Change History area (lower area) displays all the Access Plan changes made over a specified time period.
About viewing Access Plan Changes
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
...
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
In many cases a statement is part of a long batch (such as a Stored Procedure or improvised batch). Sometimes you want to examine a particular statement, and sometimes you want to view the statement in the context of the entire batch.
The Statements view breaks down the Access Plan of the entire batch into statements and correlates the statements in the Access Plan with the statements that were captured by the Precise for Sybase Collector agent. The statement IDs of these statements that were captured are displayed in Precise. It is possible to view the Access Plan of a different statement by clicking its respective Plan icon.NOTE Only
Info |
---|
Only statements that were captured by the Precise for Sybase Collector agent can be chosen. |
When you switch to this view in the context of a batch, all the batch's statements are listed. In this case, all the information displayed is related to the selected batch.
The table below describes the information displayed in the Statement's View table.
Table 9- 13 Statement's View table
Column Description
...
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. |
Table 9-13 Statement's View table
Column Description
...
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:
- Create a new statement
- Open an existing statement
- Edit an existing statement
- Re-Explain an existing statement
- Display the Sybase plan using Sybase SQL Advantage and Interactive SQL
Creating a new statement
You can create a new Statement and save it in the PMDB in a logical cabinet and folder hierarchy. You can also rewrite statements and view their Access Plans.
...
- On the Precise bar, click Actions>New.
- In the New Statement dialog box, choose the relevant properties for the new statement from the drop-down lists and enter a statement name, as follows:
- Instance Indicates Instance. Indicates the name of the instance that the statement belongs to.
- Cabinet Indicates Cabinet. Indicates the name of the cabinet that the statement is saved in (creates a cabinet if it does not already exist).
- Folder Indicates Folder. Indicates the name of the folder that the statement is saved in (creates a folder if it does not already exist).
- Name Indicates Name. Indicates the name of the statement (generated randomly).
- Database Indicates Database. Indicates the name of the database where the statement is running.
- User Indicates User. Indicates the statement's parsing user.
- Enter the SQL text for the statement in the text frame.
- Click OK.
The new statement is saved in the PMDB in a logical cabinet and folder hierarchy.
Opening an existing statement
You can view a statement that was saved in the PMDB. To open an existing statement
- On the Precise bar, click Actions>Open.
- In the Open Statement dialog box, choose the relevant properties for the statement you want to view from the Instance, Cabinet, Folder and Name drop-down lists, as follows:
- Instance Indicates Instance. Indicates the name of the instance that the statement belongs to.
- Cabinet Indicates Cabinet. Indicates the name of the cabinet that the statement is saved in (creates a cabinet if it does not already exist).
- Folder Indicates Folder. Indicates the name of the folder that the statement is saved in (creates a folder if it does not already exist).
- Name Indicates Name. Indicates the name of the statement (generated randomly).
- Database Indicates Database. Indicates the name of the database where the statement is running.
- User Indicates User. Indicates the statement's parsing user.
The statement's text is displayed in the text frame.
- Click OK.
Editing an existing statement
You can edit a statement that was saved in the PMDB. The statement that was collected by the Collector agent is saved as a New Statement in a logical cabinet and folder hierarchy.
...
- On the Precise bar, click Actions>Edit.
- In the Edit Statement dialog box, define the statement you want to edit by choosing a relevant property from the drop-down lists, as follows:
- Instance Indicates Instance. Indicates the name of the instance that the statement belongs to.
- Cabinet Indicates Cabinet. Indicates the name of the cabinet that the statement is saved in (creates a cabinet if it does not already exist).
- Folder Indicates Folder. Indicates the name of the folder that the statement is saved in (creates a folder if it does not already exist).
- Database Indicates Database. Indicates the name of the database where the statement is running.
- User Indicates User. Indicates the statement's parsing user.
- Enter a New Statement name in the Name field.
- Edit the SQL text for the statement in the text frame.
- Click OK.
The edited statement is saved in a logical cabinet and folder hierarchy.
Re-Explaining an existing statement
You can Re-Explain an existing statement. If the new access plan is different from the most recent access plan, it is saved in the PMDB and displayed.
To re-explain an existing statement, click the Re-Explain tab
Display the Sybase plan using Sybase SQL Advantage and Interactive SQL
You can display the Sybase plan using Sybase SQL Advantage and Interactive SQL. To display the Sybase plan using Sybase SQL Advantage and Interactive SQL, click the Sybase Plan tab.
...
You can identify a performance problem by doing one or more of the following:
- Identifying problematic operators in the latest access plan
- Locating referenced tables that pose potential problems
- Examining how access path changes affect statement performance
- Locating the most resource-consuming statements in a batch
Anchor | ||||
---|---|---|---|---|
|
The first step in tuning a statement is to identify problematic operators. Problematic operators are operators whose estimated cost is high.
...
- Launch to the SQL tab with a statement in-context or open the statement you want to analyze in the SQL tab.
- Open the Statistics tab in Plan view
- Sort the grid by the Estimated Cost value (desc.)
Info |
---|
...
The lower Estimated Cost the better. |
Following are examples of how to improve problematic operators:
- 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
distinct
or change theunion
clause tounion all
.
See “About About the Execution plan tree” on page 100.tree.
Anchor | ||||
---|---|---|---|---|
|
When it is not possible to access the statement, such as in third company products, the only way to improve the statement is by improving access to the data. This can be achieved by performing changes in the referenced objects by examining the operators that access the tables and identifying potential problematic operators.
...
- 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 103.plan.
Anchor | ||||
---|---|---|---|---|
|
Precise for Sybase allows you to track the Access Plan changes and compare them with the Resource Consumption of your statement to understand how the changes affected the statement's performance.
...
- Launch to the SQL tab with a statement in-context or open the statement you want to analyze in the SQL tab.
- On the History tab, on the View controls, click History.
- Analyze the statement's resource consumption over time vs. execution cost, access path changes, and number of executions over time. This can help you to observe whether the change in the statement performance occurred due to the change in the access path or not.
- In the Changes History table, you can view additional details on the change made. You can select the launch icon for a particular access path and launch to the Plan tab in-context to continue your analysis of the change.
Anchor | ||||
---|---|---|---|---|
|
The main difference between examining a statement's Access Plan and a batch's Access Plan is in the number of operators in the Access Plan. Understanding an Access Plan with hundreds of operators is a difficult task. The Statements view can assist you with this task since it shows the Access Plan statements and their Estimated Cost. The ID of the statements sampled by the Precise for Sybase Collector agent are also displayed.
...
- 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.
Precise. Performance intelligence from click to storage. Learn more > >
...