Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

New Analysis Wizard: Destination Configuration Page

The Destination Configuration page is used to change the database parameters on the migration database and only available after you have entered your migrating database connection information on the Migration tab. The query plans for the selected SQL statements are retrieved after the parameters are changed in order to analyze the effect changing Adaptive Server configuration parameters will have on the performance of the SQL statements on the destination Adaptive Server instance.

Item Description

Name

Enter a name for this Scenario.

Description

Enter a description for this Scenario.

Show configuration parameters for

You can narrow the selection of parameters available in the parameter grid by selecting the parameter category from this drop-down list.

Show configuration parameters for

You can narrow the list of Adaptive Server configuration parameters available in the parameter grid by selecting the parameter category from this drop-down list.

Parameter settings grid

Enter the new settings for the parameters you want to analyze and what effect changing them will have on the performance of your SQL statements on the destination Adaptive Server instance. This grid displays the parameter name, the default parameter value, the current value, unit of measurement for the parameter and if you must restart the database before the parameter change can take affect. Enter the new parameter setting in the New Value column.

Minimum and Maximum value

Displays the lowest and the highest value that you can enter for the selected parameter.

Monitor Information

Displays the following information. This information is only available for some of the Adaptive Server configuration parameters.

num_free

Amount of scan descriptors, both metadata and auxiliary, which are not currently in use.

num_active

Amount of scan descriptors, both metadata and auxiliary, which are installed in cache and therefore are in use.

pct_act

Percentage of the total number of scan descriptors that are installed in cache and therefore are in use.

Max_Used

Maximum amount of scan descriptors, both metadata and auxiliary, which were in use at any one time since starting the server was started.

Num_Reuse

Number of scan descriptors reused.

 

Description

Provides an explanation of the function of the selected Adaptive Server configuration parameter.

 

Related Topic

New Analysis Wizard: Abstract Plan

In the New Analysis wizard, the Abstract Plan page is used to include the abstract plan with SQL statements.

Baseline Plan

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement from the system you are migrating from. The abstract plan displays in the right pane for SQL statements. This retrieves a new abstract plan even if one was saved with the original SQL. It is important to use this option if you original saved the SQL statement and abstract plans in version 12.5.0.3 or earlier of Adaptive Server and are now using Adaptive Server 15.0 or later since the format of the abstract plan has changed.

Note: This option is disabled if you have selected Using existing query plan saved with the SQL on the Select SQL page.

Group name

Specify the abstract plan group name where the abstract plans are saved. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.

ap_stdout is used by default to capture an abstract plan.

ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.

Abstract Plan Manager button

Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.

Scenario

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement from the system you are migrating to. The abstract plan displays in the right pane for SQL statements.

Group name

Specify the abstract plan group name where the abstract plans are saved. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.

ap_stdout is used by default to capture an abstract plan.

ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.

Abstract Plan Manager button

Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.

 

Related Topic

View Migration Analysis Details

The Migration Analysis Details window displays information about the retrieve of the query plan for each SQL statement.

The Migration Analysis Details window displays after the batch run process is completed unless the Show details on next batch run checkbox in the Migration Analysis Details window is unchecked.

To view the Migration Analysis Details window after an analysis

  1. Select View | Show Migration Analysis Details.

  2. Review this information to see if an error occurred during the retrieval of the query plan. Errors may occur if the selected SQL statements are from one database and user and you have set another database and user in the drop-down boxes at the bottom left of the main window.

 

Related Topic

Migration Analysis Results

Upon completion of the migration analysis process, a summary of the analysis is the Migration Analysis Details window, which tells if any of the query plans retrieval failed for any of the SQL in the Scenario. The comparison of the query plans, estimated I/O cost, abstract plans, Trace On information, and SQL Classification displays in the right pane of the Migration Analyzer window.

Once the analysis is complete, the best way to compare the behavior of the SQL in the analysis is to select the Scenario from the left tree structure.

Properties Button

The initial view is of the Properties of the Scenario and it gives a basic summary of the analysis including the configuration parameters that were specified as different between the databases.

Prognosis Button

The Prognosis section of an analysis is the best way to analyze the results to see how the performance of the SQL statements will be affected by the migration. The Prognosis has two tabs in the top pane that provide the overview of the impact of the migration on SQL performance: Chart and Summary. Specific information about the individual SQL statements displays in the middle and bottom panes.

Click any portion of the chart and the SQL statements represented are highlighted in the grid below the charts.

Chart Tab

Total Cost Chart

This chart shows the overall Estimated I/O cost differences of the SQL statements when connected to the current Adaptive Server instance and when connected to the destination Adaptive Server instance.

SQL with Cost Change Chart

This chart represents those specific statements whose query plans undergo a change in Estimated I/O cost. Click any portion of the chart and the SQL statements represented are highlighted in the grid below the charts.

SQL with Plan Structure Change Chart

This chart represents those statements that experience a structure change in their query plan, regardless of any cost changes.

Classification Changes Chart

This chart indicates any SQL statements whose query plans undergo a change in classification (Simple, Complex, and Problematic). The statements that experience degradation in performance would be the best statements to work with to optimize before migrating to the new Adaptive Server instance, since they are the statements that could potentially cause performance problems.

SQL Classification Chart

This chart gives the aggregate view of the classifications of the SQL statements in the analysis.

Summary Tab

Presents a summary of the analysis in text format showing the statistics that are used in the charts.

Configuration Parameters Button

Displays the configuration parameters for the destination Adaptive Server instance. For each parameter it displays current value and the old parameter value before the analysis. The Configuration Parameters pane is only displayed if changes to configuration parameters were specified for the destination instance during the migration analysis.

Apply Changes

Displays the script for making the configuration parameters changes on Adaptive Server. Press Executeimage\B_Execute.gif to run the script.

 

Related Topic

Related Documents