Quest^® SQL Optimizer for Oracle

Version 8.8.1

Release Notes

August 09, 2013

Contents

Welcome to Quest SQL Optimizer for Oracle

Quest SQL Optimizer for Oracle automates the SQL optimization process and maximizes the performance of your SQL statements. SQL Optimizer 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.

Back to Top

New in This Release

This release of SQL Optimizer includes the following new features and enhancements.

New in Release 8.8.1

Support for New Oracle Database 12c Features

This release of SQL Optimizer includes support for the following new Oracle Database 12c features.

• In this release of SQL Optimizer you can optimize SQL statements that contain the Oracle 12c FETCH FIRST clause for limiting the number of rows returned:

  FETCH FIRST N ROWS ONLY

• This release includes support for the Oracle Identity Column (new in Oracle 12c). You can identify the Identity Column in a table by using the Schema Information tab. Column details are listed for the selected table, including whether a column is an Identity Column or not.

• This release supports Oracle 12c pluggable databases and container databases. You can connect to an Oracle 12c pluggable database using SQL Optimizer 8.8.1 and use all the same functionality you normally do to optimize SQL.
• This release also supports Oracle 12c invisible columns. Invisible columns can be used in a SQL statement, but are not visible to the end-user. SQL Optimizer 8.8.1 supports the use of invisible columns when rewriting your SQL statements.
• This release includes support for LATERAL, an extension of the inline view syntax, in Oracle Database 12C.
• Enhanced Oracle native LEFT OUTER JOIN syntax that allows multiple tables on the left of an outer join is supported.
• This release supports SQL statements that include Oracle 12c row pattern matching syntax.

Optimize SQL

• The algorithm used to determine batch run execution order (when the Intelligent order option is selected) has been enhanced to reduce the time it takes to find the best alternative. This is done by increasing the likelihood of executing the most favorable alternatives early in the batch run process. This enhancement applies to the Batch Run and Auto Optimize methods of optimization.

Integration with Toad for Oracle

• For Toad for Oracle users, SQL Optimizer now uses your Toad login script to set up your environment when you invoke SQL Optimizer using the Optimize Current SQL or the Scanning options in Toad.

• If you are using a login script for Oracle TEMP tables in Toad, when invoked from Toad, SQL Optimizer will also use the log-in script.

• These enhancements are particularly useful if you are an Oracle Application user who frequently takes advantage of the Toad login script feature.

User Interface

• This release includes several new keyboard shortcuts for navigating the SQL Optimizer user interface. You can use these keys and key-combinations to open drop-down lists, activate buttons and links, scroll and refresh, and move between UI elements.
• See the online Help for the list of keyboard shortcuts and the command/action associated with each.

New in Release 8.8

Optimize SQL

• JOIN Syntax. You now have the option to instruct SQL Optimizer to use the same JOIN syntax as the original SQL statement when generating SQL statement alternatives. This is the default setting and can be modified if necessary in SQL Optimizer Options (Options | Optimize SQL | Optimizer | Optimization).

• User-Defined Alternatives. When you create a User-Defined Alternative, SQL Optimizer now checks if the plan is the same as the original statement or any of the other system-generated alternatives. If it is the same, a message displays in the Execution Plan window. This can be useful when you are attempting to rewrite a statement to create your own alternative.

• Unused Index. When creating a User-Defined Alternative, if you create an index and the index is not used, an icon displays in the upper-right portion of the Virtual Indexes tab alerting you to the fact that the index is not used in any retrieved plans.

• Invisible Indexes. This release supports Oracle invisible indexes. SQL Optimizer will not show invisible indexes in the Optimize SQL, Optimize Indexes, and Analyze Impact modules.

• XML Type Data Type. This release of SQL Optimizer supports columns with the data type XML Type.

• Optimize SQL Resolution Report. You can now include multiple SQL alternatives in the Optimize SQL Resolution Report. In the Report window, click the Current Settings button to select which alternatives to include.

New Rules and Syntax Support

• New transformation rules have been added to promote more offloading operations in EXADATA.

• When optimizing a SQL statement, SQL Optimizer will now rewrite a SELECT statement that is in a Set clause.

• This release includes support for the function LNNVL in SQL statements.

  SELECT COUNT(*)
  FROM employees
  WHERE LNNVL(commission_pct >= .2);

• For SQL statements with multiple sub-queries, multiple sub-queries can be rewritten in an alternative SQL. Previously, only one sub-query could be rewritten.

Scan SQL

• For each SQL statement in a group, you can now replace the auto-generated name with a custom name. Type the new name over the auto-generated name in the SQL Name column.

• You can also add a custom note for each SQL statement. Click the arrow in the User Comments column and enter your note.

Analyze Impact

• On the Impacts page, below the Estimated Improvements chart you can find data for the number of SQL in different impact categories. Now you can click the number to go directly to the list of SQL for that category.

• Once on the SQL List page, the breadcrumb contains a drop-down list for easy navigation to other SQL lists.

General

Execution Plan

• SQL Optimizer can now tell you if an execution plan uses any of the following:
  • Cardinality feedback
  • Dynamic sampling
  • SQL plan directives
  • Adaptive plan

  To display this information, right-click in the Execution Plan pane and select Plan Information.

• Compare Plan Statistics. You can now compare the actual statistics and the estimates in the Actual Plan tab for a selected statement in Optimize SQL. Click the top-most button in the Execution Plan toolbar (right side) to display the statistics comparison.

Options

• When using the Predefined Intelligence Level, the JOIN syntax options in Option | Optimize SQL | Optimization are now editable.

See Resolved Issues and Enhancements for a more complete list of issues resolved and enhancements implemented in this release.

Learn More

Remember, you can find blogs, videos, and forums at the SQL Optimizer for Oracle Community.

• Learn about new features from SQL Optimizer video demos.
• Find answers to your questions in the SQL Optimizer forums.
• Learn tips and tricks from blog posts.
• Find product documentation in the SQL Optimizer wiki.

Back to Top

Resolved Issues and Enhancements

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

Resolved Issues and Enhancements in Release 8.8.1

Resolved Issues and Enhancements in Release 8.8

Back to Top

Known Issues

The following is a list of issues known to exist at the time of this release.

Back to Top

System Requirements

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

Virtualization Support

Before installing SQL Optimizer, review the following for virtualization support:

Back to Top

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 is Unicode-enabled and supports any character set. It supports simultaneous operation with multilingual data. This release is targeted to support operations in the following regions: North America, Western Europe and Latin America, Central and Eastern Europe, Far-East Asia, Japan.

The release is localized to the following languages: Chinese (Simplified), Japanese, Korean.

Back to Top

Getting Started

Contents of the Release Package

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

1. Quest SQL Optimizer for Oracle
2. Product Documentation, including:
   • Installation Guide
   • User Guide
   • Online Help, which includes tutorials

Installation Instructions

Refer to the Installation Guide for installation instructions.

Upgrade from Previous Release

SQL Optimizer 8.7 (or later) does not support an upgrade from the 32-bit version to the 64-bit version (or 64-bit to 32-bit). To upgrade from the 32-bit version of SQL Optimizer to the 64-bit version, please uninstall the previous 32-bit version. See ST102226 in the Known Issues section for more information.

Note: SQL Optimizer 8.7 (or later) supports side-by-side installation of versions with different "bitness" as long as they are also different releases (e.g., 8.6 and 8.7) or different editions (e.g., Commercial and Trial).

Back to Top

For More Information

Contact Quest Software

See our web site for regional and international office information.

Contact Quest Support

Quest Support is available to customers who have a trial version of a Quest product or who have purchased a Quest product and have a valid maintenance contract. Quest Support provides unlimited 24x7 access to our Support Portal at www.quest.com/support.

From our Support Portal, you can do the following:

• Retrieve thousands of solutions from our online Knowledge Base
• Download the latest releases and service packs
• Create, update and review Support cases

View the Global Support Guide for a detailed explanation of support programs, online services, contact information, policies and procedures. The guide is available at: www.quest.com/support.

Quest SQL Optimizer Community

Get the latest product information, find helpful resources, and join a discussion with the Quest SQL Optimizer team and other community members. Join the SQL Optimizer community at http://www.toadworld.com/products/sql-optimizer-for-oracle/default.aspx.

Back to Top

© 2013 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 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:

Quest Software World Headquarters
LEGAL Dept
5 Polaris Way
Aliso Viejo, CA 92656
email: legal@quest.com

Refer to our Web site (www.quest.com) for regional and international office information.

Patents

SQL Optimizer for Oracle contains patent pending technology.

Trademarks

Quest, Quest Software, the Quest Software logo, Simplicity at Work, Benchmark Factory, Foglight, LeccoTech, Quest vWorkspace, SQLab, Toad and T.O.A.D. are trademarks and registered trademarks of Quest Software, Inc. For a complete list of Quest Software’s trademarks, please see http://www.quest.com/legal/trademark-information.aspx. Other trademarks are property of their respective owners.

Back to Top