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.
Specify whether to enforce the tables to be joined in the order specified in the FROM clause.
{Adaptive Server 15 or later}
Specify whether to consider using merge joins. If selected, the parameter will toggle against the server sort_merge setting.
{Adaptive Server 15 or later}
Specify whether to use join transitive closure. If selected, the parameter will toggle against the server JTC setting.
{Adaptive Server 15 or later}
Specify the number of tables that the optimizer considers at one time while determining table join order.
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
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.
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.
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.
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.
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.
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.
The maximum number of SQL statements generated during optimization, this figure consists of: Syntax Transformation Quota + Parallel Quota + Forces Quota.
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.
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.
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 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.
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.
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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Nutzungsbedingungen Datenschutz Cookie Preference Center