Chat now with support
Chat with Support

SQL Optimizer for Oracle 9.3.2 - Release Notes

Release Notes

SQL Optimizer for Oracle 9.3.2

Release Notes

Friday, September 20, 2019



About SQL Optimizer for Oracle

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.

 


New Features

SQL Optimizer for Oracle 9.3.2

SQL Optimizer for Oracle 9.3.2 is a patch release and includes resolved issues and the following enhancement:

  • Support for Oracle 19c

See also Resolved Issues and Enhancements.

SQL Optimizer for Oracle 9.3.1

SQL Optimizer for Oracle 9.3.1 is a minor release and includes resolved issues and the following enhancement.

  • Improved performance when identifying invalid SQL. This release includes an enhancement to the SQL parsing process which significantly reduces the time required to identify certain invalid SQL statements.

See also Resolved Issues and Enhancements.

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 .

See also Resolved Issues and Enhancements.

SQL Optimizer for Oracle 9.2.3

SQL Optimizer for Oracle 9.2.3 is a minor release and includes the following enhancements. See also Resolved Issues and 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. See also Resolved Issues 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.

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

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. See Resolved Issues and Enhancements for more information.

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.

See Resolved Issues 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.

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

Discontinued Features

The following features are no longer included in SQL Optimizer for Oracle.

Integration with Foglight Performance Investigator (PI). Beginning with SQL Optimizer for Oracle 9.3, you will no longer be able to gather SQL from a Foglight Performance Investigator repository.

 


Resolved Issues

Resolved Issues and Enhancements

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

Resolved Issues in 9.3.2

Feature Resolved Issue Defect ID
Optimize SQL Corrected an issue when optimized SQL was not semantically equivalent to the original SQL script. SOFO-2602

Resolved Issues in 9.3.1

Feature Resolved Issue Defect ID
Scan SQL Corrected an issue that caused an Out-of-Memory error after attempting to scan multiple files in Scan SQL or collecting SQL in Inspect SGA. SOFO-2016

Resolved Issues in 9.3

Feature Resolved Issue Defect ID
Batch Optimize SQL Corrected an issue that resulted in incorrect data in the "Times of Improvement" column after the SQL List grid was exported to an Excel (or other) file. SOFO-2214
General This release of SQL Optimizer for Oracle includes support for Oracle Database 18c . SOFO-2391
Options Corrected an issue in which recent custom settings where not retained if the user re-opened the options page and then clicked the Cancel button. This issue was encountered in the Options | Optimize SQL | Plan Control (Intelligence) page. SOFO-2386
SQL Syntax Added support for the PIVOT operator in SQL statements. SOFO-2081
SQL Syntax Added support for "sysdate@!" and "user@!" in SQL statements. SOFO-621
SQL Syntax Added support for the following syntax pattern: select * from (dual) x. SOFO-2137

Resolved Issues in 9.2.3

Feature Resolved Issue Defect ID
General Corrected an issue that resulted in unnecessary blank rows when exporting an Alternatives grid to Excel. SOFO-2158
General When an Alternatives grid is exported to Excel (Save As | Excel Document), the statistics time values are now formatted as HH:MM:SS. SOFO-1476
Licensing Corrected an issue that prevented support for the SQL Navigator Development Suite 7.x license key. SOFO-2168
Optimize SQL Enhancement: In the Test Run Different Bind Values tab, the Alternatives pane of the results page now includes a column for Average Logical Reads. You can also select Average Logical Reads as the performance criteria to display in the charts. SOFO-2050
Optimize SQL Restored the Session Logical Reads column in the Alternatives pane in the SQL Details tab and as criteria for finding the best alternative in the Test Run Settings dialog. SOFO-2050
Optimize SQL Test Run Different Bind Values: In the previous release, only the parent rows of the master-detail grid were exported when using the right-click Save As | Excel Document command in the Alternatives pane of the results page. Now all rows (parent and nested) of the master-detail grid are exported to Excel. SOFO-2157
Optimize SQL Enhancement: When exporting an Alternatives grid to Excel using Save As | Excel Document, the SQL text for each alternative is also included in the exported Excel file. SOFO-2054
Optimize SQL Enhancement: In the Test Run Different Bind Values tab, you can now modify the chart line color for each alternative displayed in the Performance Chart (Performance variation view) in the results page. SOFO-2161
Optimize SQL Enhancement: In the Test Run Different Bind Values tab, you can now select which top SQL alternatives to display in the Performance Chart by selecting alternatives from the selection pane. SOFO-2165
Optimize SQL Test Run Different Bind Values: Corrected an issue that caused incorrect statistics to be exported when using the right-click Save As | Excel Document command in the Alternatives pane of the result page. The incorrect statistics were found in the Performance Chart and the Elapsed Time and First Row Time columns. SOFO-2195
User Interface Clicking Yes in the "New Updates Available" message now successfully provides the user with access to the SQL Optimizer for Oracle software download page. SOFO-2155

Resolved Issues in 9.2.2

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

Resolved Issues in 9.2.1

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

Resolved Issues in 9.2

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

Self Service Tools
Knowledge Base
Notifications & Alerts
Product Support
Software Downloads
Technical Documentation
User Forums
Video Tutorials
RSS Feed
Contact Us
Licensing Assistance
Technical Support
View All
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating