Page History
This section includes the following topics:
- About the SQL tab
- How the SQL tab is structured
- How the SQL tab can help you identify performance problems
| Anchor | ||||
|---|---|---|---|---|
|
The SQL tab enables you to evaluate SQL statements and manage a database warehouse of your application's SQL statements together with their respective Explain Plans. The process of explaining statements is a prerequisite for tuning. The Explain process is designed to clarify the access path chosen for a statement and translate it into a visual medium.
...
View the historical information of statements showing performance degradation to identify the source of the problem. A change in Execution Plan may explain the impact on performance.
| Info |
|---|
...
The information described herein for this tab applies equally to statements and batches. |
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
...
| 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. See , About the Activity tab on page 49. See , and About the Objects tab on page 67.
| Anchor | ||||
|---|---|---|---|---|
|
The SQL tab lets you analyze execution plans and explain results so that you can tune statements and achieve optimal results.
Two sets of tabs control the information displayed in the SQL tab:
- View tabs
- Actions tab
About the View tabs
The SQL tab displays different information regarding the selected statement or batch, in different views. A statement is selected when you drill down to it in another tab (from Current or Activity tabs) or when you open a new or existing statement in the SQL tab. Click a View tab to display additional information regarding the statement or batch. Each view has a different layout.
The following View tabs are available in the SQL tab:
- Plan
- History
- Statements
About viewing the execution plan of a statement
The Plan view tab lets you display the execution plan of a statement and various related information such as statistics, referenced objects and operations performed in the Execution Plan.
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.
About the Execution plan tree
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:
[<execution order>] <operation type> <accessed object>
It is also possible to display the Execution Plan's text, at the bottom of the Execution Plan tree. This allows you to view the text of the statement, the Execution Plan and additional information all at once, such as the objects referenced by the statement.
Actions that can be performed on the tree
It is possible to perform the following actions on the Execution Plan tree:
- The Playback controls, located at the top of the tree enable you to freely move within the Execution Plan of an explained statement.
- By moving the pointer over the Execution Plan steps, you can view a ToolTip that contains statistical information, such as Estimated Cost and Estimated Rows of the specified step.
- Selecting a specific step will highlight its sons and affect the information displayed in the information tabs.
- Clicking the plus sign (+) located at the bottom of the tree, displays the statement's text.
Available operation options
Precise for Sybase enables you to obtain a reasonable understanding of the Execution Plan selected by the Adaptive Server Query Optimizer, by displaying the textual output returned from the SET SHOWPLAN ON command in the Plan tree format. In addition to the Execution Plan, the Estimated I/O Costs returned from the DBCC TRACEON (310, 311) command are displayed in all the table-based operations.
The following options are available to different operations:
- Clustered index - the . The leaf pages of the index hold the actual data, instead of RIDs (Records ID). A table without a clustered index is called a Heap.
- Non-clustered index - the . The index consists only of the columns that combine the key and a pointer to the records in the table data. If the table has a clustered index, then the pointer is the key of the clustered index. If the table does not have clustered index, then the pointer is an RID.
There are several operations for accessing indexes or tables:
- Seeking - the . The index tree can be used for quickly locating the matching records. Seeking can only be done on indexes.
- Scanning - . The leaf pages of the index or the data pages of the table are consecutively scanned.
...
| Info |
|---|
Seeking is the recommended way to use indexes. |
The table below describes the available operation options.
Table 9-2 Operation options
| Operator | Description |
|---|---|
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.
...
The Sybase SQL Advantage window is opened with the statement in context and commands to display its access path are executed.
| Anchor | ||||
|---|---|---|---|---|
|
After identifying a problematic statement that is slowing down the response time of a specific application, the first step in tuning the statement is to understand the access path that Sybase chose for the statement. The Explain procedure is designed to clarify the access path chosen for a statement and to translate it into a visual medium. Therefore you can easily see whether the optimizer chose the proper Execution Plan. For example, you can see whether the optimizer performed an Index Seek as expected. In addition, you can see if the Access Plan of the statement was changed and how the changes affected the performance of the statement.
...