Page History
...
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.
| 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.
...
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
distinctor change theunionclause tounion all.
See “About the Execution plan tree” on page 100.
| 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 viewing which objects are referenced by the execution plan” on page 103.
| 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.
...