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

Forces/Goal (ASE 15)

The Forces/Goal page on the Optimization for 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.

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.

Optimization Goal

allrows_mix

Specify to set the optimization goal for a database environment that is a mixture of online transactions and database warehouse transactions.

allrows_dss

Specify to set the optimization goal for a database environment that is purely for database warehouse transactions.

allrows_oltp

Specify to set the optimization goal for a database environment that is purely for oltp (online transaction processing) transactions.

fastfirstrow

Specify to set the optimization goal to retrieve the first row as fast as possible.

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

 

Related Topics

Criteria

The Optimization Criteria are session level settings. Each criteria represents a specific algorithm or relational techniques that the Adaptive Server Optimizer may choose to use when it is retrieving the query plan. They are used to fine-tune the Adaptive Server optimizer to provide the best performance for your database environment.

Although you may specify that Adaptive Server optimizer use a specific algorithm or relational technique, the optimizer may not choose what you have specified.

Join

Optimization Criteria Description
hash_join Specify that the Adaptive Server optimizer uses the hash join algorithm. This is valuable when a large number of rows satisfy the join condition or when the joining columns do not have useful indexes. However, the hash join algorithm may consume more run time resources that other join algorithms.

nl_join

Specify that the Adaptive Server optimizer uses the nested-loop-join algorithm.

merge_join

Specify that the Adaptive Server optimizer uses the merge join algorithm. This relies on ordered input and is most valuable when the input is ordered on the merge key.

Union Distinct

Optimization Criteria Description
merge_union_distinct Specify that the Adaptive Server optimizer uses the merge algorithm for the UNION ALL. It is similar to merge_union_all except that duplicate rows are eliminated.

hash_union_distinct

Specify that the Adaptive Server optimizer uses the hash distinct algorithm. This will be inefficient if most of the rows are distinct.

Union All

Optimization Criteria Description
append_union_all Specify that the Adaptive Server optimizer uses the append union all algorithm.

merge_union_all

Specify that the Adaptive Server optimizer uses the merge algorithm for the UNION ALL. This means that it will maintain the ordering of the result row from the union input.

Group By

Optimization Criteria Description

group_sorted

Specify that the Adaptive Server optimizer uses an on-the-fly algorithm. This algorithm relies on an input stream sorted on the grouping column and preserves this order in its output.

group_hashing

Specify that the Adaptive Server optimizer uses the use a group hashing algorithm to process aggregates.

Distinct

Optimization Criteria Description

distinct_sorted

Specify that the Adaptive Server optimizer uses a single-pass algorithm to eliminate duplicate rows. This algorithm relies on an ordered input stream.

distinct_sorting

Specify that the Adaptive Server optimizer uses the sorting algorithm to eliminate duplicate rows. This algorithm is useful when the input is not ordered.

opportunistic_distinct _view

Specify that the Adaptive Server optimizer uses a more flexible algorithm when enforcing distinctness.

distinct_hashing

Specify that the Adaptive Server optimizer uses the hashing algorithm to eliminate duplicates, which is very efficient when there are only a few distinct values in comparison to the number of rows.

Individual

Optimization Criteria Description

store_index

Specify that the Adaptive Server optimizer uses the reformatting, which may cause an increase in the number of worktables used.

parallel_query

Specify that the Adaptive Server optimizer uses parallel query optimization.

index_intersection

Specify that the Adaptive Server optimizer uses the intersection of multiple index scans as part of the query plan in search space.

multi_table_store_ind

Specify that the Adaptive Server optimizer uses reformatting on the result of a multiple table join.

bushy_space_search

Specify that the Adaptive Server optimizer uses bushy-tree-shaped query plans, which may cause an increase in the search space, but may provide more query plan options to improve performance.

advanced_aggregation

Specify that the Adaptive Server optimizer uses eager aggregation.

replicated_partition

Specify that the Adaptive Server optimizer uses replicated partitioning when multiple scans of the same table in different threads can improve the performance of nested loop joins.

group_inserting Specify that the Adaptive Server optimizer uses the group by aggregation algorithm. This algorithm generates a clustered index work table on the grouping columns and inserts rows into the table in order to evaluate the aggregate.

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

Optimization Time and Parallel Description
plan opttimeoutlimit

Specify that the Adaptive Server optimizer uses the optimization timeout limit to restrict the amount of time it takes to optimize a query.

Note: The default value is 60 percent. You can specify a value from 0 to 1000.

resource_granularity Specify that the Adaptive Server optimizer uses max resource granularity to set the amount of total memory allocated to a single query.
repartition_degree Specify that the Adaptive Server optimizer uses repartition degree to suggest the maximum number of worker processes the query processor uses to partition a data stream.

 

Related Topics

Quota (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, goals, and criteria selected. Only the items that have an * after the name on the Forces/Goals and the Criteria pages are counted. This figure is used to calculate the maximum number of SQL statements generated by applying these selections, ((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

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

Related Documents