Adaptive Server uses a different query plan for a SQL statement that is embedded in a cursor declaration from the query plan when the SQL statement is not embedded in a cursor. This needs to be taken into account when retrieving the query plan or run time.
Therefore, if the SQL statement comes from or will be embedded in a cursor declaration then you need to select SQL for Cursor in the SQL Editor pane of Index Advisor window. This enables cursor simulation when retrieving the query plan and run time information.
This checkbox is automatically selected in the SQL Editor pane when you use the Send to Index Advisor function from the SQL Scanner if the SQL was extracted from within a cursor declaration.
In Adaptive Server 15 or later, the Declare Cursor Setting window is also available to select specific cursor settings.
The cursor arguments should match the settings used for the SQL statement in your application code.
Item | Description |
SEMI-SENSITIVE |
Specify that the worktable which holds the result set is populated only as the rows are fetched. Therefore changes to the data that occur while the cursor is opened may be visible in the result set. |
INSENSITIVE |
Specify that the data is copied to a worktable when the cursor is open which makes the data insensitive to changes in the data that may occur while the cursor is opened. |
SCROLL |
Specify that the cursor is scrollable meaning that you can position the cursor anywhere in the cursor result set for as long as the cursor is open. All scrollable cursors are read only. |
NO SCROLL |
Specify that the rows are retrieved one row at a time. All update cursors are non-scrollable. |
The Run Time tab in the Index Advisor window displays the run time information for the SQL statement using the alternative indexes. The values are filled in after the Show Plan, Batch Run, Run for First Record, or Run for All Records functions are executed.
For Index candidates proposed by the Index Advisor, an icon is added in the far left column to indicate if the proposed index is used in the query plan. A key with a green checkmark indicates that the query plan is changed by the proposed index. A key with a red exclamation mark indicates that the key plan changed, but that the proposed index was not used by the new query plan. A key with a red x indicates that the query plan is not changed by the addition of the proposed index.
Item | Description |
Scenario Name |
The index advising process numbers the SQL alternatives. |
Estimated I/O cost |
This is a cost estimation calculated by Adaptive Server for the query plan. |
Actual I/O Cost (All Records and First Record) |
This is the Actual I/O Cost of the SQL statement after it was executed in the Batch Run or the Run Time functions. |
Elapsed Time (All Records and First Record) |
This is the run time calculated from the clock start and finish times for the SQL statement. |
Times of Improvement (All Records and First Record) |
If the original SQL statement run time is available, this indicates how much faster the SQL statement is under the alternative index(es) than it is under the original index(es). |
Records (All Records and First Record) |
This is the number of records processed by the SQL statement. |
Remarks |
Information from the Batch Run is included in this column. It includes:
|
Estimated I/O cost, All Records Actual I/O Cost and First Record Actual I/O Cost are not available for logon users without sa_role privileges or with the "allow resource limits" configuration parameter turned off for the Adaptive Server. To retrieve the Estimated I/O cost, All Records Actual I/O Cost and First Record Actual I/O Cost estimations, you should either grant yourself sa_role, logon as another user with sa_role privileges, or turn on the "allow resource limits" parameter:
sp_configure "allow resource limits", 1
The Statistics tab of the Run Time pane in the Index Advisor window shows the accumulative totals for the CPU Time, Scan Count, Logical Reads, Physical Reads, and Read-Ahead Reads from the execution of the SQL statement with each index alternative. To see the individual values for the statistics by table, click All Records or First Record on the top right pane or the Statistics tab at the bottom of the Index Advisor window.
For Index candidates proposed by the Index Advisor, an icon is added in the far left column to indicate if the proposed index is used in the query plan. A key with a green checkmark indicates that the query plan is changed by the proposed index. A key with a red exclamation mark indicates that the query plan changed, but that the proposed index was not used by the new query plan. A key with a red x indicates that the query plan is not changed by the addition of the proposed index. In this case, the SQL statement is not executed by the by the Batch Run.
For more information about this statistics see the Adaptive Server Performance Tuning: Monitor and Analyzing manual, Chapter 4.
Item | Description |
Elapsed Time (All Records and First Record) |
The Elapsed Time is the time (in clock time) that it takes the SQL statement to select all records or the first record. This figure may include time that Adaptive Server spent on processing other tasks or waiting for disk or network I/O to complete. This is the accumulative run time for the SQL statement. The individual values for the Elapsed Time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window. |
CPU Time (All Records and First Record) |
The CPU Time is accumulative total for the CPU time to execute the SQL statement. The individual values for the CPU time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window. |
Actual I/O Cost (All Records and First Record) |
This Actual I/O Cost is a calculation from Adaptive Server after the SQL statement was executed in the Batch Run or the Run Time function. |
Writes (All Records and First Record) |
The Writes is the total number of buffers written to the disk. |
Scan Count (All Records and First Record) |
The Scan Count represents the number of times a query accessed a particular table. |
Logical Reads (All Records and First Record) |
The Logical Reads represents the accumulative total for logical read for each table and index used in the SQL statement. |
Physical Reads (All Records and First Record) |
The Physical Reads represents the accumulative total for logical read for each table and index used in the SQL statement. |
APF IOs used (All Records and First Record) |
The APF (Asynchronous-PreFetch) IOs used is the I/O the server does in advance anticipating which pages will need to be read next, so that the pages will be in cache when the process actually tries to access them. |
Charts are available for all the statistics and run times. The Charts tab of the Run Time pane in the Index Advisor window displays two chart panes showing the run time information and statistics.
To change the information in the chart
Click the Chart Title and select one of the following functions:
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center