Quest® SQL Optimizer for Oracle

Installation Guide
Version 7.2
© 2007 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.

If you have any questions regarding your potential use of this material, please contact:

Quest Software World Headquarters

LEGAL Dept
5 Polaris Way
Aliso Viejo, CA 92656

Web site: www.quest.com

Email: legal@quest.com

Trademarks
Quest, Quest Software, the Quest Software logo, Aelita, Benchmark Factory, Big Brother, DataFactory,
DeployDirector, ERDisk, Fastlane, Final, Foglight, Funnel Web, I/Watch, Imceda, InLook, InTrust, IT Dad, JClass,
JProbe, LeccoTech, LiveReorg, NBSpool, NetBase, PerformaSure, PL/Vision, Quest Central, RAPS, SharePlex,
Sitraka, SmartAlarm, Speed Change Manager, Speed Coefficient, Spotlight, SQL Firewall, SQL Impact, SQL
LiteSpeed, SQL Navigator, SQLab, SQLab Tuner, SQLab Xpert, SQLGuardian, SQLProtector, SQL Watch, Stat,
Stat!, Toad, T.O.A.D., Tag and Follow, Vintela, Virtual DBA, and XRT are trademarks and registered trademarks of
Quest Software, Inc. Other trademarks and registered trademarks used in this guide are the property of their
respective owners.

Third Party Contributions

This product includes software developed by RSA encryption logic from http://www.crypto-central.com/,
TDCP_Rijndael from http://www.scramdisk.clara.net/, and DPAPI from http://msdn2.microsoft.com/en-us/library/
ms995355.aspx.

Installation Guide
August 2007
Version 7.2
Table of Contents

Preface ..................................................................................................................................................................v
About This Guide..............................................................................................................................................................vi
Additional Information ......................................................................................................................................................vi
Online Help..............................................................................................................................................................vi
Release Notes .........................................................................................................................................................vi
About Quest Software, Inc. ............................................................................................................................................. vii
Accessibility ............................................................................................................................................................ vii
Contact Quest Software ................................................................................................................................................. vii
Contact Quest Support .................................................................................................................................................. viii

Installation ............................................................................................................................................................1
Installation Requirements................................................................................................................................................. 2
.NET Framework ..................................................................................................................................................... 2
Privileges Required for Using Quest SQL Optimizer for Oracle .............................................................................. 2
Hardware Requirements.......................................................................................................................................... 6
Software Requirements ........................................................................................................................................... 6
Install Quest SQL Optimizer for Oracle ........................................................................................................................... 7
Upgrade Quest SQL Optimizer for Oracle ....................................................................................................................... 8
Uninstall Quest SQL Optimizer for Oracle ....................................................................................................................... 8
SQL Optimizer Version Selector ..................................................................................................................................... 9

Configuration......................................................................................................................................................11
Access Quest SQL Optimizer for Oracle........................................................................................................................ 12
Review Option Settings ................................................................................................................................................. 12
Copy Impact Analyzer Files .......................................................................................................................................... 12

Index....................................................................................................................................................................15
iv Quest SQL Optimizer for Oracle
Installation Guide
Preface

Contents

About This Guide

Additional Information
About Quest Software, Inc.
Contact Quest Software
Contact Quest Support
vi Quest SQL Optimizer for Oracle
Installation Guide

About This Guide

This guide describes how to install Quest SQL Optimizer for Oracle.

Additional Information
In addition to this guide, there are a number of options available for quickly finding the
information you need.

Online Help
There are several ways to access online help topics.

To... Do This...

Display the table of contents for help Select Help | Help. If required, in the
topics Help window, select the Contents tab.

Search for a specific help topic Select Help | Help. If required, in the
Help window, select the Search tab.

Display the index for help topics Select Help | Help. If required, in the
Help window, select the Index tab.

Display help for a specific window or In the window or dialog, do one of the
dialog following:
• Click Help
• Press F1
Note Not all windows and dialogs are linked
to specific help topics.

Release Notes
To access the release notes, select Start | All Programs | Quest Software | Quest SQL
Optimizer for Oracle | Documentation | Release Notes.
 Preface

About Quest Software, Inc.

Quest Software, Inc. delivers innovative products that help organizations get more
performance and productivity from their applications, databases and infrastructure.
Through a deep expertise in IT operations and a continued focus on what works best,
Quest helps more than 18,000 customers worldwide meet higher expectations for
enterprise IT. Quest Software lets organizations deliver, manage and control complex
database environments through award-winning products for Oracle, SQL Server, IBM
DB2, Sybase, and MySQL. Quest Software can be found in offices around the globe and
at www.quest.com.

Accessibility
Quest Software is committed to providing products and solutions that are accessible to
all people regardless of their capabilities. Section 508 compliance details are available
by product in the Voluntary Product Accessibility Templates (VPATs) published on our
web site at: http://www.quest.com/public-sector/section508.aspx.

Contact Quest Software

Email info@quest.com
Mail Quest Software, Inc.
World Headquarters
5 Polaris Way
Aliso Viejo, CA 92656
USA
Web site www.quest.com

Please refer to our web site for regional and international office information.
viii Quest SQL Optimizer for Oracle
Installation Guide

Contact Quest Support

Quest Support is available to customers who have purchased a commercial version and
have a valid maintenance contract or who have a trial version of Quest software. Quest
Support provides around the clock coverage with SupportLink, our web self-service.
Visit SupportLink at: http://support.quest.com
With SupportLink, you can do the following:
• Quickly find thousands of solutions (Knowledgebase articles/documents).
• Download patches and upgrades.
• Seek help from a Support engineer.
• Log and update your case, and check its status.

View the Global Support Guide for a detailed explanation of support programs, online
services, contact information, and policy and procedures. The guide is available at:
http://support.quest.com/pdfs/Global%20Support%20Guide.pdf
 1
Installation

Contents

Installation Requirements
Install Quest SQL Optimizer for Oracle
Upgrade Quest SQL Optimizer for Oracle
Uninstall Quest SQL Optimizer for Oracle
SQL Optimizer Version Selector
2 Quest SQL Optimizer for Oracle
Installation Guide

Installation Requirements
Before beginning installation, review this section to ensure required privileges are set
and system requirements are met.

.NET Framework
Before you can install Quest SQL Optimizer for Oracle you must have .NET
Framework 2.0. or later installed. Both the installation program and Quest SQL
Optimizer for Oracle require .NET Framework to run. You can download the
installation program, dotnetfx.exe, from the following website:
http://msdn2.microsoft.com/en-us/netframework/aa731542.aspx

Privileges Required for Using Quest SQL Optimizer for Oracle

Database Objects created

No database objects are created in Oracle 8i or later for users that have the CREATE
TEMP TABLE privilege.
For users of Oracle version 8 or earlier and users without the CREATE TEMP TABLE
privilege, a permanent table is created which is used to retrieve the execution plan for
SQL statements. This table is named QUEST_SL_EXPLAINn, where n is a number
starting at 1 which will make the table name unique in the database. The first time you
logon with a new user name, you are prompted to create this table with a dialog that
shows you the CREATE TABLE statement before it is executed.

Privileges / Grants created

The following lists the functionality in Quest SQL Optimizer for Oracle that requires
specific Oracle database privileges. If you do not have one of these privileges, you can
still use the functionality in the other features of the program.
 Chapter 1 3
Installation

Module Functionality Privilege

SGA Inspector SQL to Collect: Access is needed to these

Executed SQL system views:
from SQL Area SYS.V_$SQLAREA and
SYS.V_$SQLTEXT_WITH_N
EWLINES (or
SYS.V_$SQLTEXT depending
on your
version of Oracle)
In Oracle 9 or later,
V$_SQL_PLAN

SGA Inspector SQL to Collect: Access is needed to these

Currently system views:
executing SQL SYS.V_$OPEN_CURSOR,
SYS.V_$SESSION,
SYS.V_$SQLAREA and
SYS.V_$SQLTEXT_WITH_N
EWLINES
(or SYS.V_$SQLTEXT
depending on your version of
Oracle)
In Oracle 9 or later,
V$_SQL_PLAN

SGA Inspector Flush Oracle ALTER SYSTEM privilege is

shared pool needed.

SGA Inspector Execution Plan Oracle 9 or above is required.

Information Access is needed to this system
view: V_$SQL_Plan
4 Quest SQL Optimizer for Oracle
Installation Guide

Module Functionality Privilege

Tuning Lab, Retrieve time The TIMED_STATISTICS

Global related statistics parameter in the INIT.ORA file
Indexing, Batch must be set to TRUE.
Optimizer

Index Expert Generate Virtual Oracle 8i or above is required..

and Global Indexes
Indexing

All Modules If the Oracle init parameter

O7_DICTIONARY_ACCESSI
BILITY (Oracle 8 or above) is
set to false, objects under SYS
are not accessible, even for a
user with the SELECT ANY
TABLE privilege granted. In
this case, the SELECT ANY
DICTIONARY privilege or the
SELECT_CATALOG_ROLE
role is needed to access the
objects under SYS.

Tuning Lab Altering session Access is needed to this system

parameters for view: SYS.V_$PARAMETER
executing SQL
 Chapter 1 5
Installation

Module Functionality Privilege

Outline Creating and Oracle 8i or above is required.

Manager and managing outlines DBA role is needed.
Deploy Outlines
CREATE ANY OUTLINE and
DROP ANY OUTLINE
privileges are needed.
Access to this package is
needed: SYS.OUTLN_PKG
Access is needed to these
system views:
SYS.USER_OUTLINES and
SYS.USER_OUTLINE_HINTS
or
SYS.DBA_OUTLINES and
SYS.DBA_OUTLINE_HINTS
6 Quest SQL Optimizer for Oracle
Installation Guide

Hardware Requirements

Component Requirement

Processor 233 MHZ minimum

Disk Space 90 MB of disk space for installation

Note The disk space required may vary based on options selected
for installation.

Memory 512 MB of RAM minimum; 768 MB of RAM recommended

Note The memory required may vary based on the following:
• Applications that are running on your system.
• Size and complexity of the database.
• Complexity of SQL statement
• Amount of database activity.
• Number of concurrent users accessing the database.

Monitor SVGA with a minimum resolution of 800x600, 1024x768

recommended

Software Requirements

Component Requirement

Operating System Microsoft® Windows® 2000, 2003, XP Professional

.NET Framework Microsoft .NET Framework 2.0

Browser Microsoft Internet Explorer 6.0 or later

PDF Viewer Adobe® Acrobat® Reader 5.0 or later (for viewing the
Installation Guide)
 Chapter 1 7
Installation

Component Requirement

Database Client One of the following connection is needed to the database

• SQL*NET
• NET8
• 10g Client
• Oracle 10g Instant Client
• Oracle 10g Easy Connect

Database Server Oracle® 8.1.7, 9i, 10g

Install Quest SQL Optimizer for Oracle

To install Quest SQL Optimizer for Oracle
1 Run the Quest SQL Optimizer for Oracle installation program from the CD or the
download file.
2 In the Welcome screen, click Next.
3 In the License Agreement window, review the license agreement.
4 Accept the license and click Next.
5 In the Select the Products to Install window, select Quest SQL Optimizer for
Oracle. Click Next.
6 In the Summary and Confirmation window, click Next.
7 When the installation completes, click Finish.

The production version, the trial version, and the beta version of Quest SQL Optimizer
are installed independent of each other. Therefore, you can install or uninstall one
without effect the other.

Note After installing Quest SQL Optimizer for Oracle all integration points into a previously
installed version of SQL Tuning for Oracle (6.1.2 or earlier) or SQL Server (6.2.1 or earlier)
are re-routed to version 7.2. In order to switch between different versions, use the SQL
Optimizer Version Selector.
8 Quest SQL Optimizer for Oracle
Installation Guide

Upgrade Quest SQL Optimizer for Oracle

Quest SQL Optimizer for Oracle version 7.2 requires a full installation if you are
running SQL Tuning for Oracle 6.1.2 or earlier.
If have Quest SQL Optimizer for Oracle version 7.0 currently installed, when you run
the installation program for Quest SQL Optimizer for Oracle version 7.2, the program
will only install the upgraded files.

Uninstall Quest SQL Optimizer for Oracle

To uninstall SQL Optimizer using the Quest Installer
1 Select Start | All Programs | Quest Software | Quest Installer |Quest Installer.
2 On the Quest Installer Welcome page, select Uninstall a Product, then click
Next.
3 Select Quest SQL Optimizer for Oracle, then click Next.
4 Confirm that you want to remove SQL Optimizer, then click Uninstall.
5 After the Quest Installer has completed uninstalling SQL Tuning, click Finish.

To uninstall SQL Tuning (installed from Quest Central)

Note To uninstall Quest SQL Tuning for Oracle when installed with Quest Central, you must have
the Quest Installer installed.

1 Select Start | All Programs | Quest Software | Quest Installer |Quest Installer.
2 On the Quest Installer Welcome page, select Uninstall a Product, then click
Next.
3 Select SQL Tuning for Oracle, then click Next.
4 Confirm that you want to remove SQL Tuning, then click Uninstall.
5 After the Quest Installer has completed uninstalling SQL Tuning, click Finish.

To uninstall SQL Tuning/SQL Optimizer (installed from Toad or stand-alone)

• Use the Control Panel | Add or Remove Programs.
 Chapter 1 9
Installation

To uninstall Quest SQL Optimizer verison 3.8.n

• Use the Control Panel | Add or Remove Programs.

SQL Optimizer Version Selector

You can only call one version of the Quest's SQL optimization program from other
Quest products such as Toad, SQL Navigator, Spotlight or Performance Analysis. But
you can have more than one version installed on your PC. The latest version (7.2) is
called Quest SQL Optimizer for Oracle and the previous versions were bundled as SQL
Tuning for Oracle in Toad, SQL Navigator, and Quest Central.
The SQL Optimizer Version Selector enables you to register which version of Quest's
SQL optimization program will be launched when a SQL statement, a group of
statements, or a block of text is sent.
To select which version will be launch
1 Select Start | All Programs | Quest Software | Quest SQL Optimizer for
Oracle | SQL Optimizer Version Selector.
2 Select the version.
3 Click the Set Active button.
10 Quest SQL Optimizer for Oracle
Installation Guide
 2
Configuration

Contents

Access Quest SQL Optimizer for Oracle

Review Options Settings
Copy Impact Analyzer Files
12 Quest SQL Optimizer for Oracle
Installation Guide

Access Quest SQL Optimizer for Oracle

To run Quest SQL Optimizer for Oracle, select Start | All Programs | Quest Software |
Quest SQL Optimizer for Oracle | Quest SQL Optimzer 7.2 for Oracle.

Review Option Settings

Starting with version 7.0, Quest SQL Optimizer for Oracle uses a new Windows registry
location and the whole registry structure has been revised. If you have been using SQL
Tuning version 6.1.2.or earlier or Quest SQL Optimizer 3.8.5 or earlier you may want to
review the Option settings and reselect your preferences.

Copy Impact Analyzer Files

If you have created a SQL Repository, Script files, and Analyzers in a version 6.1.2, or
earlier, of SQL Tuning and would like to use these in verison 7.0 or higher, you must
copy the files manually.
A new Group feature has been added to the Impact Analyzer to allow you to create
multiple SQL Repositories with corresponding Analyzers and Scripts according to the
database connection. The SQL Repository, Analyzers, and Scripts from the previous
version will all be placed in one Group in Quest SQL Optimizer for Oracle.
1 In Quest SQL Optimizer 7.0 for Oracle or higher, create a Group in the Impact
Analyzer. Do not open or add any SQL statements or Scripts to this new Group. If
you do, they will be over written by the data in your previous SQL Repository
and Scripts.
2 Locate the Analyzer Data directory where the files will be stored by clicking the
Options button and selecting the Analyzer page. This is the directory that is used
by default:

C:\Documents and Settings\User\Application Data\Quest Software\Quest SQL

Optimizer for Oracle\Impact Analyzer Data.
3 In the previous version of SQL Tuning for Oracle, locate the DATA directory
where the files are currently stored. From the SQL Tuning Analyzer, select Files |
Preferences and look at the Directory Setup page. This is the directory that is
used by default:
 Chapter 2 13
Configuration

C:\Documents and Settings\User\Application Data\Software\Quest

Software\STA\DATA
4 In Windows Explorer, located the folder for the Group that you just created. The
name of the folder will be PCAnnnnnnnnn. Check the time and date for the one
you just created.
5 From the SQL Tuning directory,

C:\Documents and Settings\User\Application Data\Software\Quest

Software\STA\DATA

copy the following files and folders to the Quest SQL Optimizer directory:

C:\Documents and Settings\User\Application Data\Quest Software\Quest SQL

Optimizer for Oracle\Analyzer Data\PCAnnnnnnnnn

Name Type Description

ODRepRep.tvw File Analyzer tree

ODRepScr.tvw File Script tree

ODRepSQL.tvw File SQL Repository

EDRepHdr.hdr File Header Information

ODANALYZER Folder Folder with Analyzer files

ODSCRIPT Folder Folder with Script files

ODSQL Folder Folder with SQL Repository files

14 Quest SQL Optimizer for Oracle
Installation Guide
Index

Symbols hardware requirements 6

.Net Framework 2 requirements 2
software requirements 6

Miscellaneous
L
508 compliance vii
launch 12

A
O
about
Quest Software vii online help vi
this guide vi
access 12 P
accessibility vii Privileges 2
additional information vi

Q
C Quest Software
contact about vii
Quest Software vii contact vii
Quest support viii contact support viii

H R
hardware requirements 6 requirements
help vi hardware 6
installation 2
I software 6
Impact Analyzer Files 12 Review Option Settings 12
information, additional vi
installation 7
16 Quest SQL Optimizer for Oracle
Installation Guide

S
Selector 9
software requirements 6
support
contact viii
system requirements 2
hardware 6
software 6

T
troubleshoot 12

U
uninstall 8
upgrade 8