Tchater maintenant avec le support
Tchattez avec un ingénieur du support

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

Abstract Plan Manager

Abstract Plan Manager Overview

Abstract Plan Manager > Abstract Plan Manager Overview

The Abstract Plan Manager provides a window for you to easily view, create, delete and modify your abstract plan groups.

In Adaptive Server version 15 and above, the abstract plan enables you to influence the optimization of a SQL statement without having to modify the SQL statement syntax. If you cannot change the source code that contains your SQL statement, you can use the abstract plan to force Adaptive Server to use a specific query plan for a SQL statement. This is particularly useful if you have third party applications where you do not have access to the source code.

Another use of the abstract plan is to protect the performance from changes to the database. When changes are made to a database, Adaptive Server may choose a different query plan for a SQL statement as a result of the changes. The abstract plans provide a means for system administrators and performance tuners to protect the overall performance of a SQL statement from these changes since the abstract plan will cause Adaptive Server to always choose the same query plan.

In the SQL Optimizer window, you can find alternate abstract plans in one of two ways while you are optimizing a SQL statement. First, you can optimize to find only the compatible alternative abstract plans. Second, you can optimize to find the semantically equivalent SQL statements with alternative query plans and then choose the ones with compatible alternative abstract plans.

 

Related Topics

Abstract Plan Compatibility with Original SQL

Abstract Plan Manager Window

Open the Abstract Plan Manager

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans

Abstract Plan Group

Abstract Plan Manager > Abstract Plan Group

Abstract plans are saved to an abstract plan group. The group has the following elements associated with it:

  • Database
  • User-ID
  • SQL statement
  • Abstract Plan

When you save an abstract plan, you specify the database, group and user. This means that you can have a different abstract plan for different users. It enables SQL optimization to be based on the user’s individual activities.

Here is an example of where you might want to have a different abstract plan for different users. In this example, the column emp_sex is indexed.

select * from employee where emp_sex=:var_sex

If 90% employees are male, then the SQL statement to retrieve the records where the employees are male should not be indexed for the best results.

But the SQL statement to retrieve the 10% of the employees who are female should be indexed.

For Group A users, who always select "male" employees, the Abstract Plan should do full table scan. For Group B users, who always select "female" employees, the Abstract Plan should use an index scan.

If you use the default group of ap_stdin, you can set Adaptive Server to use this group server-wide. You must save each abstract plan that you want to use system-wide in this group.

You can set up Adaptive Server to use different abstract plans for the same SQL statement from different applications. In this case, you create a different group for each application. For each user that you want to use a specific abstract plan, you must save that abstract plan in the group.

 

Related Topics

Abstract Plan Compatibility with Original SQL

Abstract Plan Manager Window

Open the Abstract Plan Manager

Abstract Plan Group Functions

Abstract Plan Manager Overview

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans

Why Save the Abstract Plan?

Abstract Plan Manager > Why Save the Abstract Plan?

Saving the abstract plan for a SQL statement preserves how the SQL statement is executed. Therefore, when changes occur in the database environment, the query plan used to execute the SQL statement remains constant.

One major advantage of saving the abstract plan is that you can optimize the SQL statement without altering the SQL text. This is an ideal solution for when you do not have the source code from a vendor but want to improve the performance of the database application. The saving of the abstract plan can be very useful when you are considering making changes to the configuration parameters or you are migrating to another version of Adaptive Server and you want to preserve the current performance of SQL statements that are critical to the performance of your applications.

These database environment changes may include:

  • Parallel degree

  • Table partitioning

  • Indexing

  • Database software upgrades

  • Changes in data columns

You can save an abstract plan from the following modules:

To save in... See:

SQL Optimizer

Save Abstract Plan

SQL Scanner

Save Abstract Plan.

Index Impact Analyzer

Right Pane for SQL Statements

Configuration Analyzer

Right Pane for SQL Statement

Migration Analyzer

Right Pane for SQL Statement

SQL Repository

SQL Repository Window

 

Related Topics

Abstract Plan Compatibility with Original SQL

Abstract Plan Manager Window

Open the Abstract Plan Manager

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans

Documents connexes

The document was helpful.

Sélectionner une évaluation

I easily found the information I needed.

Sélectionner une évaluation