Get to Know Quest SQL Optimizer 7.5 for Oracle
Updates to Quest SQL Optimizer for Oracle
- LDAP and Direct Connection - Connecting to Oracle using LDAP (Named Server Connection) and a direct connection are now supported using the Connection Manager.
- Modules Renamed - The modules in Quest SQL Optimizer were renamed.
| 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 |
- Side-by-Side Installation and New Data Directory - Starting in 7.5, each new version of Quest SQL Optimizer for Oracle can be installed in a new directory, so that you can have the current and previous versions installed. If you select the same directory as the current installation, Quest SQL Optimizer will be upgraded. If you select a new directory, then a new copy of Quest SQL Optimizer will be installed.
Whether you upgrade or install a new copy, the default data directory will include the version number in the directory path for the verison that you just installed.
C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\7.5\
If you would like to use the data you created in a previous version, either change the default directories in the General | Directory Setup options
or
move your data to the new directory.
Note that the directory names were changed to correspond to the new module names.
| Default Data Directory Before Version 7.5 |
Default Data Directory in Version 7.5.0 |
| C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\Batch Optimizer Data |
C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\7.5\Batch Optimize Data |
| C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\SQL Scanner Data |
C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\7.5\Scan SQL |
| C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\SGA Inspector Data |
C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\7.5\Inspect SGA Data |
| C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\Impact Analyzer Data |
C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for Oracle\7.5\Analyze Impact Data |
- Session Parameters - In the Optimize SQL module, the Session Parameters window which allows you to alter session parameters now has a drop-down list for each parameter which has specific values that you can select.
These three parameters were added to the list of parameters: use_stored_outlines, hash_join_enabled,and _optimizer_sortmerge_join_enabled. Note: These parameters were added at the bottom of the list and therefore are not in the correct location in alphabetically order.
You can also save different session parameters for each SQL alternative. This enables you to test the original SQL statement with different parameters. To do this:
1. Enter your original SQL statement.
2. Click Add User Alterative. This copies your SQL statement to another alternative.
3. Instead of editing the alternative, right-click and select Alter Session Parameters.
4. Change parameters.
5. Repeat steps 2-4 for as many different parameter changes as you would like.
6. Click Batch Run to execute the original SQL statement and the alternatives with the various parameter changes.
- New Option for SQL Execution - When running the Batch Run for executing the SQL alternatives, the Run all SQL twice if original SQL runs faster than n (seconds) gives you the option to have all the SQL statements executed twice for fast running SQL to make sure that all the indexes, SQL statements, and data are cached to give the most accurate run time comparisons.
- New Batch Optimize Option - The Cancelation Delay option was added to the SQL Termination Criteria options in the Batch Optimize module. This option enables you to specify how many additional seconds are added to the termination time used to stop the execution of a SQL alternative. It enables you to account for the delay in sending the execute command to the server due to network traffic because the application is running in a client/server environment.
- Scan Performance Analysis Connection - A list of database connections is now provided when you are scanning a Performance Analysis repository so that you can select the database where the repository resides if it is different from the database connection selected for the group. This applies to the Batch Optimize and the Scan SQL modules
- Intelligence Levels - In the SQL Optimizer and Index Generation options, the Intelligence Levels were changed from 1 - 10 to 1 - 5. The previous level 2 is now level 1, 4 is 2, 6 is 3, 8 is 4, and 10 is 5.
- Run Results - The Run Results function in the Optimize SQL module no longer sends the SQL statement to another program (Toad or SQL Navigator). Instead it executes the SQL statement within the program and displays the results.
- Revised the Execution Plan Comparison - The
algorithm which is used to compare the execution plans when the SQL alternatives are generated was revised. This algorithm determines which execution plans are identical and thus which SQL alternatives are eliminated due to identical plans. The plan steps including the Filter and Order Predicates are being checked. The Plan Cost is no longer checked.
New Features in Quest SQL Optimizer for Oracle
- Optimize SQL (formerly Tuning Lab) - This module was completely redesigned with a new user interface that has been simplified and is easier to use.
- Control and Help Panel - In the middle of the screen in the Optimize SQL module is a control and help panel to help new users understand what is going on during SQL optimization and what next step a user can take during or after SQL optimization process. You can toggle the display of this panel on or off.
- Optimization Process - The optimization process in the Optimize SQL module now returns each alternative SQL statement as soon as it is generated. This means that if you cancel the optimization process you will have the SQL alternatives that have been generated thus far.
- Rewrite SQL and Execute Simultaneously -
In the Optimize SQL module, the execution of the original SQL and the alternatives can now be automatically started during the optimization process so that you can complete the SQL generation and testing process more quickly. You can also stop a lengthy optimization process if one of the first alternatives gives you satisfactory performance improvement.
- Intelligence Selection of SQL Alternatives to Execute - A new feature was implemented in the Optimize SQL module that will select one SQL alternative to execute from each group of SQL alternatives whose plan cost is determined to be within the same cost group. The SQL alternatives that are selected from the plan cost groups are executed before the rest of the SQL alternatives. The Execution order for SQL option enables you to use this Intelligence order or you can select Plan cost to execute the SQL alternatives in order of the lowest plan cost to the highest cost. By executing one SQL from each group first, you increase the likelihood that you will find a faster alternative quickly because SQL statements with similar costs tend to have similar performance.
- Unicode Support in the Optimize SQL Module - The Optimize SQL module now supports Unicode. The Batch Optimize, Inspect SGA, and Scan SQL already supported Unicode. Analyze Impact, Advise Indexes, and Manage Outlines modules do not support Unicode.
- New Scanner Option - The Automatically start scanning SQL when a job is added option enables you to have the Scan SQL jobs automatically scanned after you have added them to the Scanner group.
- 64-bit Operating Systems - You can now use Quest SQL Optimizer on computers running 64-bit Windows 2003, Windows XP, and Windows Vista.
- Login Script -
An option was added on the General | General Options page so that you can create a script that will be executed when you connect to a database.
