Introduction
Introduction to SQL Optimizer for SAP ASE
New in This Release
Database Versions Supported
User Logon Privileges
Adaptive Server Configuration
Enter Authorization Key
Connect to the Database
Synchronize the Data Dictionary
Tutorials
SQL Inspector Tutorial
SQL Collector for Monitor Server Tutorial
SQL Scanner Tutorial
Performance Monitor Tutorial
SQL Optimizer Tutorial
Index Advisor Tutorial
Abstract Plan Manager Tutorial
SQL Repository Tutorial
Save SQL to the SQL Repository from other modules Tutorial
Configuration Analyzer Tutorial
Migration Analyzer Tutorial
Index Impact Analyzer Tutorial
Index Usage Analyzer Tutorial
Database Explorer Tutorial
Object Extractor Tutorial
SQL Worksheet Tutorial
Code Finder Tutorial
SQL Formatter Tutorial
User-Defined Temp Tables Tutorial
SQL History Tutorial
Preferences
Preferences Overview
Optimization for Pre-ASE 15 Tab
Editor Functions
Optimization Settings for Pre-ASE 15
Intelligence (Pre-ASE 15)
Optimization
Forces (Pre-ASE 15)
Quota (Pre-ASE 15)
Abstract Plan
Optimization for ASE 15 Tab
Optimization Settings for ASE 15
Intelligence (ASE 15)
Optimization
Forces/Goal (ASE 15)
Criteria
Quota (ASE 15)
Abstract Plan
Index Advisor Tab
Directory Setup Tab
Activity Log Tab
SQL History Tab
Plan Tab
SQL Worksheet Tab
SQL Scanner Tab
SQL Classification Tab
General Tab
Editor Tab
Database Setting Tab
DDL Tab
Performance Monitor
Syntax Highlight
Auto-Correction
Member and Argument Lookup
Indent and Outdent Text
Comment and Uncomment Text
Change Text to Uppercase and Lowercase
Bracket Pairing
Customize Editable Panes
Editor Functions
Editor Panes Shortcuts
SQL Information and Functions
Send SQL Statement to SQL Optimizer
Copy to SQL Worksheet
Send to Index Advisor
Open SQL from SQL Repository
Save SQL to SQL Repository
Create Benchmark Factory Import File
SQL Navigation
Modify Data
Copy Data from a Single Cell
Comments
Parameters
Parameters Window
Filter Database Objects
SQL Information Pane
Query Plan
Performance Monitor
Query Plan Overview
Review Query Plans
Query Plan Options
Animate Query Plans
Copy Query Plans
Plan Detail
Search Menu
Performance Monitor Overview
Performance Monitor Privileges
Supported Monitoring Tables
Performance Monitor Window
SQL Inspector
Performance Monitor Window
Right Pane for Database Statistics Summary
Right Pane for Monitoring Statistics
Right Pane for Information
Status Bar
Open the Performance Monitor
Connect and Disconnect from Database
Start and Stop the Performance Monitor
Manual Refresh Data
Hide and Show Console Tree
Chart Information
Display View for Performance Monitor Charts
Chart Drill Down
Lock and Unlock Chart
Chart Zoom
Copy Chart
Print Chart
Save Chart
Performance Monitor Functions
Performance Monitor Preferences
SQL Inspector Overview
SQL Inspector Privileges
Performance Statistics - Monitoring Tables
Performance Statistics - QP Metrics
Data Directory Setting
Open SQL Inspector
SQL Inspector Window
Add/Modify Inspector
SQL Collector for Monitor Server
Add/Modify Inspector
General Information
Monitor Process (Monitoring Tables)
Retrieval Criteria (Monitoring Tables)
Schedule Settings (Monitoring tables)
Retrieval Criteria (QP Metrics)
Schedule Time (QP Metrics)
Inspect
Ad-hoc Inspect
Abort Inspect
Scan Inspector
Find Inspector
Delete Marked Inspector
View Inspector Properties
Place Bookmarks in SQL Inspector Window
Create Benchmark Factory Import File
View and Add Charts
Scan Inspected SQL
Sum Statistics for a Stored Procedure
SQL Inspector Functions
SQL Collector for Monitor Server Overview
Adaptive Server Configuration Parameters for SQL Collector
Data Directory Setting
SQL Collector Window
Open SQL Collector for Monitor Server
Add or Modify a Collector
Query Plan
Monitored SQL Statement Types
Monitor
Ad-hoc Monitoring
Abort Monitor
Why use the SQL Scanner to Identify Problematic SQL Statements
Scan Collectors
Place Bookmarks in SQL Collector
View Particular Types of SQL Statement
Find Specific Information in Collectors
Find SQL Statement
Generate a Report for Monitored SQL Statements
Collector Properties
SQL Collector Functions
SQL Scanner
SQL Scanner Overview
SQL Scanner Window
Group Manager Window
View Group Summary
View Group Properties
Embedded and Dynamic SQL Statements
What files does SQL Scanner scan?
Data Directory Setting
Open SQL Scanner
Add Scanner Jobs
Index Advisor
Add Jobs to SQL Scanner
Add Database Objects (Add Jobs Wizard)
Add Abstract Plan Groups (Add Jobs Wizard)
Add SQL Collectors from the SQL Collector (Add Jobs Wizard)
Add SQL Inspectors (Add Jobs Wizard)
Add Source Code (Add Jobs Wizard)
Summary (Add Jobs Wizard)
Scan
Scan Source Code with Temp Tables
Review Scanned SQL
Find Jobs
Find SQL Using a Text String
View Job Properties
Open Group Manager while SQL Scanner is Open
Check Scanned SQL
Mark and Unmark All Jobs
Move or Copy Jobs to Other Group
Switch Database and User
Modify a Job
Placing Bookmarks in SQL Scanner
Open in SQL Collector
Open in SQL Inspector
Create Benchmark Factory Import File
View a Particular SQL Type
Generate Report for Scanned SQL
Save Abstract Plan
Create Benchmark Factory Import File
SQL Scanner Functions
SQL Conversion
Index Advisor Overview
Index Advisor Window
SQL for Cursor Checkbox
Run Time Tab Page
Statistics Tab Page
Chart Tab Page
Filter the Run Time Results
Advise Index Alternatives
Index Advising Details
Add Index Alternatives
Batch Show Plan
Create Indexes
Analyze Impact of New Indexes
Clear All Index Alternatives
Index Advisor Functions
Batch Run
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
SQL Worksheet
SQL Optimizer Window
Enter Original SQL Statement
Retrieve Query plan
Show Default Plan
Optimize Original SQL Statement
SQL for Cursor Checkbox
Optimize Using Abstract Plan Only
Insert User-Defined SQL
Open Optimized SQL
Create Benchmark Factory Import File
Optimized SQL
Optimized SQL
Optimization Details Window
Input Parameter Values
Analyze Time and Statistics Results
Analyze Run Time Results
Analyze Statistics Results
Analyze Chart Results
Filter Run Time Results
Find SQL Using a Text String
Generate a Report for Optimized SQL
Verify Correctness of Optimized SQL Statements
Insert User-Defined SQL
Check Abstract Plan Compatibility
Save Abstract Plan
Test for Scalability
Save Optimized SQL
Batch Run
Activity Log
Retrieve Run Time for a Group of SQL
Selected SQL
SQL Termination (Options)
SQL Termination (Percentage Delay)
Batch Termination
Run Time Mode
Batch Run Schedule
Commit or Rollback
View Batch Run Details
Run Time and Run Result
Run Time
Retrieve Run Time
Terminate a Run Time SQL Statement
Retrieve Run Result
Commit or Rollback
Terminate Retrieval of Run Result
SQL Comparer
SQL Worksheet Overview
SQL Worksheet Window
Execute SQL or Transact SQL
Retrieve Description of Tables and Views
Modify the Data
New Database Session
Command List
Bookmarks
SQL Worksheet Functions
SQL Formatter
SQL Formatter Overview
SQL Formatter Window
Format a SQL statement
Parameters within SQL Formatter
SQL Formatter Functions
Database Explorer
Database Explorer Overview
Database Explorer Window
Database Explorer Privileges
Open Database Explorer
Modify Data
Copy column names to another window
Database Explorer Functions
Code Finder
Code Finder Overview
Code Finder Window
Search Database Objects and Files
Regular Expression
Open Database Object or File in SQL Worksheet
Extract Object DDL to File
Save Search Results
Code Finder Functions
Object Extractor
Object Extractor Overview
Object Extractor Window
Extract Object DDL and Dependencies
Extraction Criteria
Object Extractor Functions
SQL Repository
SQL Repository Overview
SQL Repository Window
Add SQL to SQL Repository
Index Impact Analyzer
Add SQL to SQL Repository
Add SQL Wizard: General Page
Add SQL Wizard: SQL Information Page
Add SQL Wizard: Settings Page
Refresh SQL Query Plan
Modify SQL
Rename SQL
Delete SQL
Create Benchmark Factory Import File
SQL Repository Functions
Index Impact Analyzer Overview
Index Impact Analyzer Window
Index Usage Analyzer
Index Impact Analyzer Window
Right Pane for Analyzers
Right Pane for Scenarios Analyzed Folder
Right Pane for Scenario
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statements
Create an Index Impact Analyzer
Create an Index Impact Analyzer
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
New Analysis Wizard: Index Page
Add Index Wizard
New Analysis Wizard: Abstract Plan Page
Drop Indexes
View Index Impact Analysis Details
Add SQL to an Index Impact Analysis
Add a Scenario
Delete an Index Impact Analysis
Modify an Index Impact Analyzer
Regenerate
Rename an Index Impact Analyzer
Index Impact Analyzer Functions
Index Usage Analyzer Overview
Index Usage Analyzer Window
Configuration Analyzer
Index Usage Analyzer Window
Right Pane for Analyzers
Right Pane for Tables Analyzed
Right Pane for Analyzed Table Information
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statements
Create an Index Usage Analysis
Create an Index Usage Analysis
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
Update an Index Usage Analysis
View Index Usage Analysis Details
Modify an Index Usage Analysis
Rename an Index Usage Analysis
Delete an Index Usage Analysis
Index Usage Analyzer Functions
Configuration Analyzer Overview
Configuration Analyzer Window
Migration Analyzer
Configuration Analyzer Window
Right Pane for Analyzers
Right Pane for Scenarios Analyzed
Right Pane for Scenarios
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statement
Create a Configuration Analysis
Create a Configuration Analysis
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL page
New Analysis Wizard: Configuration Page
New Analysis Wizard: Abstract Plan
Regenerate a Configuration Scenario
Add a Scenario
View Configuration Analysis Details
Roll Back Changes to Configuration Parameters
Add SQL to a Configuration Analysis
Modify a Configuration Analysis
Rename a Configuration Analysis or Scenario
Delete a Configuration Analysis or Scenario
Modify a Configuration Scenario
Configuration Analyzer Functions
Migration Analyzer Overview
Migration Analyzer Window
Abstract Plan Manager
Migration Analyzer Window
Right Pane for Analyzers
Right Pane for Scenarios Analyzed
Right Pane for Scenarios
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statement
Create a Migration Analysis
Create a Migration Analysis
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
New Analysis Wizard: Migration Page
New Analysis Wizard: Destination Configuration Page
New Analysis Wizard: Abstract Plan
View Migration Analysis Details
Migration Analysis Results
Add a Scenario
Roll Back Changes to Configuration Parameters
Add SQL to a Migration Analysis
Modify a Migration Analysis
Regenerate a Migration Scenario
Delete a Migration Analysis or Scenario
Rename a Migration Analysis or Scenario
Modify a Migration Scenario
Migration Analyzer Functions
Abstract Plan Manager Overview
Abstract Plan Group
Why Save the Abstract Plan?
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
User-Defined Temp Tables
User-Defined Temp Table Overview
Create Temporary Tables
Parameters in Temporary Table SQL
View SQL scripts of temporary tables
Drop Temporary Tables
Example of Temporary Tables in SQL Scanner
Copy SQL with Temporary Tables to SQL Optimizer
Create Alternative SQL Which Uses Temporary Tables
Preference Settings for Handling Temporary Tables
SQL History
SQL History Overview
SQL History Window
Filter SQL Statements
Recall SQL Statements
SQL History Functions
Legal Information
Right Pane for SQL Statement
Configuration Analyzer > Configuration Analyzer Window > Right Pane for SQL Statement
The detailed information about the SQL statements used in the Analysis displays in the button pages. The buttons for displaying specific information are found at the top of this pane.
Properties button
Displays general information about the SQL statement and the database settings at the time of the analysis.
SQL Information button
Displays the SQL text in the top portion of the right pane and two SQL Information panes, one for the SQL information before the analysis and one for the SQL information with the parameter changes, in the bottom right pane.
Saving the Abstract Plan
The abstract plan for a SQL statement can be saved from this pane. In order to save the abstract plan, it must be retrieved when the SQL was selected to be analyzed. This is done from the Abstract Plan page of the New Analysis wizard.
-
Select the SQL statement in the left pane.
-
Click SQL Information section of the right pane. This enables the Save Abstract Plan button.
-
In the Save to group field, select the abstract plan group.
-
Click Save.
.
Create a Configuration Analysis
Create a Configuration Analysis
Configuration Analyzer > Create a Configuration Analysis > Create a Configuration Analysis
To create a Configuration Analysis:
- Click
to open the Configuration Analyzer window.
- If no analysis exists in the Configuration Analyzer then the New Analysis wizard displays automatically. Otherwise, click
to open the New Analysis window.
-
The New Analysis wizard has three pages for selecting the options for creating an analysis:
Page See... Analyzer New Analysis Wizard: Analyzer Page Selected SQL New Analysis Wizard: Select SQL page Configuration New Analysis Wizard: Configuration Page Abstract Plan New Analysis Wizard: Abstract Plan - After making your selection, click OK.
To abort the Configuration analysis
Select Analysis | Abort Analysis.
New Analysis Wizard: Analyzer Page
Configuration Analyzer > Create a Configuration Analysis > New Analysis Wizard: Analyzer Page
View New Analysis Wizard - Analyzer Page
The Analyzer page is used to name the analysis and to select the folder where the SQL statements that you want to analyze are stored.
Creating a new Analyzer and select SQL to be analyzed
Select this option to create a new analysis using a different set of SQL statements.
Continuing an existing Analyzer using the Analyzer's selected SQL
Select this option to use the same set of SQL statements that is stored in an existing Analyzer. This option adds another Scenario under an existing Analyzer and does not retrieve a new set of query plans for the SQL statements. It uses the query plans that were originally saved with the SQL statements.
Do not rollback changes to the ASE configuration parameters
Specific to leaves the database configuration changes that you select under the Configuration tab after the analysis is finished. Otherwise, these changes are rolled back at the end of the analysis processing. After the analysis is complete, the Apply/Undo Changes button displays at the top of the Configuration Analyzer window which displays the Apply Changes and the Undo Changes panes. These panes contain the scripts to make changes to the Adaptive Server configuration parameters.
| Item | Description |
|
Name |
Enter the name for the analysis. |
|
Description |
Enter the description for the analysis. |
|
Last modified |
Displays the last modified date and time. |
|
Save location |
Displays the folder in the tree where the analysis is saved. In the bottom pane, select the folder where you want to save the analysis. To create a new folder, click Add Folder |
.