SQL Optimizer for SAP ASE 3.8 - User Guide

SQL Scanner Tutorial

Use the SQL Scanner to analyze SQL statements embedded within database objects, text/binary files, Abstract Plan Groups, SQL Collector for Monitor Server files, SQL Inspector files, and application source code. The SQL Scanner extracts each SQL statement embedded within the scanned database objects and files, retrieves their respective query plans from Adaptive Server, and then performs an analysis that determines which of these SQL statements may be a performance bottleneck. You can copy the SQL statements analyzed as problematic (top priority) or complex (second priority) into the SQL Optimizer, or Index Advisor, and/or examine the extracted SQL statements with their query plans.

Open a Scanner Group

1. Click .

2. When you scan the database objects or the application files, you first create a Group to store the items you want to scan.

3. If this is the first time you have used the SQL Scanner, the Create Group window displays. Otherwise, click Create in the Group Manager window.

4. Enter a new Group name, e.g. "Test." Click OK to close the Create Group window.

5. Check that your new Group name is highlighted in the list field. Click Open.

Add Scanner Jobs

6. The selected group is opened in the SQL Scanner window. For a new Group, the Add Jobs wizard is automatically opened so you can select what files or database objects you want to scan.

Note: If you are using an existing Group, click .

7. In the Add Jobs wizard, select the page for the item that you want to scan. You can select Database Objects, Abstract Plan Groups, SQL Collector for Monitor Server files, SQL Inspector files, and application Source Code in text or binary format. Click Finish.

Scan Jobs

8. Click .

9. Details are filled in the Job List as the scanning process completes each job. It will show you how many SQL statements found in the Job and how each SQL statement is classified.

View scanning results

10. To view the scanned SQL statements, highlight the job by clicking the row.

11. The first SQL statement found is shown in the SQL Text pane. Click the tabs, e.g. SQL1, SQL2, SQL3, etc., at the bottom left of the pane to view the other SQL statements.

12. Notice the buttons on the top of the SQL Information pane. These buttons display in the, the query plan, the abstract plan, Trace On information, the SQL classification and connection information, the DDL for temporary tables used by the SQL, and the Checked SQL information about SQL statement that you are reviewing.

13. You can narrow the number of original SQL statements to view only the problematic and/or complex statements with View | Problematic SQL and/or View | Complex SQL.

14. Select one SQL statement you want to analyze for performance improvement. Click to copy the SQL statement to the SQL Optimizer window and start the optimization process. Alternatively, you can also send the SQL statement to the Index Advisor. Click to copy the SQL statement to the Index Advisor window and generate index options.

Related Topic

SQL Scanner Overview

Performance Monitor Tutorial

The Performance Monitor monitors and retrieved database performance statistics and performance diagnostics metrics from the Adaptive Server (15.0 and later) monitoring tables. The collected statistics are presented graphically and provides a Top-N view that displays the overall database performance health. The graphical view makes it possible to drill down to get the details of different performance statistics such as engines, CPU, devices, processes, cache, lock, and others.

Retrieve statistics from monitoring tables

1. Click .

2. In the Performance Monitor window enable on the left tree view the detailed performance statistics that you want to monitor. By default, general performance statistics are enabled for monitoring.

3. Click to start the monitoring process.

4. Click to stop the monitoring process.

Note: Monitor options and refresh intervals can be changed by in File | Preferences.

Related Topic

Performance Monitor Overview

SQL Optimizer Tutorial

Due to complex nature SQL, there may be many SQL statements that return the same result set, but only a few that may be efficient. The SQL Optimizer applies advanced SQL transformation technology to generate a list of semantically equivalent SQL statements. To identify the most efficient SQL statement for your database environment you can benchmark test the SQL alternatives in your database.

Optimize a SQL statement

1. Open the SQL Optimizer window by clicking .

2. After entering a SQL statement in the SQL Editor pane of the window, click . This step launches the SQL Optimizer that automatically transforms the SQL statement.

   Notes:

• The use of forces and other optimization options such as temp table generation, ANSI JOIN syntax are optional and configurable in the Preferences.
• The intensity of the SQL transformation process is controlled by the Intelligence Level in the Preferences. The Intelligence Levels control how many forces are applied to transformed SQL and how many SQL alternatives are created.
• If your SQL statement uses a temporary table, see section User-Defined Temp Table for the steps to create a temporary table in the User-Defined Temp Table module.

3. After optimization, the Optimization Details window shows the total number of semantically equivalent SQL statements, the number of alternative statements with query plans different from your original SQL statement, and a warning message if the number of SQL transformations reaches any of the optimization quotas set in the Preferences.

4. Click OK to close the Optimization Details window.

5. In the SQL Optimizer window, look at the tabs which are labeled ALT1, ALT2, ALT3, etc. By clicking the tabs you can see the alternative SQL statements that were created by the SQL optimization process. The query plan for each SQL statement displays beside the SQL text.

6. At the bottom left of the SQL Optimizer window are three tabs, Time, Statistics, and Charts, which display the statistics for each SQL statement after it is executed. At this point since you have not yet run the SQL statements, it displays only the Estimated I/O Cost values. These are only estimations of how each statement will perform. You need to test each statement to obtain its actual run time statistics.

7. In the SQL Optimizer window, look at the right pane to see the query plan for the SQL statement.

Compare SQL alternatives

8. To see how an alternative SQL statement differs from your original SQL, you can compare these statements side-by-side.

• Click . Your original SQL statement displays in one pane of the window and an alternative statement in another pane. Blue highlighted items in one pane show the area where there is a difference from the SQL statement displayed in the other pane.
• At the bottom of the SQL Comparer window, click the Show query plan checkbox to display the query plan for both SQL statements.
• Select File | Close SQL Comparer.

Batch test SQL alternatives

9. To prepare to execute the original and the alternative SQL statements, click .

10. In the Batch Run Criteria window, select the SQL statements that you would like to run in batch. Notice the tabs at the top of the Batch Run Criteria window.

11. Selected SQL Tab

• Select which SQL statements are to be executed. The blue checkmark in the left column indicates that the SQL statement is selected. By default, all statements are executed.
• To deselect a statement, click that SQL statement in the SQL column, for instance click ALT1.
• To deselect all the SQL statements, right-click and select Unselect All.
• To save time when testing run all generated alternatives, a filter function is provided to help you to precisely select the alternatives to test run. Click the Apply SQL Selection filter checkbox. Click to select and apply criteria to narrow down the SQL alternatives to test run.

12. SQL Termination Tab
    Select the option for terminating the execution of your original and the alternative SQL statements. The SQL Optimizer generated all the alternative SQL statements in order to find the optimal SQL. Some of those alternatives may run faster than the original SQL, others may run longer. Therefore, you can set the termination criteria to cancel the longer running SQL statements and save database-processing time for the overall batch test. You have these options for terminating your SQL.

• Original SQL: Terminate the SQL statement when it has run as long as the original SQL.
• Best running time SQL: Run the first SQL statement and use the time from that statement as the termination time. When a SQL statement runs faster than this time, use the faster time as the new termination time, so you are always using the fastest run time as the termination time for the next SQL statement.
• User-defined time: Set your own termination time. If your original SQL statement takes a long time to execute and there are many alternative statements, executing all statements may take considerable time. In that event, consider setting an aggressive user-defined termination time. If the original takes 1 hour, try a 5-minute termination time. If no alternative statements execute in under that period, raise the termination time to 10 minutes, etc.

• You can also combine User-defined time with Original SQL or Best running time SQL by clicking the Or User-defined time checkbox next to each one.

13. Batch Termination Tab
    Select the option for terminating the Batch Run.

• No termination: Specify to run the Batch Run to completion.
• Terminate Batch Run if the specified number of SQL falls in the criteria:

Specify to terminate the Batch Run when a specified number of SQL statements are found that meet the following requirements for terminating the Batch Run.

Number of SQL (excluding Original): Specify how many SQL statements must be found that a show performance improvement over the Original SQL.

Count the SQL if it elapsed time is faster than: Specify one of the following criteria to determine how the performance improvement is determined.

Original SQL: Count all SQL statements that run faster than the run time from the Original SQL.

Original SQL with a percentage of improvement: Count all SQL statements where the run time for the alternative SQL statement is the specified percentage faster than time for the Original SQL statement.

User-defined time (mins/secs): Count all SQL statements that run faster than a specified number of minutes and/or seconds.

14. Run Time Mode Tab

• Run to retrieve: Select First Record to find the time to process the first record. Select All Records to find the time to process all records. You must run the Batch Run twice to get both times.
• Retrieve the run time by executing: Select the number of times to run each SQL alternative. SQL statements can be run more than once to eliminate data caching time and obtain more accurate run time statistics.

Note: The Batch Run function provides an efficient way of benchmarking SQL. It runs the selected SQL statements in the database and the SQL statements that exceed the termination time are cancelled. The Batch Run retrieves the time the SQL statement executes in the database and does not retrieve the result set from the database server to the client; so it does not create additional network traffic. For SQL statements such as SELECT...INTO, INSERT, DELETE and UPDATE, each statement is run in a transaction that is ROLLBACK, therefore maintaining the consistency of your data.

15. Batch Run Schedule Tab

• Start: Select the time you would like the Batch Run to starting executing.
• Until: Select the option for when the Batch Run should finish.

16. To execute the original and the selected alternative SQL statements, click OK. The Batch Run window opens enabling you to view the results as each statement executes.

17. At the completion of the entire job, the Batch Run Details window replaces the Batch Run window. This window provides greater detail about each SQL statement. Click OK.

Review test results

18. Click the Time tab in the SQL Optimizer window to see the columns in the SQL Run Time pane which contain the time to execute the entire statement (all records) and/or the time to retrieve only the first record for each SQL statement from the Batch Run.

19. Once you have identified the most-efficient alternative SQL statement to deploy it:

• You can copy and paste it back in your application.
• If your SQL statement comes from a database objects, you can open the database object source code in the SQL Worksheet anYou can save the alternative SQL statement in a text file either individually or multiple SQL statements in the optimized SQL report. d modify the SQL with the best alternative.
• You can save your SQL optimization results for later review. Select SQL | Saved Optimized SQL.
• If you want to use abstract plans to implement the most-efficient query plan, you should have enabled before optimization dump abstract plan in the Optimization Preferences. If abstract plans are displayed in the SQL Optimizer window, click . Select the abstract plan group and check that the abstract plan is correct. Only save abstract plans that are compatible. Select Save. This process saves the abstract plan to the database in the specified abstract plan group, so the next time you execute the same SQL statement, the saved abstract plan determines the query plan.

Related Topic

SQL Optimizer Overview

Index Advisor Tutorial

The Index Advisor analyzes the syntax of a SQL statement and the database structure and then proposes new index candidates to help improve performance. It provides detailed information on the suggested indexes, such as, space requirements and selectivity. The index recommendations can be benchmarked to identify which index yields the greatest performance gain. It also enables you to create your own indexes for testing.

Create Index candidates

1. Click .

2. In the top pane under the SQL Editor tab, enter the SQL statement for which you want to analyze for index recommendations.

Note: To copy a SQL statement from other windows such as SQL Scanner or SQL Optimizer, click .

3. Click to see the current query plan and get a list of the indexes used in the current query plan. This index information displays in the Used Index tab of the bottom pane.

4. Click .

5. From the Select Tables to Provide Indexes window, select the tables on which you want recommendations for new indexes and specify the sampling size of each table to calculate selectivity. Click OK.

6. Once the advising process is completed, the Index Advising Details window displays detailing the index candidates. Click OK.

7. The index candidates are displayed on the bottom left pane on the tabs labeled Index1, Index2, …IndexN. The Used Index tab displays the DDL for the index(es) currently used by the original SQL statement. The corresponding query plan, abstract plan, SQL classification, trace on and Sort Resource information are displayed in the SQL Information pane at the bottom right.

8. You can add your own index candidates using . This option displays a GUI for you to create indexes for analysis.

Test index candidates

9. To get the actual run time information for the SQL statement under every index scenario, click . Select your benchmarking options in the Batch Run Criteria window. Click OK.

Important Note: This process may impact your database server. Specify the index creation options such as the segment where the index is going to be physically created, and then dropped, and the number of consumers.

10. The Batch Run window opens enabling you to view the results as each statement executes.

11. At the completion of the entire job, the Batch Run Details window replaces the Batch Run window. This window provides greater detail about each SQL statement. Click OK.

12. Click the Time tab in the Index Advisor window to see the results of the Batch Run.

13. To analyze the impact of every index alternative on the query plans of other SQL statements, click .

Related Topic

Index Advisor Overview