Quest SQL Optimizer for Oracle 7.3

Quest SQL Optimizer for Oracle maximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. Quest SQL Optimizer automatically analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL collections from the SGA. The process of optimizing problematic SQL from multiple source code locations is completely automated. Whether you are a developer, DBA, or performance tuner, you can just let Quest SQL Optimizer analyze and optimize in batch all problem SQL from multiple sources and then Quest SQL Optimizer provides you with the replacement code with optimized SQL statements. Quest SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations for multiple SQL statements to simulated index impact analysis, through comparison of multiple SQL execution plans.

New in Release 7.4.1

Resolved Issues and Enhancements

The following is a list of issues addressed and enhancements implemented in this release of Quest SQL Optimizer.

Component	Resolved Issue in Version 7.4.1	Defect ID
Installation	The installation program no longer checks to see if Service Pack 1 or Service Pack 2 is installed for .NET Framework 2.0, although it does still check to see if .NET Framework 2.0 is installed. This enables you to install the Quest SQL Optimizer on Windows Vista.	SC658889-1 SC659533-1 ST54506
Installation	After the program is installed by a user with the Admin privilege, a limited user can now start the program instead of receiving an error when double-clicking the executable file. Note: When the program is installed by an Admin user, no shortcut to start the program is created for the limited user. Therefore the limited user will have to create the shortcut.	SC662796-1 SC658751-1 ST54780
SQL Scanner and SQL Inspector	When a job is being scanned or inspected, the status column in the Job List window now displays the percentage of the job that is completed while the job is in process.	ST53557

Component	Resolved Issue in Version 7.4	Defect ID
Tuning Lab	Added two new options to the SQL Termination Criteria on the Tuning Lab \| Execution \| Execution Criteria page to give more flexibility for terminating the execution when testing alternatives: Cancel execution by the fastest SQL run time and Cancel execution by the user defined time.	SC398153 SC446711 ST40317 ST40471
	The error message "ORA-06550: line n, column nn: PLS-00172: String literal too long" no longer appears when you perform a function that retrieves the execution plan on a very long SQL statement.	SC593277-1 SC627670-1 ST45668
	The execution of the SQL alternatives no longer takes extra time to complete the execution process when you have a SQL statement with a comment embedded before the WHERE clause. For example: select * from employee --comment-- where emp_id = emp_id	SC615434-1 ST49552
	The Copy function on the right-click menu in the SQL Text window under the Compare Scenarios page of the Tuning Lab now works.	SC622253-1 ST49073
	You can now restore a Saved Tuning Lab Session that was created in version 7.2 or earlier.	ST46739
	You no longer get the "index out of range" error message when you select the Tuning Lab \| Execution \| Execution Criteria page and then click OK without making any changes.	SC607573-1 ST47126
	You are no longer likely to receive an out of memory message when you optimize a SQL statement that generates hundreds of alternatives.	SC339156 ST40160
	You no longer need the DBA Role to deploy a stored outline.	ST47149
	The Retrieve Results function now opens the Result Set window with the SQL statement displayed and ready to be executed, whereas before it opened Toad or SQL Navigator without inserting the SQL statement.	SC605021-2 ST47239
Tuning Lab and Batch Optimizer	The client and trace statistics are no longer doubled when the original SQL statement is set to execute twice.	ST51541
Global Index and Tuning Lab	Indexes are now generated when you have set the schema to be different from the login schema.	SC651058-1 ST53211
SQL Optimizer	During the optimization process, the syntax transformation of the Not Equal condition no longer produces an alternative that does not produce the same result set as the original SQL.	SC604026-1 ST46850
SQL Scanner	A message now appears when you select the "Eliminate Duplicate SQL" option for a job that has already been scanned to let you know that selecting this option will remove all the SQL statements and their information.	ST49375
SQL Scanner and Batch Optimizer	When scanning a Performance Analysis SQL Repository, the minimum value is now greater than 0. Also, you no longer receive the error message "Input string was not in correct format" if the minimum value is set to a value greater than 999.	ST45669
Batch Optimizer	When you create a job from the SGA Inspector or from a Performance Analysis SQL Repository you cannot create a replacement script. The Generate Optimized Script function is now disabled for this case, whereas before it would generate an error for these job types.	ST45724
	In the Optimized Job report, the columns in the SQL Information grid were reorganized.	ST52396
	The Delete Current Job function no longer takes 2 or more minutes to complete.	ST45973
	The Open in Tuning Lab function is now available after you have aborted the execution of SQL statements in the Batch Optimizer so you can review the SQL statements that finished executing before you aborted the process.	ST45828
SGA Inspector	The Send to Batch Optimizer function is now disabled while the inspection is taking place.	ST52764
SGA Inspector	The Report button is now disabled when the inspection is in process for the currently selected job. This prevents an "unhandled error" message.	ST46413
Best Practices	The Best Practices results no longer say that there is no primary key when the table does have a primary key.	SC611285-1 ST49120
Integration with other Quest products	You can now send SQL statements which contain Korean and other character sets from other Quest products and Quest SQL Optimizer will launch as expected.	ST49668
Impact Analyzer	In an Analyzer, the changes in the "plan structure" and the "cost" are now correctly identified whereas before some of the plan changes were sometimes shown as cost changes and vice versa.	ST52415
Impact Analyzer	The "Add Snapshot As" function no longer hangs when it is aborted.	ST51097
Test for Scalability	When you select the criteria for choosing which SQL statements are sent to Benchmark Factory to test for scalability, the correct SQL statements are now sent to Benchmark Factory.	ST49696
Global Indexing	The "XML Construct error" no longer occurs when you send SQL statements from the SGA Inspector to Global Index when you are using the French decimal symbol = ",".	SC626203-1 ST49627
General	The warning message that appears after an error no longer is behind the Quest SQL Optimizer window where you would not see it. Instead it appears on top of the window as expected.	ST40612
	The SQL Classification rule that checks for the CARDINALITY in a specific operation now correctly identifies the SQL statements that fall into that classification.	ST49119
	The Delete (Current, All, Selected) function in several modules now prompted for a confirmation that you want to delete.	ST51255
	Quest SQL Optimizer for Oracle was tested and was confirmed to meet the FDCC Compliance standards.	ST47238
	New key types are available for licensing options for Per CPU Socket and Per CPU. Also for the Enterprise key, you will see "Enterprise" displayed so that you will know when a key is an Enterprise key.	ST51814
Connection Manager	You can now use the Delete key to remove a connection profile. You can also multi-select several connections to delete them altogether.	SC611802-1 ST47694
Reports	The PDF file that is created by the "Export to PDF" function is now considerably smaller than in previous versions.	SC576746-1

Known Issues

The following is a list of issues known to exist at the time of the Quest SQL Optimizer release.

Component	Known Issue	Change Request
Windows Vista	In Windows Vista, the Windows Admin privilege is required to run Quest SQL Optimizer. If you are logged on as a normal user and have the Universal Access Control (UAC) turned ON, you will be prompted for an Windows Admin logon as Quest SQL Optimizer starts. If you press Cancel to this prompt, the program will exit.
Integration with other Quest products	The new General Option: Launching Quest SQL Optimizer from other applications does not apply to any copy of Quest SQL Optimizer before 7.4. So if you have the production copy 7.4 or later and the trial copy 7.3 or earlier, when you launch Quest SQL Optimizer from another Quest product, it will always launch the 7.4 production copy. You cannot change it to launch the earlier copies. Note: If you want to use a previous copy of 7.3 or earlier, you should start Quest SQL Optimizer from the Windows Start menu and manually copy the text into the Tuning Lab module or Batch Optimizer module.
Installation	If you have installed Microsoft .NET Framework 2.0 Beta, you will receive the error message: "The installation of component Microsoft Net Framework 2.0 has failed." during the installation of Quest SQL Optimizer for Oracle. Workaround: Uninstall .NET Framework 2.0 Beta and install .NET Framework 2.0 with Service Pack 1 or Service Pack 2.
General	Quest SQL Optimizer for Oracle has been redesigned and reengineered. This new design affects the way the program is launched from other Quest products. Therefore, you cannot have both a previous version, formerly called Quest SQL Tuning for Oracle (6.1.2 or earlier) and Quest SQL Optimizer for Oracle (7.0 or later) “active” at the same time. You can have a previous version and the current version installed on the same PC, but you must set one or the other as the active program. The active program is the one that will be used when other Quest products launch the Quest SQL optimization program and send text to it. Note: This issue also affects Quest SQL Tuning for SQL Server version 6.2.1 or earlier. So, if you have both Quest SQL Optimizer for Oracle and Quest SQL Tuning for SQL Server you must also use the SQL Optimizer Version Selector to set one or the other as the "active" program.
General	In version 7.2 and later, we have combined the data directories locations onto one option page. Consequently, this changed the location where we stored the directory information. Therefore, if in version 7.0 you changed the location of the data directory, you will need to change this location in the Options again in version 7.4. Or, you will need to move your data files to the directory specified in the Options.
Startup	When the Windows user is setup as a "Limited" user (a non Administrator user), Quest SQL Optimizer will hang when started from a shortcut including Start \| Programs \| Quest Software \| Quest SQL Optimizer for Oracle \| Quest SQL Optimizer for Oracle. Workaround: Set the user as "Computer administrator" in the Windows user setup. Or, you can launch it by double-clicking the Quest SQL Optimizer for Oracle.exe file.	ST40486
SQL Scanner and Batch Optimizer	When scanning a Performance Analysis repository, it will only extract the SQL statements whose resolution_type = 3 in the repository.
SQL Scanner and Batch Optimizer	When scanning database objects and you select the "Include Dependents" option, it will only retrieve the first level of dependents whereas it should include all the dependents.	ST40625
SQL Scanner	The file format for the SQL Scanner data files has changes from the format used in previous versions of Quest SQL Tuning for Oracle 6.2.1 or earlier. Therefore, Scanner data files from previous versions of SQL Tuning cannot be used in Quest SQL Optimizer for Oracle version 7.0 or later.
SGA Inspector	The file format for the SGA Inspector data files has changes from the format used in previous versions of Quest SQL Tuning for Oracle 6.2.1 or earlier. Therefore, Inspector data files from previous versions of SQL Tuning cannot be used in Quest SQL Optimizer for Oracle version 7.0 or later.
Options	The Preferences settings from previous versions of SQL Tuning 6.1.2 or earlier will not be transferred to the Options settings for SQL Optimizer. You must customize all the Options settings once again.
Support Bundle	When the Windows user is setup as a "Limited" user (a non Administrator user), if you open the Support Bundle tab (select Help \| Support Bundle) and then perform an action in one of the modules, the Support Bundle log is not generated. Workaround: Set the user as "Computer administrator" in the Windows user setup.	ST40488
Printing	When you print a report in the Tuning Lab or Global Indexing, the report will go to the printer that is currently selected in Windows. There is no way to change this in Quest SQL Optimizer. Workaround: Change the printer in Windows before printing in Quest SQL Optimizer.	ST40403
National Language Support Note: These problems only arise if you type characters in the national language. If you are using the national Windows environment and type English text, these problems do not arise.	Using the French language settings, "select '€' from table" produces an error during the optimization process.	ST40273
	Using the French language settings in the Index Expert and Global Indexing, the table name and column names are not correctly displayed (spelled) in the SQL Text of the index sets.	ST40274 ST40489
	The Euro character € is not correctly displayed on the screen in the Outline Manager.	ST40213
	Many of the multi-language characters are not correctly displayed on the screen so that they look like garbage characters.	ST40298 CR40494
	Using French, German, simplified Chinese, or Korean settings in the Best Practices function, many of the characters are not correctly displayed on the screen so that they look like garbage characters.	ST40442 ST40478
	Using the Korean language settings, the parser in various modules fails to parse a SQL statement with Korean characters.	ST40148
	When running Quest SQL Optimizer in the simplified Chinese environment, many of the text lines of the options in the Options window are too long and either cover or are covered by the selection box.	ST40479
	When running Quest SQL Optimizer in the Korean environment, the highlighting of the difference between SQL statements is not correct in the Compare Scenarios layout in the Tuning Lab.	ST40491

System Requirements

Before installing Quest SQL Optimizer for Oracle, ensure your system meets the following minimum hardware and software requirements:

Global Operations

This section contains information about installing and operating this product in non-English configurations, such as those needed by customers outside of North America. This section does not replace the materials about supported platforms and configurations found elsewhere in the product documentation.
This release supports any single-byte character set. Support for double-byte or multi-byte character sets is limited. The majority of the features work under double-byte or multi-byte character sets, but there is some functionality documented under the known problems section that does not offer full double-byte or multi-byte character sets support. This release is targeted to support operations in the following regions: North America, Western Europe and Latin America. This release has been tested and offers limited support for Korean, Japanese and Chinese.

Getting Started

Contents of the Release Package

The Quest SQL Optimizer for Oracle release package contains the following products:

Installation Instructions

For More Information

Contacting Quest Software:

Contacting Quest Support:

Quest Support is available to customers who have a trial version of a Quest product or who have purchased a commercial version and have a valid maintenance contract.
Quest Support provides around the clock coverage with SupportLink, our web self-service. Visit SupportLink at http://support.quest.com/.

This document contains proprietary information protected by copyright. The software described in this document 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, contact:

Trademarks

Quest, Quest Software, the Quest Software logo, Benchmark Factory, LECCOTECH, Spotlight, SQL Navigator, SQLab, Toad, T.O.A.D., and Toad World are trademarks and registered trademarks of Quest Software, Inc. in the United States of America and other countries. Other trademarks and registered trademarks used in this guide are property of their respective owners.

Third Party Contributions

Disclaimer

The information in this document is provided in connection with Quest 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 products. EXCEPT AS SET FORTH IN QUEST'S TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, QUEST 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 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 HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Quest 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 does not make any commitment to update the information contained in this document.

Platform	Intel x86. (233 MHz or higher)
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. Amount of database activity. Number of concurrent users accessing the database.
Hard Disk Space	90 MB of disk space for installation
Operating System	Microsoft® Windows® 2000, 2003, XP Professional, Vista 32-bit Note: In Windows Vista, you must use a logon with the Admin privilege or if you are logged on as a normal user, you must have the Universal Access Control (UAC) turned OFF.
Additional Software	For Windows 2000, 2003, or XP Professional, Microsoft’s .NET Framework 2.0 SP1 or SP2, or 3.5 is required. Note: You must have either SP1 or SP2 for .NET Framework 2.0 installed. If you have only .NET Framework 3.0 installed, you must still install .NET Framework 2.0 and the service pack or you must install .NET Framework 3.5 (which includes .NET 2.0 SP1) For Windows Vista, Microsoft’s .NET Framework 3.0 is required.
Browser	Microsoft Internet Explorer 6.0 or later
PDF Viewer	Adobe® Acrobat® Reader 5.0 or later (for viewing the Installation Guide)
Database Client	SQL*NET NET8 10g Client Oracle 10g instance client Oracle 10g Easy Connect
Database Server	Oracle® 8.1.7, 9i, 10g, 11g

Welcome to Quest SQL Optimizer	New in this Release	Resolved Issues and Enhancements	Known Issues
System Requirements	Global Operations	Getting Started	For More Information