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

Forces (Pre-ASE 15)

Preferences > Optimization for Pre-ASE 15 Tab > Forces (Pre-ASE 15)

The Forces page on the Optimization for Pre-ASE 15 tab of the Preferences window allows you to decide if you would like to have the Adaptive Server forces applied to alternative SQL statements. By adding an Adaptive Server force, you can force the Adaptive Server’s internal optimizer to choose a particular query plan.

Set

Set Forceplan On

Specify whether to enforce the tables to be joined in the order specified in the FROM clause.

Set Sort_Merge On/Off

{Adaptive Server 15 or later}

Specify whether to consider using merge joins. If selected, the parameter will toggle against the server sort_merge setting.

Set JTC On/Off

{Adaptive Server 15 or later}

Specify whether to use join transitive closure. If selected, the parameter will toggle against the server JTC setting.

Set Table Count

{Adaptive Server 15 or later}

Specify the number of tables that the optimizer considers at one time while determining table join order.

Parallel force

PARALLEL

Specify whether to enforce the use of a particular parallelism degree to access the table if parallel query is enabled in the database server.

Index force

INDEX

Specify whether to enforce the use of a particular index to access the table.

Note: The forces that are marked with an asterisk are counted in the Number of forces selected on the Quota Preferences page.

 

Related Topics

Quota (Pre-ASE 15)

Preferences > Optimization for Pre-ASE 15 Tab > Quota (Pre-ASE 15)

Quota (Pre-ASE 15)

The Quota page on the Optimization tab of the Preferences window allows you to restrict the number of SQL transformations produced during optimization process. The optimization quotas can only be changed when the optimizer intelligence level is set to custom.

Syntax Transformation Quota (Range = 1 to 99,999)

The maximum number of SQL statements generated by applying rules in the Feedback-Searching engine. The default quota of 100 is normally sufficient for most of the complicated SQL statements. However you can increase the quota to tackle exceptionally complicated SQL statements with very high levels of table joins or multiple levels of nested sub-queries.

This value controls the number of SQL statements that are generated during the process of rewriting the syntax of the SQL statement.

Parallel Quota (Range = 10 to 99,999)

The maximum number of SQL statements generated by applying the degree of parallelism in the Feedback-Searching engine. Only applicable if the MAXDOP checkbox is selected.

Forces Quota Ratio (Range = 1% to 100%)

The percentage used to calculate the maximum number of SQL statements to apply forces.

This value determines the number of SQL alternatives that are generated by applying the Forces to the original SQL and the SQL alternatives that were created by transforming the SQL syntax.

Number of forces selected (forces with * only)

A read only field indicating the number of forces selected. Only the forces that have an * after the name on the Forces page are counted. This figure is used to calculate the maximum number of SQL statements generated by applying forces, ((Syntax Transformation Quota + Parallel Quota )* Forces Quota Ratio%)*Number of Forces Selected = Forces Quota.

Total Quota

The maximum number of SQL statements generated during optimization, this figure consists of: Syntax Transformation Quota + Parallel Quota + Forces Quota.

Table Join Permutation Quota (Range = 50 to 999,999)

The maximum number of table joins rearrangements to generate a new table join access path during optimization.

Note: You should bear in mind that the higher the quota, the longer it might take to optimize a complicated SQL statement.

 

Related Topics

Abstract Plan

Preferences > Optimization for ASE 15 Tab > Abstract Plan

Abstract Plan (Optimization Tab)

The Abstract Plan page on the Optimization tab of the Preferences window allows you to include the retrieval of the abstract plan for the SQL statements. This requires Adaptive Server 15 or later.

Dump abstract plan

Specify whether to retrieve the abstract plan for the SQL statement whenever the query plan is retrieved. The abstract plan displays in the SQL Optimizer window. The abstract plan is not saved on the database until you deliberately save it.

When this checkbox is selected, the selected abstract plan icon image\I_AbstractPlanGroup.gif and the abstract plan group name display on the bottom right of the main status bar.

Note: For third-party package applications, you are able to optimize SQL statements without changing the source code by saving the abstract plan.

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.

Check compatibility with Original SQL

Specify whether you want to check the compatibility of the optimized SQL statements with the original SQL. When this option is selected, the AP Compatibility column displays on the SQL Run Time pane of the SQL Optimizer window and in the Batch Run Criteria window.

Note: A SQL statement may have many different "semantically equivalent" SQL statements. Each statement has an abstract plan. Even though SQL statements are "semantically equivalent", the abstract plans may not be compatible with the original SQL statement. When you check this option, the alternative abstract plans that are compatible with your original SQL are identified.

 

Related Topics

Optimization for ASE 15 Tab

Documentos relacionados

The document was helpful.

Seleccionar calificación

I easily found the information I needed.

Seleccionar calificación