®

SQL Optimizer for Oracle 9.3.1

User Guide
Copyright 2019 Quest Software Inc.
ALL RIGHTS RESERVED.
This guide contains proprietary information protected by copyright. The software described in this guide is
furnished under a software license or nondisclosure agreement. This software may be used or copied only in
accordance with the terms of the applicable agreement. No part of this guide may be reproduced or transmitted
in any form or by any means, electronic or mechanical, including photocopying and recording for any purpose
other than the purchaser’s personal use without the written permission of Quest Software Inc.
The information in this document is provided in connection with Quest Software products. No license, express
or implied, by estoppel or otherwise, to any intellectual property right is granted by this document or in
connection with the sale of Quest Software products. EXCEPT AS SET FORTH IN THE TERMS AND
CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, QUEST SOFTWARE
ASSUMES NO LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORY
WARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTY
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. IN NO EVENT
SHALL QUEST SOFTWARE BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE,
SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF
PROFITS, BUSINESS INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT OF THE USE OR
INABILITY TO USE THIS DOCUMENT, EVEN IF QUEST SOFTWARE HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES. Quest Software makes no representations or warranties with respect to the
accuracy or completeness of the contents of this document and reserves the right to make changes to
specifications and product descriptions at any time without notice. Quest Software does not make any
commitment to update the information contained in this document.
If you have any questions regarding your potential use of this material, contact:
Quest Software Inc.
Attn: LEGAL Dept
4 Polaris Way
Aliso Viejo, CA 92656
Refer to our Web site ([Link] for regional and international office information.
Patents
SQL Optimizer for Oracle is protected by U.S. Patents No. 8,332,346 and 8,499,001. Additional patents
pending. For the most current information about applicable patents for this product, please visit our website at
[Link]
Trademarks
Quest, Quest Software, Foglight, Spotlight, Toad , and the Quest logo are trademarks of Quest Software Inc. in
the U.S.A. and other countries. For a complete list of Quest Software trademarks, please visit our website at
[Link] Microsoft, Windows, Windows Server, and Windows Vista are either registered
trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Oracle is a
trademark or registered trademark of Oracle and/or its affiliates in the United States and other countries. Citrix
and XenApp are trademarks of Citrix Systems, Inc. and/or one or more of its subsidiaries, and may be
registered in the United States Patent and Trademark Office and in other countries. Other trademarks are
property of their respective owners.
Legend

IMPORTANT, NOTE, TIP, MOBILE, VIDEO: An information icon indicates supporting information.

CAUTION: A CAUTION indicates potential damage to hardware or loss of data if instructions are
not followed.

SQL Optimizer for Oracle 9.3.1

User Guide
Tuesday, March 19, 2019
 Contents

Contents 3

Introduction to SQL Optimizer 4

About SQL Optimizer for Oracle 4
Optimize SQL 4
Optimize Indexes 5
Batch Optimize 5
Scan SQL 5
Inspect SGA 5
Analyze Impact 5
Manage Plans 5
SQL Optimization Workflow 6
Database Privileges 7

Use SQL Optimizer 12

Tutorial: Optimize SQL (SQL Rewrite) 12
Step 1: Optimize the SQL Statement 12
Step 2: Test Alternative SQL Statements 12
Tutorial: Optimize SQL (Plan Control) 14
Step 1: Generate and Execute Execution Plan Alternatives 14
Step 2: Deploy Execution Plan as a Baseline 14
Tutorial: Generate Indexes in Optimize SQL 16
Tutorial: Best Practices 17
Tutorial: Deploy Outlines 18
Tutorial: Batch Optimize SQL 19
Tutorial: Scan SQL 22
Tutorial: Inspect SGA 24
Tutorial: Analyze Impact 25
Tutorial: Send Indexes to Analyze Impact 27
Tutorial: Manage Outlines 28
Optimize Indexes for SGA Workload 29
Review Optimize Indexes Results 31

About Us 34
We are more than just a name 34
Our brand, our vision. Together. 34
Contact Quest 34
Technical Support Resources 34
SQL Optimizer Community 35

SQL Optimizer for Oracle 9.3.1 User Guide

3
Contents
 1
Introduction to SQL Optimizer

About SQL Optimizer for Oracle

SQL Optimizer for Oracle® automates the SQL optimization process and maximizes the performance of your
SQL statements. SQL Optimizer for Oracle® analyzes, rewrites, and evaluates SQL statements located within
database objects, files, or collections of SQL statements from Oracle's System Global Area (SGA). Once SQL
Optimizer identifies problematic SQL statements, it optimizes the SQL and provides replacement code that
includes the optimized statement.
SQL Optimizer also provides a complete index optimization and plan change analysis solution. It provides index
recommendations for multiple SQL statements or a SQL workload, simulates index impact analysis, and
generates SQL execution plan alternatives.
SQL Optimizer consists of the following modules:

Optimize SQL
Optimize SQL includes a SQL Rewrite mode and a Plan Control mode.

SQL Rewrite Description

Mode

Optimize SQL Uses SQL Optimizer's Artificial Intelligence engine to execute SQL syntax rules and
Statements apply Oracle optimization hints to create semantically equivalent SQL statement
alternatives. In addition, you can create user-defined alternatives to test with your
database environment. See "About Optimizing SQL" in the online help for more
information.

Test Run SQL Test run SQL statement alternatives to view execution statistics. This provides
Alternatives execution times that allow you to identify the best SQL statement for your database
environment. See "Execute Scenarios" in the online help for more information.

Generate Index Analyzes SQL statement syntax and database structure to provide index alternatives
Alternatives that improve performance. SQL Optimizer uses virtual indexes to generate
alternatives without physically creating the indexes on your database. See "About
Index Generation" in the online help for more information.

Test for Uses Benchmark Factory™ to simulate potential workload conditions to test SQL
Scalability statement performance. See "Test for Scalability" in the online help for more
information.

Incorporate Best Incorporates common best practices techniques to improve database performance.
Practices See "Best Practices" in the online help for more information.

SQL Optimizer for Oracle 9.3.1 User Guide

4
Introduction to SQL Optimizer
 Plan Control Description
Mode

Generate Generates execution plan alternatives for your SQL statements without changing the
Execution Plan original source code. See "Generate Execution Plan Alternatives" in the online help
Alternatives for more information.

Deploy Baselines Creates baselines from the execution plan alternatives and deploys these baselines
to ensure optimal database performance. See "Deploy Baselines" in the online help
for more information.

Optimize Indexes
Optimize Indexes analyzes a SQL workload or any group of SQL statements and determines the best index set
for the workload or group of statements. See "About Optimize Indexes" in the online help for more information.

Batch Optimize
Batch Optimize submits files, database objects, or SQL text for batch processing. Batch Optimize scans and
extracts the SQL statements, optimizes the statements, and tests the alternatives to find the best performing SQL
statements for your database environment. See "About Batch Optimize" in the online help for more information.

Scan SQL
Scan SQL identifies problematic SQL statements in your source code and database objects without execution.
Scan SQL then analyzes the problematic SQL statements and categorizes them according to performance
levels. See "About Scanning SQL" in the online help for more information.

Inspect SGA
Inspect SGA analyzes SQL statements from Oracle's SGA. You can specify the criteria used to retrieve SQL
statements and execution statistics to review SQL performance. See "About Inspect SGA" in the online help for
more information.

Analyze Impact
Use Analyze Impact to evaluate the impact on a SQL workload from database changes by tracking execution
plan and Oracle cost changes for SQL statements. You can run an impact analysis to estimate the performance
impacts from parameter changes and new indexing. You can also run a comparison of two different databases
that run the same application. See "About Analyze Impact" in the online help for more information.

Manage Plans
Manage Plans organizes stored baselines and outlines used to improve SQL statement performance. See
"About Managing Plans" in the online help for more information.

SQL Optimizer for Oracle 9.3.1 User Guide

5
Introduction to SQL Optimizer
SQL Optimization Workflow
The SQL Optimization workflow ensures that your SQL statements perform optimally in your database
environment.

Procedure Description

Identify Batch Optimize extracts embedded SQL statements from your database objects. After
Problematic SQL extracting the statements, it analyzes execution plan operations and identifies
potential performance bottlenecks.
Notes:
l You can use Inspect SGA to capture dynamic SQL statements. Save the
captured dynamic SQL statements into an inspector file and use Batch
Optimize to extract the statements.
l You can also use Scan SQL to extract embedded SQL statements.

Optimize SQL Once Batch Optimize identifies problematic SQL statements, it automatically
Statements optimizes these statements and generates alternatives with unique execution plans.
Batch Optimize generates the alternatives by analyzing SQL statement syntax and
database structure. You can also use hints during the optimization process.
Note: You can also use SQL Rewrite mode in Optimize SQL to optimize
SQL statements extracted with Scan SQL.

Test Run SQL After Batch Optimize generates alternatives, it will automatically test run the
Alternatives alternatives and provide you with the best statement for your database environment.
Note: Since Batch Optimize automates the SQL optimization process, you are
only provided with the best alternative statement it generates. You can send your
statement to Optimize SQL to view all statement alternatives available.

Compare SQL Batch Optimize compares the SQL text and execution plans of your original SQL
Alternatives statement and the best alternative.
Note: If you send your statement to Optimize SQL, you can compare your original
SQL statement with any of the statement alternatives available.

Replace Batch Optimize creates a script that you can use to replace your original source code.
Problematic SQL
Statements

Generate Index In addition to optimizing SQL statements, you can generate index alternatives for
Alternatives your statement in Optimize SQL. You can generate new indexes for a group of SQL
statements or for a SQL workload using Optimize Indexes.

Generate You can use Plan Control mode in Optimize SQL to generate execution plan
Execution Plan alternatives for your SQL statements without changing the original source code. Plan
Alternatives Control mode finds the best execution plan for your SQL statement and deploys it as
a baseline.

Analyze Analyze Impact evaluates what impact certain changes, such as indexing and
Performance parameter changes, will have on SQL statement performance.
Changes

SQL Optimizer for Oracle 9.3.1 User Guide

6
Introduction to SQL Optimizer
Database Privileges
Oracle database privileges limit access for individual users. The following list summarizes the functions in SQL
Optimizer that require specific Oracle database privileges.

Module Functionality Privilege

All Modules Trace setup Requires ALTER SESSION privileges.

options: Requires access to the following views:
Enable collection SYS.V_$SESSION
of Oracle trace
SYS.V_$PROCESS
statistics

All Modules Retrieve DBMS_ Requires access to the SYS.DBMS_XPLAN package.

XPLAN

All Modules General If the Oracle init parameter O7_DICTIONARY_ACCESSIBILITY

for Oracle 8 or later is set to false, you cannot access objects
under SYS even if you have SELECT ANY TABLE privileges.
In this case, you need SELECT ANY DICTIONARY privileges
or SELECT_CATALOG_ROLE to access the objects under
SYS.

Optimize SQL Alter session Requires access to SYS.V_$PARAMETER view.

(SQL Rewrite) parameters for
executing SQL

Generate virtual Requires Oracle 8i or later.

indexes

Check existing Requires Oracle 12c or later.

translation for Requires access to the SYS.ALL_SQL_TRANSLATIONS view.
your SQL

Create SQL Requires Oracle 12c or later.

Translation Requires CREATE SQL TRANSLATION PROFILE privileges.
Profile

Deploy outlines Requires Oracle 8i or later.

Requires CREATE ANY OUTLINE and DROP ANY OUTLINE
privileges.
Requires access and UPDATE privileges to the following
views:
[Link]$HINTS
[Link]$
[Link]$NODES

Register SQL Requires Oracle 12c or later.

Translation Requires access to the SYS.DBMS_SQL_TRANSLATOR
package.
Requires access to the SYS.ALL_SQL_TRANSLATION_
PROFILES view.

Optimize SQL Open database Requires Oracle 11g or later.

(Plan Control) connection

SQL Optimizer for Oracle 9.3.1 User Guide

7
Introduction to SQL Optimizer
Module Functionality Privilege

Retrieve Requires ADMINISTER SQL MANAGEMENT OBJECT

execution plans, privileges.
generate Requires access to the following packages:
alternative plans
SYS.DBMS_SQL
SYS.DBMS_SPM
SYS.DBMS_XPLAN
Requires access to the following views:
SYS.DBA_SQL_PLAN_BASELINES
SYS.V_$SQLAREA
SYS.V_$SQLTEXT_WITH_ NEWLINES

Optimize SQL Execution Requires access to the SYS.DBMS_SQL package.

and Batch method option:
Optimize Run on server
setting

Retrieve run-time Requires access to the following views:

statistics SYS.V_$MYSTAT
SYS.V_$STATNAME
SYS.V_$PARAMETER

Retrieve actual Requires ALTER SESSION privileges.

plan Requires access to the SYS.DBMS_XPLAN package.
Requires access to the following views:
SYS.V_$SQLAREA
SYS.V_$ SQL_PLAN_STATISTICS_ALL
SYS.V_$SESSION

Capture bind Requires Oracle 10g or later.

values from Requires access to the following views:
database
SYS.V_$SQLAREA
SYS.V_$SQL_BIND_CAPTURE

Optimize Indexes Recommend Requires Oracle 8i or later.

indexes Requires access to the SYS.V_$SESSION view.

Access the AWR Requires Oracle 10g or later.

Requires access to the following system views:
SYS.DBA_HIST_SNAPSHOT
SYS.DBA_HIST_SQLTEXT
SYS.DBA_HIST_SQLSTAT

Display the Requires access to the following system view:

control SYS. DBA_HIST_WR_CONTROL
information for
the Workload
Repository

SQL Optimizer for Oracle 9.3.1 User Guide

8
Introduction to SQL Optimizer
Module Functionality Privilege

Display the SQL Requires access to the following system view:

summary for the SYS. DBA_HIST_SQL_SUMMARY
Workload
Repository

Access the SGA Requires access to the SYS.V_$SQLAREA view.

Inspect SGA SQL to collect: Requires access to the following views:

Executed SQL SYS.V_$SQLAREA
from SQL area
SYS.V_$SQLTEXT_WITH_NEWLINES (or SYS.V_
$SQLTEXT depending on your version of Oracle)
Requires access to SYS.V_$SQL_PLAN view in Oracle 9 or
later.

SQL to collect: Requires access to the following views:

Currently SYS.V_$OPEN_CURSOR
executing SQL
SYS.V_$SESSION
SYS.V_$SQLAREA
SYS.V_$SQLTEXT_WITH_NEWLINES (or SYS.V_
$SQLTEXT depending on your version of Oracle)
Requires access to SYS.V_$SQL_PLAN view in Oracle 9 or
later.

Flush Oracle Requires ALTER SYSTEM privileges.

shared pool

Execution plan Requires access to SYS.V_$SQL_PLAN view in Oracle 9 or

information later.

Monitor by Requires access to the SYS.V_$SESSION view.

session

Manage Plans Open a Baselines Requires Oracle 11g or later.

(Baselines Management
Management) session or
database
connection

View baselines Requires access to the SYS.DBA_SQL_PLAN_BASELINES

view.

View baseline Requires access to the SYS.DBA_SQL_PLAN_BASELINES

plans view.
Requires access to the SYS.DBMS_XPLAN package.

Import, export, Requires ADMINISTER SQL MANAGEMENT OBJECT and

and migrate CREATE TABLE privileges.
baselines Requires access to the SYS.DBA_SQL_PLAN_BASELINES
view.
Requires access to the SYS.DBMS_SPM package.

View and modify Requires ADMINISTER SQL MANAGEMENT OBJECT and

baseline ALTER SYSTEM privileges.
configuration Requires access to the SYS.DBMS_SPM package.
values
Requires access to the following views:

SQL Optimizer for Oracle 9.3.1 User Guide

9
Introduction to SQL Optimizer
Module Functionality Privilege

Obtaining the SYS.V_$SYSTEM_PARAMETER

Oracle parameter SYS.DBA_SQL_MANAGEMENT_CONFIG
values
SYS.DBA_DATA_FILES
OPTIMIZER_
CAPTURE_SQL_
PLAN_
BASELINES
and OPTIMIZER_
USE_SQL_
PLAN_
BASELINES

Manage Plans Open an Outlines Requires Oracle 8i or later.

(Outlines Management
Management) session or
database
connection

View outlines Requires [Link]$HINTS (SELECT only) and [Link]$

(SELECT only) privileges.

Modify outline Requires ALTER SYSTEM and [Link]$ (SELECT only)

configuration privileges.
values

Modify outlines Requires DROP ANY OUTLINE and ALTER ANY OUTLINE
privileges.
Requires access to the SYS.OUTLN_PKG package.

Analyze Impact Virtual indexes Requires Oracle 8i or later.

for Index Impact Requires access to the following system view:
SYS.V_$SESSION

Altering session Requires access to the following system view:

parameters for SYS.V_$PARAMETER
Parameter Impact

Access AWR Requires Oracle 10g or later.

Requires access to the following system views:
SYS.DBA_HIST_SNAPSHOT
SYS.DBA_HIST_SQLTEXT
SYS.DBA_HIST_SQLSTAT

Display the Requires access to the following system view:

control SYS. DBA_HIST_WR_CONTROL
information for
the Workload
Repository

Display the SQL Requires access to the following system view:

summary for the SYS. DBA_HIST_SQL_SUMMARY
Workload
Repository

Access SGA Requires access to the following system view:

SQL Optimizer for Oracle 9.3.1 User Guide

10
Introduction to SQL Optimizer
Module Functionality Privilege

SYS.V_$SQLAREA

Display database Requires access to the following system views:

information for SYS.PRODUCT_COMPONENTS_VERSION
Compare
SYS.V_$PARAMETER
Database Impact
SYS.NLS_SESSION_PARAMETERS
SYS.NLS_INSTANCE_PARAMETERS
SYS.NLS_DATABASE_PARAMETERS
SYS.V_$SGAINFO
SYS.V_$DATABASE
SYS.V_$INSTANCE
SYS.V_$STATISTICS_LEVEL
SYS.V_$OPTION

SQL Optimizer for Oracle 9.3.1 User Guide

11
Introduction to SQL Optimizer
 2
Use SQL Optimizer

Tutorial: Optimize SQL (SQL Rewrite)

Using SQL Rewrite mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates
semantically equivalent alternatives with unique execution plans for your original SQL statement. An Oracle cost
estimate displays for each alternative generated. In the second step, SQL Optimizer executes the alternatives to
test each statement's performance. This provides execution times and run time statistics that allow you to find
the best SQL statement for your database environment.
Tip: The Oracle cost only provides an estimate of resource usage to execute a SQL statement. Since
statements with higher cost may perform better, you should test alternatives generated to determine the best
statements for your database environment.

Step 1: Optimize the SQL Statement

1. Select the Optimize SQL tab in the main window.
2. Select SQL Rewrite from the Optimize SQL start page.

Note: If the start page does not display, click the arrow beside and select New SQL
Rewrite Session.

3. Enter a SQL statement in the Alternative Details pane.

4. Click to retrieve the execution plan for your SQL [Link] Select Connection and Schema
window displays.
5. Select a connection and schema to use.

6. Click to optimize the SQL statement.

7. Click after SQL Optimizer completes the SQL rewrite process to compare your original
SQL statement with the alternatives generated.

Step 2: Test Alternative SQL Statements

To test the alternatives SQL Optimizer generates, you can test run one or more selected alternatives to obtain
actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these
statistics without having to retrieve result sets from the database server. Additionally, data consistency is
maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these
statements run in a transaction that is rolled back after execution.

SQL Optimizer for Oracle 9.3.1 User Guide

12
Use SQL Optimizer
To test a SQL statement alternative

1. Click after you finish comparing your original SQL statement with the
alternatives generated.

2. Click the drop-down arrow beside and select Test Run - All to execute all SQL alternatives.

3. The Test Run Settings dialog opens. Select criteria to apply to this test run. You can answer the
questions on the Usage and Symptom page to allow SQL Optimizer to determine settings for you. Or
you can click the Customize Test Run Settings link at the bottom of the page to manually specify test
run settings.

Tip: You can specify default values for some Test Run Settings options by clicking and
selecting Optimize SQL | Test Run.

4. Review the execution statistics in the Alternatives pane.

SQL Optimizer for Oracle 9.3.1 User Guide

13
Use SQL Optimizer
Tutorial: Optimize SQL (Plan Control)
Using Plan Control mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates
execution plan alternatives for your SQL statement without changing the source code. You can then execute the
alternatives to retrieve run time statistics and identify the best alternative for your database environment. In the
second step, you can use Plan Control mode to deploy the execution plan to the Manage Plans module as an
Oracle plan baseline.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field
descriptions.

Step 1: Generate and Execute Execution Plan

Alternatives
1. Select the Optimize SQL tab in the main window.
2. Select Plan Control from the Optimize SQL start page.

Note: If the start page does not display, click the arrow beside and select New Plan
Control Session.

3. Enter a SQL statement in the Original SQL pane.

Tip: Select the This SQL is contained inside a PL/SQL block checkbox if your SQL statement
originated from a PL/SQL block. Selecting this checkbox ensures that the SQL text for the baseline
you create matches the SQL text in your database.

4. Click to generate alternative execution plans for your SQL statement. The Select Connection and
Schema window displays.
5. Select a connection and schema to use.

6. Click to execute all alternative execution plans to retrieve run time statistics.

7. Review the run time statistics in the Plans pane to identify the best alternative.

Step 2: Deploy Execution Plan as a Baseline

1. Click .

2. Review the following for additional information:

Deploy Description

Select a plan Click and select an execution plan alternative to deploy as a baseline plan.
to deploy

Performance Use this to review the performance of selected plans against the original.
comparison

Mark the plan Review the following for additional information:

as
l Enabled—Select whether to enable or disable this plan.

SQL Optimizer for Oracle 9.3.1 User Guide

14
Use SQL Optimizer
 Deploy Description

l Fixed—Select whether to deploy this plan as fixed or non-fixed.

l Not Auto-Purged—Select whether to auto-purge when it is not used.

Plan name Enter a name for the plan.

Description Enter a description for this plan.

3. Click to deploy the plan to Manage Plans.

SQL Optimizer for Oracle 9.3.1 User Guide

15
Use SQL Optimizer
Tutorial: Generate Indexes in Optimize SQL
SQL Optimizer identifies columns to use as index alternatives for a SQL statement after it analyzes SQL syntax,
relationships between tables, and selectivity of data. SQL Optimizer then combines identified alternatives into
index sets.

To generate an index alternative

1. Select the Optimize SQL tab in the main menu.
2. Enter a SQL statement in the Alternative Details pane.

3. Click . The Select Connection and Schema window displays.

4. Select a connection and schema to use.

5. Select Index Details in the SQL Information pane to view index generation information.
6. Select an index set to view index name, details and generation script in the Alternative Details pane.

7. To test an index, select it in the Alternatives pane and click .

Note: The Test Run function allows you to test an index set SQL Optimizer generated. It physically
creates the indexes on the database, runs the SQL statement, retrieves execution statistics, and
drops the indexes. Since this process physically creates indexes on your database, it may impact
performance of other SQL statements.

SQL Optimizer for Oracle 9.3.1 User Guide

16
Use SQL Optimizer
Tutorial: Best Practices
You can use Best Practices to analyze your SQL statement and database to recommend common techniques
for improving database performance. Since the recommendations can also affect performance of other
statements in your database, you should review and test the recommendations before implementing them.
When evaluating the recommendations, take into account that database performance is affected by the
following:
l System resources (CPU, I/O, memory, database architecture, and more)
l Data distribution
l System architecture
l SQL execution plans

l User's usage behavior

Note: The Best Practices function is only available in SQL Rewrite mode in Optimize SQL.

To view best practices

1. Select the Optimize SQL tab in the main window.

2. Click .

Tip: To display the best practices tab, click , select Optimize SQL | Best Practices | General,
and select the Display Best Practices tab in SQL Rewrite mode checkbox.

3. Enter a SQL statement into the Alternative Details pane.

4. Click . The Select Connection and Schema window displays.

5. Select a connection and schema to use.

6. Review the recommendations provided.

SQL Optimizer for Oracle 9.3.1 User Guide

17
Use SQL Optimizer
Tutorial: Deploy Outlines
The Deploy Outline function in Optimize SQL improves SQL statement performance without changing your
original source code. Using Optimize SQL, you can generate SQL statements that are semantically equivalent to
your original SQL statement with alternative execution plans. Once you identify the best alternative for your
database environment, you can deploy it as a stored outline to use with your original statement.

To deploy an outline
1. Select the Optimize SQL tab in the main window.

2. Click the arrow beside and select New SQL Rewrite Session.

3. Enter your original SQL statement in the Alternative Details pane and click . The Select Connection
and Schema window displays.
4. Select a connection and schema to use.
5. Right-click the alternative you want to deploy as an outline in the Alternatives pane and select Deploy
Outline. The Deploy Outline window displays.
6. Review the following for additional information:

Outline name Enter a name for the stored outline

Category Click and select a previously created category or enter a new category
name.
Notes:
l The default category name is SQL_OPTIMIZIER.
l You can add the outline to a disabled category until you test the
SQL statement with and without using the outline.

7. Click .

Note: You can use the Outline Management feature in Manage Plans to enable and disable categories or to
move outlines to different categories.

SQL Optimizer for Oracle 9.3.1 User Guide

18
Use SQL Optimizer
Tutorial: Batch Optimize SQL
This topic focuses on information that may be unfamiliar to you. It does not include all step and field
descriptions.

To batch optimize SQL

1. Select the Batch Optimize tab in the main window.
2. Click Add Code to Optimize in the Batch Job List pane and select All Types. The Add Batch Optimize
Jobs window displays.
3. Review the following for additional information:

Connection Description
Page

Connection Click to select a previously created database connection.

Tips:

l Click to open the Connection Manager to create a new

connection.
l You can select an alternative connection for executing the SQL
statement alternatives Batch Optimize generates.

Database Description
Objects Page

Database Select a schema, database object type, or individual database object, and then
objects
click to add the object.
Tip:

l Click to browse for database objects.

l Your database privileges determine if you can scan all selected
database objects.

Execute using Click and select an alternative schema for executing the SQL statement
schema alternatives.

Source Code Description

Page

Source code Select Text/Binary files, Oracle SQL *Plus Script, or COBOL programming
type source code to indicate the source code type for the file or directory you want
to scan.

Add by file
Click and browse to the files you want to add.

Add by
Click and browse to the directories you want to add.
directory
Note: Select the Include Sub-directory checkbox to scan sub-directories.

Scan using Click and select a schema to scan.

schema

SQL Optimizer for Oracle 9.3.1 User Guide

19
Use SQL Optimizer
 Execute using Click and select an alternative schema for executing the SQL statement
schema alternatives.

SQL Text Page Description

SQL text Enter SQL statement text.

Scan using Click and select a schema to scan.

schema

Execute using Click and select an alternative schema for executing the SQL statement
schema alternatives.

Scan SQL Page Description

Group Select the Scanner group that contains the SQL statements you want to scan.

Scan using Click and select a schema to scan.

schema

Execute using Click and select an alternative schema for executing the SQL statement
schema alternatives.

Inspect SGA Description

Page

Group Select the Inspector group that contains the SQL statements you want to scan.

Scan using Click and select a schema to scan.

schema

Execute using Click and select an alternative schema for executing the SQL statement
schema alternatives.

Note: Batch Optimize helps you manage jobs by organizing them into batches. Use the Batch
Info page to create a new batch or to add the current job to an existing batch.

4. Click Finish to start batch optimization.

Batch Optimize scans the job you created, classifies and optimizes the statements, and executes the
SQL statement alternatives it generates.
Notes:

l Scanning starts automatically if you select the Automatically start extracting SQL when job
is added checkbox in the Batch Optimize options page. Batch Optimize selects this checkbox
by default.
l Batch Optimize selects the SQL statement to optimize based on the classification types
selected in the Batch Optimize options page. Batch Optimize selects Problematic and
Complex SQL classification types by default.
l Batch Optimize executes SQL alternatives it generates based on the statement types
selected in the Batch Optimize options page. Batch Optimize selects SELECT
statements by default.
5. Select Batch List in the Batch Job List pane to view information about the jobs you created.
The Batch List pane sorts information about your jobs by batches. Additional information displays in the
Jobs Improved pane.
6. Select a batch from the batch list node to see details for the batch in the Job List pane.

SQL Optimizer for Oracle 9.3.1 User Guide

20
Use SQL Optimizer
 The Job List pane displays the type of job, job status, and time of improvement for each job in the batch.
Additional information displays in the SQL Classification and Cost and Elapsed Time Comparison panes
for a selected job.

Tip: Select a job in the Job List pane and click to generate a replacement script with the
optimized SQL statement.

7. Select a job from the batch node to see details of the job.
The SQL List pane displays SQL classification information for the SQL statements in the job you select.
The Original SQL Text and Best Alternative SQL Text panes allow you to compare your original SQL
statement with the best alternative Batch Optimize generated.

Tip: Select a SQL statement in the SQL List pane and click to send the statement to Optimize
SQL and view all SQL alternatives.

SQL Optimizer for Oracle 9.3.1 User Guide

21
Use SQL Optimizer
Tutorial: Scan SQL
Scan SQL helps you identify problematic SQL statements in your database environment by automatically
extracting statements embedded in database objects, stored in application source code and binary files, or
captured from Oracle's System Global Area. Scan SQL retrieves and analyzes execution plans for the extracted
statements and classifies them according to complexity. You can then send statements that Scan SQL classifies
as problematic or complex to Optimize SQL.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field
descriptions.

To scan SQL
1. Select the Scan SQL tab in the main window.

2. Click to select a previously created group or click to create a new group for your scan job.

Note: Scan SQL helps you manage scan jobs by organizing them into groups.

3. Click . The Add Scanner Jobs window displays.

4. Review the following for additional information:

Page Description

Database Objects Page

Database Select a schema, database object type, or individual database object, and then
objects
click to add the object.

Tip: Click to browse for database objects.

Source Code Page

Source code Select Text/Binary files, Oracle SQL *Plus Script, or COBOL programming
type source code to indicate the source code type for the file or directory you want
to scan.

Add by file
Click and browse to the files you want to add.

Add by
Click and browse to the directories you want to add.
directory
Note: Select the Include Sub-directory checkbox to scan sub-directories.

Scan using Click and select a schema to scan.

schema

Inspect SGA Page

Group Select the Inspector group that contains the SQL statements you want to scan.

Scan using Click and select a schema to scan.

schema

5. Click Finish to start scanning.

6. Select a scan job from the Job List pane to view additional information.

SQL Optimizer for Oracle 9.3.1 User Guide

22
Use SQL Optimizer
 Details displayed in the Job List pane include the number of SQL statements found and the classification
for each statement.

Tip: Click and select a different group to display scan jobs from a different group.

7. Select a SQL statement in the SQL List pane to view additional information for the selected statement in
the SQL Text and Execution Plan panes.

Tip: Click in the SQL Text pane to send the selected

statement to Optimize SQL.

SQL Optimizer for Oracle 9.3.1 User Guide

23
Use SQL Optimizer
Tutorial: Inspect SGA
Inspect SGA retrieves executed SQL statements from Oracle's System Global Area or currently running SQL
statements from Oracle's open cursor. Once you retrieve the statements, Inspect SGA displays the statements
and their run time statistics so you can identify resource intensive statements in your database environment.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field
descriptions.

To retrieve a previously executed SQL statement

1. Select the Inspect SGA tab in the main window.
Note: To retrieve previously executed SQL statements, you must have privileges to view SYS.V_
$SQLAREA and either SYS.V_$SQLTEXT_WITH_NEWLINES or SYS.V_$SQLTEXT.

2. Click to select a group or click to create a new group in the Group list.

3. Click . The Add Inspect SGA Job wizard displays.

4. Complete the following fields in the wizard:

General Information Description

Page

Job type Select the Executed SQL from SQL Area option.

Collecting Criteria Description

Page

Collecting Criteria Select the Top n records option and enter the number of records to
display.

First by Click and select the statistic to use to extract SQL statements if you are
not displaying all records.
Note: A large SGA increases processing time.

Collection Time Description

Page

Collection Time Select the Start collecting when you click the Inspect button option.

5. Click to retrieve the SQL statements and run time statistics.

6. Select a statement that requires optimization in the SQL Statistics pane and click to send to
Batch Optimize SQL or click the arrow beside and
select an option.
Tip: You can add an Inspect SGA job in Batch Optimize to optimize all the SQL statements in the
collection.

SQL Optimizer for Oracle 9.3.1 User Guide

24
Use SQL Optimizer
Tutorial: Analyze Impact
Use Analyze Impact to evaluate the impact that a change can have on a group of SQL statements or a SQL
workload. The types of changes Analyze Impact will allow you to evaluate include: the addition of indexes and
changes to database parameters. You can also run a comparison of two different databases that run the same
application.
You can collect the SQL workload from one of several sources: source code, an Oracle Automatic Workload
Repository (AWR), or the Oracle System Global Area (SGA).

To perform an impact analysis

1. Select Analyze Impact.
2. Select the type of change you want to analyze. For this tutorial, select Additional Indexes.

3. Select the database connection and schema to use.

4. Click to add a new index.

5. Enter a new index name or use the default.

6. In the Select Index Columns and Options pane, select the table containing the columns you want to
index. The table's columns display in the lower pane. Select a column and click to move it to the
Indexed Columns list.

7. Click to create a functional index.

8. Specify the remaining index criteria for the selected index. Review the following for additional
information:

Index Type Select an index type.

Advanced Click to select from additional options for the selected index.
Options

Compress Select to add key compression. If you select Compress Key, you must also
Key include the number of columns to compress in the Columns field.

9. Click to start collecting the SQL to evaluate.

10. Select the source from which you want to collect SQL. For this tutorial, select SGA.
11. On the SQL collection page, click the edit link beside the parsing schema name. All the available
parsing schemas are displayed in the graph. Select one of the parsing schemas by clicking it's bar in the
graph. Click the bar a second time to deselect it.
12. Click the edit link beside Module. Notice that all available modules (for the selected schema) display in
the graph. You can filter the SQL to collect by selecting a particular module or you can select all to collect
SQL from all modules.
Note: Use the mouse pointer to hover over a bar in the graph to display a descriptive tooltip
containing SQL workload details to help you select a workload.

13. Click the edit link beside Action. Notice that all available actions (for the selected schema) display in the
graph. You can filter the SQL to collect by selecting a particular action or you can select all to collect SQL
from all actions.

SQL Optimizer for Oracle 9.3.1 User Guide

25
Use SQL Optimizer
 14. When you are finished selecting a SQL workload, click to start the
collection process.
15. SQL Optimizer collects the specified SQL statements. The SQL page opens displaying the list of SQL
statements collected.
16. In the SQL Workload pane, select a SQL statement to display the statement text and the parsed
execution plan.
17. You can remove a SQL statement from the workload by deselecting (clearing) the checkbox in the
Include column.

18. When you are satisfied with the SQL workload, click to start the
analysis process.
19. When the analysis process is finished, one of the following will display, depending on the
analysis results:

l If an impact was found, the Impacts page displays. See "Review Impact Analysis Results" in the
online help for more information.
l If the analysis determines that non of the specified SQL were impacted, the log page displays
and reports "No impacted SQL found."

Tip: Use and to navigate back and forth between the pages of a tab.

SQL Optimizer for Oracle 9.3.1 User Guide

26
Use SQL Optimizer
Tutorial: Send Indexes to Analyze Impact
You can analyze the impact of new indexes on your SQL statement's execution plans before you physically
create the indexes on your database. You can create indexes in Optimize SQL or Optimize Indexes and then
send the index or indexes to Analyze Impact to evaluate the impact on your SQL workload.
Use the Optimize SQL module to generate index alternatives for a single SQL statement. Or use the Optimize
Indexes module to generate index alternatives for a SQL workload or group of SQL statements.

To send indexes to Analyze Impact from Optimize SQL

1. Generate index alternatives using the Index Generation feature in Optimize SQL. See Tutorial: Generate
Indexes in Optimize SQL on page 16 for more information.
2. Select the virtual index alternative you want to use for the analysis in the Alternatives pane in
Optimize SQL.

3. Click in Optimize SQL. A new Analyze Impact session opens.

4. The Indexing Change Details page is populated with the new index that you sent from the Optimize SQL
session. Use this page to modify the index, if necessary. See "Analyze the Impact of Additional Indexes"
in the online help for more information.

5. When you have finished modifying the index, click to start collecting the SQL to
evaluate. See "Collect SQL and Run an Impact Analysis" in the online help for more information.

To send indexes to Analyze Impact from Optimize Indexes

1. Generate index alternatives in Optimize Indexes. See "About Optimize Indexes" in the online help for
more information.
2. In the Optimize Indexes session, select the Results tab.
3. On the Results page, in the Results Summary pane, click Send to Analyze Impact. A new Analyze
Impact session opens.
4. The Indexing Change Details page is populated with the new indexes that you sent from the Optimize
Indexes session. Use this page to modify the index or indexes, if necessary. See "Analyze the Impact of
Additional Indexes" in the online help for more information.

5. When you have finished modifying the indexes, click to start collecting the SQL to
evaluate. See "Collect SQL and Run an Impact Analysis" in the online help for more information.

SQL Optimizer for Oracle 9.3.1 User Guide

27
Use SQL Optimizer
Tutorial: Manage Outlines
Outline Management displays stored outlines deployed using SQL Rewrite mode in Optimize SQL.

To manage outlines
1. Select the Manage Plans tab in the main window.
Tip: Select the Show Manage Plans checkbox on the Manage Plans options page to display the
Manage Plans tab in the main window.

2. Click Manage Plans. The Create a New Manage Plans Session window displays.
3. Select a connection to use.

4. Select the Outlines Management tab.

5. Select a category in the Category/Outline pane.
You can delete or rename the selected category.
6. Select a stored outline from the category node.
You can move, rename, or reset the Used Flag on the selected stored outline.

SQL Optimizer for Oracle 9.3.1 User Guide

28
Use SQL Optimizer
Optimize Indexes for SGA Workload
If you selected the SGA as a workload source, define the new workload and specify the search process criteria.

Define a new SGA Workload

1. If you have already created a new session, skip to 3. Otherwise, select Optimize Indexes.

2. Click .

3. Enter a SQL workload name and select a database connection from which to collect SQL.
4. Specify SQL filter criteria. To modify filter criteria, click the Edit link beside each filter. The available
options display as bars in the graph. Click an option (bar) in the graph to select it. The selected option
then displays in the filter criterion field in the top pane. Review the following for additional information:

SQL filter criteria pane Description

Collect SQL for parsing schema You must select a parsing schema before collecting SQL.

Module, Action Use these fields to filter the available SQL statements.
Default = All

Notes:
l Use the cursor to hover over an option (bar) in the graph to display option details.
l Click List statistics data to display the options for the currently selected filter criterion in a
grid format.
l You can display additional data in the graph (or in the grid). To do this, select/deselect data
options from the group of options at the bottom of the page.

5. To begin collecting SQL, click .

6. Optimize Indexes immediately asks you to choose a second database connection. Select a database
connection to use to retrieve execution plans and evaluate index alternatives.

Note: This step allows you to select an alternate database (other than the one used to collect SQL)
to run the index optimization process. In other words, you can collect your SQL from one database,
but run the optimize indexes process on a second database. The second database must have the
same application environment and data volume statistics as the first.

7. The Search Process page opens. Before specifying search process criteria, you can review and modify
your SQL workload.
l Review collected SQL. This step is optional. You can modify the SQL workload if you want.
l Specify search process criteria.

(Optional) Review collected SQL

1. To review and modify your SQL workload, click Review/adjust workload SQL. Review the following for
additional information:

SQL Optimizer for Oracle 9.3.1 User Guide

29
Use SQL Optimizer
 Pane / Button Description

SQL Tuning Set Pane / This pane displays the list of SQL in the workload.
SQL Workload Pane Include—To include a SQL statement in the optimize indexes
process, select the checkbox in the Include column.

SQL Text Pane This pane displays the SQL text of the selected SQL statement.

Parsed Execution Plan Pane This pane displays the execution plan of the selected SQL
statement.
SQL Statistics—Select this tab to display statistics of the selected
statement.

Select to send your SQL to Optimize SQL or Batch Optimize. Click

the arrow for more options.

Notes:

l Click to return to the SQL Collection page. (On the SQL Collection page, click
Review SQL to go back to the SQL Review page.)
l To collect a new SQL workload, return to the SQL Collection page and click
Collect SQL again.
Caution: If you click Collect SQL again, your current SQL workload will be lost, as
well as any analysis data already gathered for the current session.

2. After reviewing the collected SQL, select the Search Process tab to return to the Search Process page.

Specify search process criteria

1. With the Search Process page open, locate the Search Process Control pane. Use these options to
specify criteria for SQL Optimizer to use to find new indexes. Review the following for additional
information:

Search Process Control Pane Description

Primary goal (minimize) Select a primary goal. SQL Optimizer evaluates indexes to
optimize the workload based on this primary goal.

Scheduled to run on Click the link to schedule a time to run the optimization
process on this workload. Select a start time, end time,
duration, and interval.

Stop condition(s) Select criteria to determine when to end the optimization

process. You can select one or more conditions. The default
setting is When no further improvement found in 1:00:00.
Note: If you do not specify a Stop condition or an End
date, the optimization process will run indefinitely.

SQL Optimizer for Oracle 9.3.1 User Guide

30
Use SQL Optimizer
 Search Process Control Pane Description

Advanced options Click this link to select the number of columns in a

composite index, the maximum number of indexes to
recommend, and an index-type time distribution.
Recommend function-based indexes—Select this checkbox
if you want SQL Optimizer to include function-based
indexes.
Search time distribution between B-Tree and Bitmap
indexes—Select a pre-defined or user-defined time
distribution. Determines the percentage of time spent
searching for B-Tree vs Bitmap index types.

2. Click Start to begin searching for new indexes to improve the workload performance.

Review Optimize Indexes Results

You can review the results or the progress of your search for new indexes in Optimize Indexes.

To View Optimize Indexes Search Results

1. If you already have a session open, skip to 3. Otherwise, select Optimize Indexes.
2. Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
Note: You can view a summary of the results, or the progress of the search if it is ongoing, in the
bottom pane of the Open SQL Workload window.

3. Select the Search Process tab to review details related to the search process. Each line on the graph
indicates a new indexing recommendation.
Notes:
l Use your cursor to hover over a line on the graph to display a summary of the
recommendation’s improvements (in a tooltip). Then click View details (in the tooltip) to open
the Results page for the selected recommendation.
l Select All in the Display Range box, to display all index recommendations in the graph.
l Click List all results to display all index recommendations and their cost improvement
values in a grid format. Click a recommendation in the list to open the results page for the
selected recommendation.
4. Select the Results tab to view the final recommendation (or the last recommendation if the search
process is ongoing).
Review the following for additional information:

SQL Optimizer for Oracle 9.3.1 User Guide

31
Use SQL Optimizer
Pane / Option / Button Description

n Recommended Indexes Displays a list of the indexes for the recommendation.

Index Name—Click an Index name to see the index details,
including a list of existing indexes which you can use to
check for duplication.
Tip: Select (highlight) an index in the Recommended
Indexes list to highlight the SQL statements in the
Impacted SQL list that are impacted by that
recommended index.

Results Summary (left side)—Displays a summary of workload improvement

results.

Note: If the links at the bottom of the Results Summary

pane are not in view, click and drag the pane's bottom
edge down to resize.
(right side)—Displays the workload timeslots.
Use the cursor to hover over a timeslot to display a summary
of the improvement to that timeslot’s SQL.
Note: Timeslots display only for workloads collected
from snapshots.

View Displays one of two different evaluations for the Workload:

l Plan Statistics view—Displays the estimated
improvements based on changes in plan statistics
and weighed by the number of executions for each
SQL per timeslot.
l Projected Actual Statistics view—Displays the
estimated improvements calculated using actual
statistics and changes in plan statistics.

Publish Report Click to view the Optimize Indexes Workload Report which
you can customize and then export, print, or email.

Send to Analyze Impact Click to send the results to the Analyze Impact module
where you can evaluate the impact of the new indexes on
other SQL.

Chart Options Click this link to select which charts/statistics to display.

Impacted SQL SQL Name—Click a SQL name to review the SQL statement
and to compare the recommended execution plan to the
original execution plan.

SQL Optimizer for Oracle 9.3.1 User Guide

32
Use SQL Optimizer
 Pane / Option / Button Description

Tip: Select (highlight) a SQL statement in the Impacted

SQL list to highlight the indexes in the Recommended
Indexes list that contributed to the performance
improvement of that statement.

Click to create a script to generate the new indexes

recommended by Optimize Indexes.

Notes:
l To save the contents of a grid (list) as a file, right-click the grid and select Save As.
l An open database connection is not required when opening a completed session, reviewing results,
or creating and publishing (printing) a report.

Tip: Use and to navigate back and forth between the pages of a tab.

SQL Optimizer for Oracle 9.3.1 User Guide

33
Use SQL Optimizer
 About Us

We are more than just a name

We are on a quest to make your information technology work harder for you. That is why we build community-
driven software solutions that help you spend less time on IT administration and more time on business
innovation. We help you modernize your data center, get you to the cloud quicker and provide the expertise,
security and accessibility you need to grow your data-driven business. Combined with Quest’s invitation to the
global community to be a part of its innovation, and our firm commitment to ensuring customer satisfaction, we
continue to deliver solutions that have a real impact on our customers today and leave a legacy we are proud of.
We are challenging the status quo by transforming into a new software company. And as your partner, we work
tirelessly to make sure your information technology is designed for you and by you. This is our mission, and we
are in this together. Welcome to a new Quest. You are invited to Join the Innovation.

Our brand, our vision. Together.

Our logo reflects our story: innovation, community and support. An important part of this story begins with the
letter Q. It is a perfect circle, representing our commitment to technological precision and strength. The space in
the Q itself symbolizes our need to add the missing piece—you—to the community, to the new Quest.

Contact Quest
For sales or other inquiries, visit [Link]/contact.

Technical Support Resources

Technical support is available to Quest customers with a valid maintenance contract and customers who have
trial versions. You can access the Quest Support Portal at [Link]
The Support Portal provides self-help tools you can use to solve problems quickly and independently, 24 hours
a day, 365 days a year. The Support Portal enables you to:
l Submit and manage a Service Request
l View Knowledge Base articles
l Sign up for product notifications
l Download software and technical documentation
l View how-to-videos
l Engage in community discussions
l Chat with support engineers online
l View services to assist you with your product

SQL Optimizer for Oracle 9.3.1 User Guide

34
About Us
SQL Optimizer Community
You can also find help and additional information at the SQL Optimizer community at:
[Link]

SQL Optimizer for Oracle 9.3.1 User Guide

35
About Us