New in This Release

Wednesday, September 05, 2018

SQL Optimizer for Oracle 9.3

SQL Optimizer for Oracle 9.3 is a minor release and includes resolved issues and the following enhancements.

• Support for Oracle Database 18c. This release of SQL Optimizer for Oracle includes support for Oracle Database 18c .

For a complete list of enhancements and resolved issues in this release, see the SQL Optimizer for Oracle 9.3 Release Notes at: Quest Support - SQL Optimizer for Oracle Technical Documentation.

SQL Optimizer for Oracle 9.2.3

SQL Optimizer for Oracle 9.2.3 is a minor release and includes the following enhancements.

Optimize SQL

Test Run Different Bind Values

• Average Logical Reads Column. An Average Logical Reads column is now included in the Alternatives pane of the results page. In addition, you can select Average Logical Reads as the performance criteria to display in the charts.
• Export to Excel. In the Alternatives pane of the results page, when using the right-click Save As | Excel Document command, all rows (parent rows and nested rows) are now exported to Excel.
• Performance Chart - Select SQL to Display. You can now select which top SQL alternatives to display in the Performance Chart by selecting alternatives from the selection pane.

• Performance Chart - Customize Colors. You can now modify the chart line color for each alternative displayed in the Performance Chart (Performance variation view) in the results page. This allows you to customize chart colors for better viewing.

  1. To modify the line color for an alternative, click the color-coded alternative name in the legend/selection pane.
  2. Select a color from the palette.

Additional Enhancements

• Session Logical Reads Column. The Session Logical Reads column has been restored to the Alternatives pane in the SQL Details tab. In addition, you can now select Session Logical Reads as the criteria for finding the best alternative in the Test Run Settings dialog.
• Export to Excel. When you export an Alternatives grid to Excel using Save As | Excel Document, the corresponding SQL text for each alternative is now included in the exported Excel file.

SQL Optimizer for Oracle 9.2.2

SQL Optimizer 9.2.2 is a minor release and includes the following new features and enhancements.

Optimize SQL

New Option to Ignore Record Count Mismatch. You can now instruct SQL Optimizer to ignore a record count mismatch between alternatives and the original SQL. This is useful if testing against tables in which the record count can change frequently during the test run. Enabling this feature suppresses the record count mismatch warning message in the Status column.

• To enable this feature, go to Options | Optimize SQL | Test Run and select Ignore record count difference from original SQL.

Custom Test Run Settings - SQL Termination Criteria. When using run time of the fastest SQL as the SQL termination time, you can now instruct SQL Optimizer to use the fastest run time in this test run only or to include the fastest run time from tested alternatives.

• To specify one of these options in the Test Run Settings dialog, select Customize Test Run Settings. Then select the Order and Termination tab. Select one of the options in the SQL Termination Criteria section.

Additional Features

Inspect SGA. A new metric, Elapsed Time per Execution, is now included in the run-time statistics that can be used to collect SQL in Inspect SGA. An Elapsed Time/Execution column is included in the SQL Statistics grid.

User Interface. A Chinese language version of the SQL Optimizer for Oracle application is not provided for this release. You can find a Chinese language version of the SQL Optimizer documentation (User Guide, Installation Guide, and Release Notes) at: https://support.quest.com/sql-optimizer-for-oracle/technical-documents.

SQL Optimizer for Oracle 9.2.1

SQL Optimizer 9.2.1 is a maintenance release and includes resolved issues and minor enhancements.

SQL Optimizer for Oracle 9.2

Optimize SQL

Stop and Resume the Optimization Process (SQL Rewrite)

This release of SQL Optimizer includes an exciting new enhancement to the Optimize SQL workflow. You can now interrupt the optimization process without losing the SQL alternatives and test run results generated thus far in your SQL Rewrite session. This is useful if you need to close SQL Optimizer or shut down your computer in the middle of a long-running optimization session. When it is convenient, reopen the saved session and resume running the optimization process, beginning from the point where it was interrupted.

Another important workflow enhancement enabled by this new feature is the ability to increase the intelligence level in the middle of an optimization process. If you find that the current intelligence level is not generating better or sufficient alternatives, you can stop the process, increase the level, and then resume the process. SQL Optimizer will use increased quotas and hints to find additional alternatives without losing the alternatives and results generated thus far.

Note: This new feature is available for SQL Rewrite sessions only. It is not available for Plan Control sessions at this time.

• To stop the optimization process, click the Stop button in the SQL Details toolbar.

  

• After the optimization process stops, alternatives that were in the process of executing now display a status of Stopped in the Alternatives pane.

  

• After stopping the process, you can save and close your session. The SQL alternatives and test run results generated thus far are saved with your SQL Rewrite session.

• If you want to increase the intelligence level to find additional alternatives, click one of the Intelligence Level buttons in the upper-right portion of the SQL Rewrite window, increase the level, and then resume the process.

  

• To resume the optimization process, open the saved session and select the desired action.
  • To continue running the Auto Optimize process, click the Auto Optimize button.
  • To continue to find alternatives, select the Rewrite command.
  • To continue to test run alternatives, select Test Run - Non-Tested.
  • To continue to find index alternatives, click the Index button to search for alternatives or select Test Run - Non-Tested to test run alternatives.

Test Run Untested Alternatives (SQL Rewrite)

The Test Run button now includes a new command, Test Run - Non-Tested. Use this command to test run SQL alternatives that have never been tested. This is useful if you paused the optimization process during the test run phase.

Note: This new feature is available for SQL Rewrite sessions only. It is not available for Plan Control sessions at this time.

• To test run untested alternatives, click the arrow beside the Test Run button and select Test Run - Non-Tested.

  

• This action will test run the remaining untested alternatives in a SQL Rewrite session that includes both tested and untested alternatives.
• Alternatives with a status of Stopped (stopped by the user) are included in execution with this method. Alternatives with a status of Aborted, Terminated, or Error are excluded from execution.

Specify Session-Level Optimization Settings

This release of SQL Optimizer provides a way to specify optimization settings for a single session without changing your default settings.

• To change the Intelligence level for the current session (SQL Rewrite or Plan Control), click the Optimizer Intelligence Level button. Then select a predefined setting and intelligence level, or customize the settings. Your settings are applied to the current session only. The settings previously specified through Options | Optimize SQL | Optimizer or Options | Optimize SQL | Plan Control remain unchanged.

  

• To change the Intelligence level for index generation for the current session (SQL Rewrite), click the Index Generation Intelligence Level button. Then select an intelligence level or customize the settings. Your settings are applied to the current session only. Any settings previously specified through Options | Optimize SQL | Index Generation remain unchanged.

  

Note: Settings specified through the Options dialog (for example Options | Optimize SQL | Optimizer) are now used as the default settings only and are applied to any new SQL Rewrite and Plan Control sessions you create. These default settings remain unchanged when you modify optimization settings at the session level.

Enhancements to the Test Run Different Bind Values Feature

This release includes multiple enhancements to the Test Run Different Bind Values feature.

• This feature was introduced in SQL Optimizer 9.1 and can be used when optimizing a SQL statement that includes a least one bind variable.

• To use this feature, generate and test run SQL alternatives using the SQL Details tab in Optimize SQL. Then select the Test Run Different Bind Values tab where you can test run the best-performing SQL alternatives again, this time using a list of bind values you specify.

  

• Bind value source. A new column in the Bind Values page identifies the source of the bind value. In addition, when you modify the list of bind values, new entries are highlighted green.

  

• Auto Fill finds all available values from Oracle. When you click the Auto Fill button to retrieve bind values from Oracle, all available bind value sets captured by Oracle are now retrieved. In the previous release, only the latest set of bind values captured by Oracle was retrieved. The Bind Variables Values grid is automatically populated with any value sets found that are not currently listed in the grid.

  

• For testing alternatives using bind value sets, you can now specify a default value for the SQL termination option Percentage of original SQL total run time available in the Test Run Settings dialog. Specify this default value through the Options dialog in Options | Optimize SQL | Test Run.

General

Execution Plan

• Display DBMS_XPLAN as plain text. You can now display execution plans using the DBMS_XPLAN in plain text format. This is the format returned from Oracle. To use this format, in the Execution Plan window right-click the plan and select View Plan | As DBMS_XPlan (Plain Text).

  

• Save plan as text file. You can now save an execution plan as a text file. This feature is available for the DBMS_XPlan formats.
  • In the Execution Plan window, select one of the DBMS_XPlan formats. Then right-click the plan and select Save. Select Text Files in the Save as type field.

Learn More

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

• Find answers to your questions in the SQL Optimizer forums.
• Learn tips and tricks from blog posts.

Back to Top

Copyright 2018 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 (https://www.quest.com) 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 https://www.quest.com/legal.

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 https://www.quest.com/legal/. 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.

Back to Top