Chatee ahora con Soporte
Chat con el soporte

SQL Optimizer for SAP ASE 3.8 - 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: Select SQL page

Configuration Analyzer > Create a Configuration Analysis > New Analysis Wizard: Select SQL page

The Select SQL page is used to select the SQL statements whose query plans you want to analyze when various database parameters are changed.

Select SQL to check for performance changes:

Select SQL from

The SQL statements used in an analysis can be taken from the SQL Repository or the SQL Scanner. If you select SQL statements from the SQL Scanner, these statements are added to the SQL Repository.

SQL query plans will be analyzed:

In the Configuration Analyzer, the query plans for the selected SQL statements before the configuration change are compared to the query plans after the change. The following options allow you to determine the "before" query plans.

Using existing query plan saved with the SQL

This option uses the query plan that was saved with the SQL statement when it was saved in the SQL Repository or SQL Scanner.

Obtaining a new query plan under the current connection

This option retrieves the query plan with the current logon and the current database settings. This current query plan is compared to the query plan that is retrieved after the configuration changes are made.

 

Related Topic

New Analysis Wizard: Configuration Page

Configuration Analyzer > Create a Configuration Analysis > New Analysis Wizard: Configuration Page

The Configuration page is used to change the database parameters. The query plans for the selected SQL statements are retrieved after the parameters are changed in order to analysis the effect changing database parameters will have on the performance of the SQL statements.

Each set of configuration parameters used in an analysis is called a Scenario. When you create an analysis, you can create one Scenario. Additional Scenarios can be created after the analysis is created.

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.

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. 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.

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

Configuration Analyzer > Create a Configuration Analysis > New Analysis Wizard: Abstract Plan

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

Review the following for additional information:

Baseline Plan

 

Description

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement for the before the configuration parameters are changed. 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.

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

 

Description

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement after the configuration parameters are changed. 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.

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

 

Related Topic

Regenerate a Configuration Scenario

Configuration Analyzer > Regenerate a Configuration Scenario

The Scenario can be re-executed using the exact same parameter settings. You can choose to retrieve the query plans from using the same parameter settings for all SQL statements in the Scenario or select specific SQL to retrieve the query plan using the parameter setting.

The function is useful if changes have occurred in the database environment, such as data volumes, that may change the query plans.

To regenerate

  1. Select the Scenario you want to modify from the left pane.

  2. Right-click and select Regenerate.

  3. From the Regenerate Scenario window, check the SQL statements whose current query plans you would like retrieved using the parameter changes.

 

Related Topic

Documentos relacionados

The document was helpful.

Seleccionar calificación

I easily found the information I needed.

Seleccionar calificación