Release Notes
Thursday, May 18, 2017
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.
SQL Optimizer 9.2.2 is a maintenance release and includes resolved issues and minor enhancements. See Resolved Issues and Enhancements for more information.
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.
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.
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 9.2.1 is a maintenance release and includes resolved issues and minor enhancements. See Resolved Issues and Enhancements for more information.
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.
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.
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 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.
This release includes multiple enhancements to the Test Run Different Bind Values feature.
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.
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).
See Resolved Issues for a more complete list of issues resolved and enhancements implemented in this release.
Remember, you can find blogs, videos, and forums at the SQL Optimizer for Oracle Community.
The following is a list of issues addressed and enhancements implemented in this release of SQL Optimizer for Oracle.
Feature | Resolved Issue | Defect ID |
---|---|---|
General | SQL Optimizer for Oracle was tested with and supports Windows Server 2016. | SOFO-2114 |
General | SQL Optimizer for Oracle was tested with and supports Oracle Database 12.2 (Oracle Database 12c Release 2). | SOFO-2119 |
Inspect SGA | Added the metric of Elapsed Time per Execution to the run-time statistics that can be used to collect SQL in Inspect SGA. | SOFO-2053 |
Optimize SQL | Added support for CAST to varchar with "CHAR" label. | SOFO-2088 |
Optimize SQL | Added an option (Options | Optimize SQL | Test Run ) to ignore record count mismatch between alternatives and original SQL. | SOFO-2084 |
Optimize SQL | Corrected an issue that resulted in an incorrect value for "Average Physical Reads" in the Test Run Different Bind Values tab. This issue was encountered when the average value was between 0 and 1, and user's Regional Number format specified the comma (,) as the Decimal symbol. | SOFO-2057 |
Optimize SQL | In the Test Run Different Bind Values tab, when importing bind values from a file, ANSI encoding is now supported. | SOFO-2056 |
Optimize SQL | In the custom test run settings in the Test Run Settings dialog, when specifying SQL termination criteria you can now use the fastest run time in this test run only or include the fastest run time from previous test runs. | SOFO-2118 |
Feature | Resolved Issue | Defect ID |
---|---|---|
Batch Optimize SQL | The Termination Delay option in Options | Batch Optimize SQL | Execution | Termination Criteria now works as expected in the Batch Optimize SQL module. | SOFO-1871 |
Integration with other products | If the user selects a Beta edition of SQL Optimizer for Oracle as the default to open from Toad for Oracle, when the Beta edition expires, the user can reset the default application back to the commercial edition of SQL Optimizer for Oracle. | SOFO-641 |
Integration with other products | Corrected an issue that caused a connection error when attempting to optimize SQL sent from Performance Analysis for Oracle. | SOFO-631 |
Integration with other products | Corrected an issue that unexpectedly modified the Save Password option when connection information was sent from Toad for Oracle to SQL Optimizer for Oracle where the connection already existed. | SOFO-1939 |
Optimize Indexes | Corrected an issue in the Optimize Indexes module that resulted in 4 database sessions opened instead of two. | SOFO-1943 |
Optimize SQL | A bind value set name change is now included in the list of changes when you modify and then rerun a test in the Test Run Different Bind Values tab. | SOFO-1228 |
Optimize SQL | After generating all alternatives using the "Rewrite" action, clicking the Auto-Optimize button (or Rewrite and Test Run) correctly initiates the test run process, where expected. | SOFO-1845 |
Optimize SQL | Corrected an issue that caused a connection error when attempting to run an alternative to retrieve the result set (Run Result) after deploying an alternative as an outline. | SOFO-1917 |
Optimize SQL | You can now abort the original SQL when testing alternatives using the Auto Optimize action. If you selected test run settings that are based on the original SQL, aborting the original could abort all alternatives. If this is the case, SQL Optimizer informs you and prompts you to confirm or cancel the abort. | SOFO-1980 |
Optimize SQL | Test Run Different Bind Values: When using the right-click Print command in the Alternatives pane of the results page, all rows of the master-detail grid are now printed, as expected. | SOFO-1167 |
Optimize SQL | Test Run Different Bind Values: Corrected an issue that exported only the parent rows of the master-detail grid when using the right-click Save As | HTML/PDF/TXT commands in the Alternatives pane of the results page. Now all rows of the master-detail grid are exported to HTML, PDF, or TXT, as expected. | SOFO-1161 |
User Interface | The Data Browser in the Test Run Different Bind Values tab now includes a Modify button in the Select drop-down pane to modify an added function, as expected. | SOFO-1289 |
User Interface | Optimize SQL: Corrected an issue that removed the current status in the Status column from an executing test when switching between sessions. | SOFO-1983 |
User Interface |
Corrected an issue that temporarily disabled the Test Run Current button after switching from the Test Run Different Bind Values tab to the SQL Details tab and then back again. |
SOFO-1287 |
User Interface | In the Test Run Different Bind Values tab, after changing the criteria, the Best Alternative shown is now consistent within all the panels, without switching sessions to refresh the screen. | SOFO-1834 |
User Interface | Manage Plans: Corrected a refresh issue in the Execution Plan pane. | SOFO-1918 |
User Interface | Plan Control - Test Run Different Bind Values: In the SQL Text pane of the Define Bind Values page, the SQL text is displayed as expected, even if the original SQL was not selected for the test run. | SOFO-1868 |
User Interface | Optimize SQL: Corrected a highlighting issue in the Scenario Name column when using the Find Panel. | SOFO-1479 |
User Interface | For a few different windows in the application, corrected an issue that prevented the online Help from opening to the applicable Help topic after pressing the F1 key. | N/A |
User Interface | Corrected a refreshing issue that unexpectedly displayed currently executing SQL status for a session in the window of another session, when running multiple sessions. | SOFO-1899 |
User Interface | Optimize SQL - Reports: When switching between completed sessions, the Optimization Status field in Optimization Details retains the correct value, as expected. | SOFO-1801 |
Feature | Resolved Issue | Defect ID |
---|---|---|
Analyze Impact | In the Impacts tab, the labels on the chart are now consistent with the column names in the grid. | SOFO-1532 |
Analyze Impact | After sending an index from Optimize Indexes to Analyze Impact, the Tables/Columns drop-down list is pre-populated with tables, as expected, without requiring the user to select the connection again. | SOFO-643 |
Analyze Impact | Corrected issues with analyzing a user-defined index given a custom name (non-default name). | SOFO-358 |
General | Improved support for complicated synonym relationships. | SOFO-1626 |
Inspect SGA | In the SQL Filter page of the Add Inspect SGA Job wizard, if you set the filter to retrieve SELECT statements, then statements that include a WITH clause are now collected as expected. | SOFO-651 |
Licensing | If SQL Optimizer prompts you for a valid license while attempting to connect in a new session, you no longer encounter an "Invalid license" error after entering a valid license. | SOFO-639 |
Optimize Indexes | Corrected an issue that caused an error if the user selects the cost option check boxes while a search process is running. | SOFO-1327 |
Optimize SQL | If the "Transform query to inline view" option is not selected in Optimization Settings, as expected the SQL is not transformed to an inline view. | SOFO-1504 |
Optimize SQL | Corrected an issue that caused a parser error when attempting to run SQL after creating the Plan table. | SOFO-1457 |
Optimize SQL | In the Comparison with Original SQL tool tip, the Criteria type is now displayed, as expected. | SOFO-664 |
Optimize SQL | Enhancement: When connected to an Exadata database, the "Offloading Operation and Bloom Filter" column is now shown in the Test Run Different Bind Values Results page (Alternatives grid). | SOFO-1259 |
Optimize SQL | SQL Rewrite: Corrected an issue that prevented SQL with no cost from being tested when the "Test Run - All" command was used. | SOFO-1422 |
Optimize SQL | In the Compare tab, columns are now displayed in the same order as in the Details pane, as expected. | SOFO-1314 |
Optimize SQL | Corrected an issue that caused the Abort Current command to fail in the Test Run Different Bind Values tab when all SQL were test run. | SOFO-1356 |
Optimize SQL | In the Test Run Different Bind Values tab, user's column display settings are now retained between sessions, as expected. | SOFO-1367 |
Optimize SQL | In the Test Run Different Bind Values tab, selections made on the "Finish Setup" and "Let Me Select What to Test Run" pages are synchronized in some scenarios for a better workflow. | SOFO-1247 |
Optimize SQL | In the Test Run Different Bind Values tab, if you modify the bind sets and then select "Test run only the newly added or modified" the values are now executed in the order specified, as expected. | SOFO-1260 |
Optimize SQL | Enahancement: In the Test Run Different Bind Values tab, added a few UI enhancements to the Performance Chart. | SOFO-1347 |
User Interface | The User Defined Alternative Virtual Indexes tab can be displayed and persists during the SQL Rewrite process as expected. | SOFO-1235 |
User Interface | In a grid, for column headers containing a hyperlink, you can now successfully sort the column by clicking the column header outside the hyperlink. | SOFO-1250 |
The following is a list of issues known to exist at the time of this release.
Feature | Known Issue | Defect ID |
---|---|---|
General |
If you select a non-default schema for a SQL statement in SQL Optimizer and send it to Benchmark Factory™, the default schema is preserved in the SQL statement and an error occurs when it is executed. Workaround: Select the appropriate schema in Benchmark Factory™. |
ST68829 |
Scan SQL and Batch Optimize |
When scanning a Performance Investigator repository, Scan SQL only extracts SQL statements with the resolution_type of 3 in the repository. |
ST77286 |
When you select the "Include Dependants" option while scanning database objects, only first level dependents are retrieved instead of all dependents. |
ST40625 | |
Connecting to Database |
When the directory where SQL Optimizer is installed has a directory path which contains parenthesis, connecting to Oracle fails when using a pre-11g client. This is a known Oracle issue that we cannot fix. This problem is more common in 64-bit environment because the default directory for 32-bit applications is "C:\Program Files (x86)\". Workaround: Install SQL Optimizer in a directory that does not have parenthesis or upgrade the Oracle client to 11g. |
ST63421 |
Installation (Upgrade) |
In Windows Server 2008 R1 (or earlier) or Windows Vista (or earlier), if you upgrade to SQL Optimizer 9.0 from a previous release, a known issue in Microsoft Windows Installer prevents the upgrade from opening. Workaround: Uninstall and then reinstall SQL Optimizer 9.0. See https://support.microsoft.com/en-us/kb/905238 for more information. |
N/A |
Installation |
In a Windows Server 2003 SP2 or Windows Server 2008 environment that does not have the applicable hotfix installed (see link below), if you upgrade to SQL Optimizer 9.0 from a previous version, after installation the application may fail to launch. This is a known issue in Windows Installer 4.5 and Microsoft has provided a hotfix. For more details see http://support.microsoft.com/kb/972397. Workaround: If you installed SQL Optimizer 9.0 in one of the environments described above that does not have the applicable hotfix, uninstall it. Install the hotfix provided by Microsoft. Then reinstall SQL Optimizer 9.0. Or install the hotfix prior to upgrading to 9.0. |
N/A |
Installation |
If you upgrade to SQL Optimizer 8.9.1 from a previous version and then uninstall 8.9.1, the 8.9.1 shortcut and some installation files are not removed as expected. Workaround: Manually remove the 8.9.1 shortcut and remaining 8.9.1 files. |
ST115266 |
Installation | If you install SQL Optimizer version 8.8.1 (or later) and then do a side-by-side install of version 8.8.0, version 8.8.0 will fail to launch after it is successfully installed. |
ST111404 |
Installation |
If you install SQL Optimizer 8.8.0 (or later) side-by-side with version 8.7.0, then uninstall version 8.7.0, version 8.8.0 (or later) will no longer integrate with other Toad products. This issue is found in side-by-side installations of 8.8 and either 8.5.0, 8.6.0, 8.7.0, or 8.7.1 that are of the same edition (i.e., both Commercial edition) and the same bitness (i.e., both 32-bit). Workaround 1: Launch SQL Optimizer 8.8.0 (or later) from the short-cut on the Start menu. Workaround 2: Uninstall SQL Optimizer 8.8.0 (or later) and reinstall it. |
ST109347 |
Installation |
Background: SQL Optimizer 8.7 (or later) does not support an upgrade from the 32-bit version to the 64-bit version (or 64-bit version to 32-bit version). If you attempt to upgrade by using the Quest Software Installer, you will receive the appropriate warning message and the installation will not proceed. Issue: If you attempt to upgrade from the 32-bit version of SQL Optimizer to the 64-bit version (or from the 64-bit version to the 32-bit version) by using the .msi installer, you will not receive a warning message and the installation will appear to proceed. However, you will encounter an error upon launching the upgraded application. Workaround: To upgrade to a newer version of SQL Optimizer that has a different "bitness" (i.e., from 32-bit to 64-bit), you must first uninstall the previous version. |
ST102226 |
Installation |
A shortcut is not created in the Metro UI on Windows 8 and Windows Server 2012 for limited users in the following cases (when application is installed "for everyone"):
|
ST105153 |
Installation |
A shortcut is not created in the Windows Start menu for limited users when upgrading or performing a side-by-side installation of SQL Optimizer 8.5 or later. Workaround: Launch SQL Optimizer from Windows Explorer. |
ST76887 |
Installation |
The CPM product key is not automatically entered when upgrading or performing a side-by-side installation of SQL Optimizer 7.5 or later. Workaround: Manually enter CPM product key in licensing window. |
ST73191 |
Installation |
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.
Note: On Windows 7 and 2008 64 bits R2, set the UAC level to Never Notify me When. |
ST66341 |
Report |
Unicode characters do not display correctly in the generated report. Workaround: For PDF: Select a font that contains the specific Unicode characters. Do this by modifying the font settings that apply to data grids and generated reports.
For HTML and MHT: When exporting the report, select a Character set that contains the Unicode characters. For CSV and Text: When exporting the report, select an Encoding option that supports the Unicode characters. |
ST102639 |
Help | Currently, you cannot enter Unicode characters in the Index or Search fields of the online help. |
ST71331 |
When searching the online help for information using the Search bar in SQL Optimizer, topics display in the help window for exact matches found but the related index entry is not highlighted. |
ST71433 |
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center