Quest SQL Optimizer 7.5.2 for Oracle

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

Quest SQL Optimizer for Oracle 7.5.2

Quest SQL Optimizer for Oracle will now migrate settings and data files from previously installed versions to the newly installed version. This release also includes general bug fixes and enhancements.

Quest SQL Optimizer for Oracle 7.5.1

Quest SQL Optimizer for Oracle 7.5.0

Resolved Issues and Enhancements

Resolved Issues and Enhancements in 7.5.2

Resolved Issues and Enhancements in 7.5.1

Component	Resolved Issue	Defect ID
National Language Support	The Scan SQL module no longer hangs when running on Windows XP when you have Japanese selected as the language setting.	ST68085
National Language Support	When an error message is displayed that includes text from non-English databases, the non-English characters are now correctly displayed in the error message.	ST40273
Batch Optimize	When selecting a value for a bind variable in the Setting Bind Variables window, the correct format for the DATE data type is now displayed.	ST40694
	The program no longer suddenly closes after you have tried to optimize a SQL statement with a schema that is not the correct schema for the SQL statement and then you attempt to change the schema to the one that corresponds to the SQL statement.	ST57900
	In the Setting Bind Variables window, the data type is now entered into the form along with the value when you select the value from a table.	ST45786
	You will no longer receive this error message, "The following unhandled error has occurred: Object reference not set to an instance of an object." when you abort the searching process.	ST68700
	The Test for Scalability button is no longer enabled in the Batch Optimize module if Benchmark Factory is not installed.	ST66385
General	The error message, "Unhandled exception has occurred in a component in your application. If you click Continue the application will ignore this error and attempt to continue. Could not find file 'C:\Documents and Settings\username\Local Settings\Temp\tmp75.tmp." no longer occurs as you start Quest SQL Optimizer when you have the Windows environment variables TEMP and TMP set to different directories.	ST68656
	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 now generated as expected.	ST40488
	With the Windows user is setup as a "Limited" user (a non Administrator user), you can now launch Quest SQL Optimizer from Start \| Programs \| Quest Software \| Quest SQL Optimizer for Oracle \| Quest SQL Optimizer for Oracle.	ST40486
	Less memory is used by the application.	ST67766
	The Nested Loop rule for SQL classification now correctly classifies SQL statements which have a Nested Loop and meet the requirements of the rule.	ST67354
	The Hash Join rule for SQL classification now correctly classifies SQL statements which have a Hash Join and meet the requirements of the rule.	ST67355
SQL Scanner	The Job List window now displays the correct information about the SQL classification for each SQL statement after you have aborted a job and then exited and re-opened the program.	ST59427
Optimize SQL	You will no longer get a "string length" error when you execute a SQL statement which has a RAW data type in a column in the SELECT list.	ST68440
	A change was made to the optimization engine for checking if an Oracle optimization hint can be applied at the subquery level to enable more alternatives to be generated.	ST67320
	After you close the Welcome window, the SQL Details tab in Optimize SQL no longer displays as a completely blank layout when you re-open Quest SQL Optimizer.	ST67022
	The optimization process no longer occasionally hangs when creating a large number of alternative SQL statements.	ST66803
	After you have optimized a SQL statement or generated index candidates in the Optimized SQL module and then exited from Quest SQL Optimizer, the optimization session files are no longer sent to the recycle bin on your computer.	ST67254
	When you use the Run Result function to retrieve the results of a SQL statement and that SQL statement has a bind variable, you will no longer get the error message, "Error occurs during browsing data from database. ORA-01008: not all variables are bound." after you enter the variable and the SQL statement starts to execute.	ST66913
	When the correct information is entered for the FTP selection in the Trace Setup options, it will no longer occasionally fail to validate the connection. Also, when you are optimizing and executing the SQL statements using the Optimize button, it will not longer display the message "Error retrieving trace statistics" in the Statistics Information window.	ST67097
	In the Alternatives window, the graph next to the statistic value is no longer missing when the SQL statement and alternatives are sent to the Optimize SQL module from the Batch Optimize module.	ST67311
	After you have generated index sets, when you click the Compare tab to view the indexes and execution plans side-by-side, the drop down lists now list the original SQL and the index sets. Also, you will not receive this error if you click the Next or Last button, "The following unhandled error has occurred: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index."	ST67513
	You can execute the original SQL statement with the index set. Previously, this message appeared, "The parser did not identify the text as a valid SQL Statement. Create index ... This type of SQL Statement is not supported."	ST67512
	In the right-click menu in the Alternatives window, the Select options were added to allow you to unselect and select all SQL alternatives and index sets.	ST67772
	When you select Abort Current while running the Batch Run, the SQL statement will now abort.	ST67263
	When you are connected to an Oracle 11g database and you use the Optimize button to optimize and execute the original and alternative SQL statements, the execution process no longer stops after executing only the original SQL statement.	ST67930
	If you save a session that only contains the original SQL statement, it now correctly saves and restores that SQL statement.	ST67278
	When you select only one SQL statement, the Batch Run Selected and Batch Run Multiple Selected buttons are now enabled so that you can execute that one SQL statement.	ST67259
	In the Optimization Details window, the number of SQL alternatives generated is now correct instead of being one less than it should be.	ST66847
	When your SQL statement has a bind variable and you generate indexes candidates, if you then retrieve the actual execution plan, the program will now give you the plan instead of giving you no response.	ST68211
	You will no longer receive this error, "The Parser did not identify the text as a valid SQL statement." when you try to retrieve the actual execution plan for an index candidate.	ST67215
	When executing an UPDATE statement, you will no longer occasionally be prompted with the Parameters window when there is no variable in the UPDATE statement.	ST40652
	When using the Run Result function, you can now execute a SQL statement with an INTO clause.	ST67853
	The trophy icon that marks the best SQL alternative no longer disappears from the Compare tab window when you are scrolling in the grid.	ST66842
	You will no longer incorrectly receive this error: "Record Count not matched. Number of records in this SQL is different from that of the original" if an index candidate is execute before the original SQL statement	ST66303
	When the language on your computer is set to Korean, the slider on the Intelligence Level Options page for Optimizer and Index Generation now points to the correct number.	ST66237
	You will no longer get this error message "ORA-01008" when you execute a SQL statement with a bind variable whose name does not start with a letter of the alphabet. For example: select * from dual where 1 <> :1.	ST68704
	The report now always includes the SQL text and execution plan for the original SQL and the best alternative.	ST66846
Integration with other Quest products	You will no longer receive this error message, "Length cannot be less than zero. Parameter name: length" when you send a very long SQL statement from Toad or Spotlight and the SQL statement is all on one line.	ST68447
	When you send a SQL statement from Toad to the Optimize SQL module, the SQL statement text now appears immediately in the SQL Text window instead of you having to refresh the window to see it.	ST66990
	You will no longer get an access violation error, if you have installed the Toad DBA Suite for Oracle 9.7.2 which included Quest SQL Optimizer 7.4, install Quest SQL Optimizer 7.5 as a side-by-side installation and uninstall Quest SQL Optimizer 7.5 or later and then send a SQL statement to Quest SQL Optimizer from Toad.	ST67023
	You will no longer receive an unhandled error message when a SQL statement is sent from Toad to Quest SQL Optimizer when the license key for Quest SQL Optimizer has expired. Instead the Quest SQL Optimizer for Oracle Licensing window appears so you can enter a new key.	ST66848
Uninstall	Some issues were resolved with uninstalling files in the Program Files\Common Files\Quest Shared\Quest SQL Optimizer Shared directory when 2 or more versions of Quest SQL Optimizer for Oracle are installed.	ST66941
Uninstall	When you install Quest SQL Optimizer into a directory other than the default directory and then uninstall it, it now uninstalls the Quest SQL Optimizer for Oracle Common component so that you will no longer find it in Add or Remove Programs.	ST66944
Connection Manager	You will no longer get the message "Root element is missing" when you are trying to connect to Oracle and the connection fails. Instead you will get the error message explaining that you have not make a successful connection.	ST67223
Connection Manager	You can now enter multiple commands in the login script which are executed each time you connect to a database.	ST66532

Known Issues

Known Issues in 7.5.2

Component	Known Issue	Change Request
Install	Quest SQL Optimizer for Oracle 7.5.2 is not listed in the Quest Installer if it is silently installed.	ST70182
General	The time is not included with the datestamp in the Data Browser.	ST69091
	If you select a non-default schema in a SQL statement in Toad and send it to Quest SQL Optimizer, the default schema is preserved in the SQL and an error occurs when the SQL is executed. Workaround: Select the appropriate schema in Quest SQL Optimizer.	ST68825
	If you select a non-default schema in a SQL statement in Quest SQL Optimizer and send it to Benchmark Factory, the default schema is preserved in the SQL and an error occurs when the SQL is executed. Workaround: Select the appropriate schema in Benchmark Factory.	ST68829
	If you have the trial version installed with another version of Quest SQL Optimizer and the trial is set to your default in a Quest application and then you send SQL to Quest SQL Optimizer, you receive an error. Workaround: Select a non-trial version of Quest SQL Optimizer as your default to use when information is sent from another Quest application. This option is set on the General Options page.	ST68952
	An "ORA-00911: invalid character" error displays when a connection cannot be made due to a Login Script issue. The error should specify that the connection issue is due to the Login Script.	ST69284
	When you send SQL from Performance Analysis to Quest SQL Optimizer, the Check Connection window displays with an error message. Workaround: Cancel the operation and then select the appropriate connection in Quest SQL Optimizer.	ST69535
	Quest SQL Optimizer for Oracle versions 7.5.0 and 7.5.1 cannot run side-by-side in a 64-bit operating system.	ST69563
Optimize SQL	If you click Explain Plan in the Optimize SQL tab and close the Connection window without selecting a database, a Schema dropdown displays on the right side of the application but displays nothing when selected because there is no connection.	ST69280
Optimize SQL	Schema information is not sent to Optimize SQL from the Inspector. Workaround: Set the schema after it is sent to Optimize SQL.	ST69076
Batch Optimize	Schema information is not sent to Batch Optimize from the Inspector or Scanner. Workaround: Set the schema after it is sent to Batch Optimize.	ST69077
Connection Manager	The LDAP dropdown does not list all servers in the Connection Manager.	ST69600

Known Issues in 7.5.1

Component	Known Issue	Change Request
Optimize SQL	If you enter a comment before the SQL statement using the "-- comment" format, you will get the error message, "ORA-00900: invalid SQL statement ORA-06512: at line 61" when you execute this SQL statement. You can see this error message when you click the Status column in the Alternatives Window. Workaround: Remove the comment or move it to the end of any line in the SQL statement.	ST68781
Optimize SQL	Because of the redesign of the Optimize SQL module (formerly the Tuning Lab module), a Saved Optimization Session (.STS file) created in the Tuning Lab in version 7.4 or earlier is not compatible with the file structure of this redesigned module. Therefore, you cannot restore a session from previous versions of Quest SQL Optimizer into the Optimize SQL module of version 7.5.
Uninstall Previous Versions	If you do not uninstall version 7.4.1 or earlier before you install version 7.5, you cannot use the Quest Installer to uninstall it. You must use the Windows Add/Remove Programs to uninstall version 7.4.1.
Connecting to Database	When the directory where Quest SQL Optimizer is installed has a directory path which contains parenthesis, connecting to Oracle will fail when using a pre-11G client. This is a known Oracle issue, so we cannot fix it. This problem is more common in 64-bit environment because the default directory for 32-bit applications is "C:\Program Files (x86)\". Workaround: Install Quest SQL Optimizer into a directory that does not have parentheses or upgrade the Oracle client to 11G.
Silent Installation on Vista	You cannot do a silent installation on a Windows Vista computer if the User Account Control (UAC) is set to ON. Workaround: Set the User Account Control to OFF. Log on to Vista with an Admin user. Select Start \| Control Panel \| User Accounts. Select Turn User Account Control on or off. Clear Use User Account Control (UAC) to help protect your computer. Restart the computer. Select Start \| Run and enter cmd. Click OK. Enter *msiexec /i "path to msi file\QuestSQLOptimizer_Oracle_version.msi" /qn. Follow steps 1-3 and then check Use User Account Control (UAC) to help protect your computer. Note:* On Windows 7 and 2008 64 bits R2, set the UAC level to "Never Notify me When:".
Scan SQL and Optimize Batch	When scanning a Performance Analysis repository, it will only extract the SQL statements whose resolution_type = 3 in the repository.
Scan SQL and Optimize Batch	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
Scan SQL	The file format for the Scan SQL data files changed in version 7.0 from the format used in previous versions of Quest SQL Tuning for Oracle 6.2.1 or earlier. Therefore, Scan SQL data files from previous versions of SQL Tuning cannot be used in Quest SQL Optimizer for Oracle version 7.0 or later.
Inspect SGA	The file format for the Inspect SGA data files changed in version 7.0 from the format used in previous versions of Quest SQL Tuning for Oracle 6.2.1 or earlier. Therefore, Inspect SGA data files from previous versions of SQL Tuning cannot be used in Quest SQL Optimizer for Oracle version 7.0 or later.
Session Parameter	Three parameters were added at the bottom of the list of parameters instead of being placed in alphabetical order in the list of Oracle parameters in the Session Parameters window in the Optimize SQL module.	ST66613
Options	The Options settings from previous versions of SQL Optimizer 7.4.1 or earlier will not be transferred to the Options settings for SQL Optimizer 7.5.1. You must customize all the Options settings once again.
Printing	When you print a report in Advise Indexes, the report will go to the printer that is currently selected in Windows. There is no way to change the printer 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 in Index Generation and Advise Indexes, the table name and column names are not correctly displayed (spelled) in the SQL Text of the index sets.	ST40489
	The Euro character € is not correctly displayed on the screen in the Manage Outlines module.	ST40213
	Many of the multi-language characters are not correctly displayed on the screen so that they look like garbage characters.	ST40298
	You will receive an error message when you execute a SQL statement that has a bind variable name with Chinese (Unicode) characters.	ST65114

System Requirements

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

Citrix Requirements

Quest SQL Optimizer has been tested for Citrix support on Windows Server 2003 Enterprise Server SP2 using the MetaFrame Presentation Server 4.0 and Citrix Presentation Server Clients 10.2.

Global Operations

Quest SQL Optimizer 7.5 for Oracle supports Unicode in most of its modules and it has native support for any Oracle Unicode character set, such as UTF8 or AL32UTF8. However, these modules are not Unicode supported yet: Advise Indexes, Manage Outlines, and Analyze Impact. These modules are tested in English environments only and using them on Unicode or non-English environment may not be supported.

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 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 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.

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

Trademarks

Quest, Quest Software, the Quest Software logo, Benchmark Factory, Spotlight, SQL Navigator, 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.

Component	Known Issue	Change Request
General	Quest SQL Optimizer now supports Oracle's alternative quoting mechanism (q-quoted syntax).	ST43347
	Quest SQL Optimizer now saves your default data directories when you upgrade to a new version.	ST67548
	Quest SQL Optimizer can now connect using Instant Client.	ST68824
	You can now specify a new name when you save a session.	ST69996
	Synonyms with a hierarchy of more than two levels (with the second level objects as private synonyms from another user) are now identified as valid objects in the parser.	ST70103
Optimize SQL	If you enter a comment before the SQL statement using the "-- comment" format, you no longer receive an error message when you execute this SQL statement.	ST68781

Platform	1 GHz Processor
Memory	512 MB of RAM minimum; 768 MB of RAM recommended
Hard Disk Space	90 MB of disk space for installation
Operating System	Microsoft Windows 2000 Microsoft Windows 2003 (32-bit and 64-bit) Microsoft Windows XP (32-bit and 64-bit) Microsoft Windows Vista (32-bit and 64-bit) Microsoft Windows 2008 (32-bit and 64-bit) Microsoft Windows 7 (32-bit and 64-bit)
Additional Software	For Windows 2000, 2003, or XP Professional, Microsoft’s .NET Framework 2.0 SP1 or SP2, or 3.5 is required. 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, Windows 2008 and Windows 7, Microsoft’s .NET Framework 3.0 is required.
Browser	Microsoft Internet Explorer 6.0 or later
PDF Viewer	Adobe Acrobat Reader 7.0 or later (for viewing the Installation Guide)
Database Client	(32-bit Client only) Net8 Net9 Net10 Net11 Instant Client
Database Server	Oracle 8.1.7, 9i, 10g, 11g R1 & R2

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

From	To
Batch Optimizer	Batch Optimize
Tuning Lab	Optimize SQL
SQL Scanner	Scan SQL
SGA Inspector	Inspect SGA
Impact Analyzer	Analyze Impact
Outline Manager	Manage Outlines
Global Indexing	Advise Indexes
Index Expert	Index Generation