Embarcadero® DB Optimizer™ 3.5

Welcome to Embarcadero® DB Optimizer™ 3.5

Last Updated: March 23, 2012

DB Optimizer Home | DB Optimizer Documentation | Embarcadero Technical Support | www.embarcadero.com

Thank you for using Embarcadero DB Optimizer, an enterprise SQL development and optimization tool that gives database developers and administrators the perfect environment for building the best performing SQL code. This application easily identifies inefficient SQL code and boosts developer productivity by ensuring SQL is right before it becomes a problem in mission-critical applications. DB Optimizer also provides a rich SQL IDE with code completion, real-time error checking, code formatting and sophisticated object validation tools. Lastly, DB Optimizer provides advanced project and data source management tools.

What's New in DB Optimizer

What's New in DB Optimizer 3.5

What's New in DB Optimizer 3.0

Release Notes

System Requirements

DBMS Support

Important Advisory Notes

Upgrade Notes

Bug Fixes

What's New in DB Optimizer 3.5

The following briefly outlines the changes in DB Optimizer 3.5. For a detailed description of these changes see the DB Optimizer Quick Start Guide available at a docs.embarcadero.com/products/db_optimizer.

Profiling

Repository management improvements (simplification).
Variable/parameter capture (Oracle).
Profiling data filters now allow you to restrict profile data to a particular user, database, schema, or application.

Tuning

Reporting (previously only available for profiling).
Vastly improved bind variable management.
Automatic bind variable extraction for Oracle SGA.
More intuitive UI (catalog/schema overrides, simplified workflows).

VST diagrams

Diagramming of generated cases.
Improved SQL coverage, across all platforms.
Query rewrites on all platforms (previously only available on Oracle).

Other

Update to Eclipse 3.6 and Java 1.6.
DBO localized to Chinese.

What's New in DB Optimizer 3.0

User Interface Changes

Removal of Eclipse-specific UI elements: To provide a better focus on DB Optimizer-specific functionality and workflows, UI elements provided by the underlying Eclipse framework aimed primarily at Java development have been removed. These include the Navigate, Project, and Run menus, as well as toolbar buttons for debugging and code navigation.
Shortcuts to DB Optimizer Preferences: A new Preferences menu has been added with shortcuts to the preference pages relevant to DB Optimizer configuration. These pages (and their associated menu entries) are Data Sources, SQL Development, SQL Editor, SQL Execution, SQL Filters, Data Source Indexing, Profile Alerts, and Tuning Job Editor. The set of all preferences is available under Preferences > Other option, and the previously-available Window > Preferences option.

Object Indexing Enabled by Default

Previous versions of DB Optimizer had the option to create an index of schema objects on a data source in order to provide code completion and semantic validation of object names in the SQL Editor, but this was disabled by default, because it could affect data source performance. In DB Optimizer 3.0, the speed and efficiency of Object Indexing has been significantly improved, so this functionality is now enabled by default. This option can still be disabled from the preferences window (available from the Preferences > Data Source Indexing option).

New Actions on Oracle Sessions During Profiling

While profiling an Oracle data source, the Sessions tab in the profiling editor has two new context menu actions on rows in the session grid:

Kill Session: Instructs the database to kill the given session. The session will still appear within the profiling editor, based on its activity prior to being killed.
Trace Session: Instructs the database to begin a full trace operation on the session. (This simply executes set_sql_trace_in_session from Oracle's dbms_system package for the given session.)

MS SQL Server and Sybase ASE Profiling Improved

Profiling on Microsoft SQL Server and Sybase Adaptive Server Enterprise are now more efficient, contributing less than 2% load on the profiled data source under most conditions. Furthermore, more accurate data is collected.

Improved User Interface for the Tuning Editor

The dedicated Input tab of the Tuning Editor has been removed, allowing the user to specify statements for tuning directly within the Overview tab. In the top-right of the Tuning Editor, there is an input selection button, to add statements for tuning from the following sources:

New SQL Text: Input/pasted by the user into a dialog box.
Extract from Database Object: Use SQL DML statements from an appropriate schema object (such as views, procedures, triggers, functions, materialized views, and packages). Alternatively, objects can be dragged and dropped onto the Tuning Statements grid from the Data Source Explorer tree.
Import from File (Workspace): Import statements from a file in an existing SQL Project.
Import from File (System): Import statements from a file on a local disk or from a network share.

To view or modify a tuning statement, or to bind variable values within the statement, double-click on the statement in the grid or right-click and select Edit. from the context menu.

Oracle RAC Profiling

All instances of an Oracle Real Application Cluster (RAC) can be profiled using a single profiling session (and a single connection to the cluster). To enable this functionality for a RAC data source, you must access the Profile Configurations dialog, in one of the following ways:

Right-click the data source in the Data Source Explorer and select Profile As > Profile Configurations..
Click the down-arrow button next to the New Profiling Session button in the toolbar, and select Profile Configurations..

Within the Profile Configurations dialog, check the box for Real Application Cluster (RAC) mode. Within a RAC profiling session, the filter bar at the top of the profiling editor includes an Instances filter, which allows you to view profiling results for individual instances in the cluster, or show results for sessions from all cluster instances. Furthermore, several tabs from the Profiling Details view will show the ID of the instance on which the selected statement/event/session was observed.

Profiling Alerts

During profiling, alerts can be triggered based on thresholds set on the number of:

Active sessions
Sessions experiencing a wait condition
Active sessions not on CPU

These alerts can produce a system notification (from the Windows status tray) and/or generate an email to be sent to one or more addresses. This allows you to begin a profiling session, continue with other work, and return to DB Optimizer when the alert has fired to investigate the underlying cause. Alerts can be set up from the preferences page available from the menu item at Preferences > Profile Alerts. Alerts may be configured per data source and multiple alerts may be configured for a profiling session. In order to generate email messages for alerts, the mail server options must be specified on the Email Settings page under the Profile Alerts preferences, and one or more recipients must be specified on the Email Contacts page.

Profiling Reports

The results of a profiling session can now be exported as an HTML or PDF document that can be saved for later, shared, and printed. Within the Profiling Editor, the Export Report button is located in the top right, between the filter options and the Help button. By default, the report is generated for the entire duration of the profiling session. To restrict the report to a particular time interval, click and drag on the profile session chart. To clear the selection, click once on the chart outside the selection.

Support for Common Table Expressions on Oracle and MS SQL Server

The SQL Editor and Tuning Editor now both support common table expressions (CTEs) for Oracle and SQL Server. This includes syntax highlighting and checking in the editor, as well as generation of explain plans for CTEs from the SQL Editor and the Tuning Editor.p>

Improved Visual SQL Tuning for Oracle

The Visual SQL Tuning (VST) diagram for Oracle 10g and 11g now has the option of showing Explain Plan details in addition to the column relationships. The explain plan display can be toggled on/off by clicking the leftmost button that appears when hovering over the SQL Analysis diagram. The additional nodes shown in the Explain Plan overlay provide details on the flow of the query plan, with operations (such as nested loops, sorts, and joins) showing connecting tables and other operations.

Release Notes

System Requirements

Before installing Embarcadero® DB OptimizerT, verify that your environment meets the following requirements.

Hardware Requirements

The following are the minimum hardware requirements:

Pentium 4-Level Processor
1024 MB of memory
500 MB of disk space
1024 x 768 screen resolution

Supported Operating Systems

DB Optimizer is supported on the following operating systems:

Microsoft Windows 7, 32 and 64 bit
Microsoft Windows Server 2008, 32 and 64 bit
Microsoft Windows Vista, 32 and 64 bit
Microsoft Windows XP, 32 and 64 bit
Microsoft Windows 2003
Linux, x86-32, GTK 2

Red Hat Enterprise Linux 5.0
SuSe Linux Enterprise Server 10

Other Requirements

DB Optimizer requires a Java 6 VM to run:

Windows XP/Vista/2003 Sun Java 2 Standard Edition 6.0 Update 29 for Microsoft Windows
Linux: Sun Java 2 Standard Edition 6.0 Update 29 for Linux x86
DB Optimizer can be installed as a standalone application (RCP installation) or as an Eclipse plug-in (plug-in installation).
The Eclipse plug-in version of DB Optimizer requires Eclipse version 3.6 or higher and Sun Java Standard Edition 6.0 Update 29 or later in addition to regular system requirements. Before installing the plug-in version of DB Optimizer, ensure that Eclipse and Java are installed on your machine. You can download Eclipse from the following Web site: www.eclipse.org/downloads.

DBMS Support

SQL execution and SQL tuning with DB Optimizer is supported on the following DBMS platforms and versions:

Oracle (9i - 11g)
Sybase (12.5 - 15.5)
IBM DB2 for LUW (8.0 - 9.7)
MS SQL Server (2000, 2005, 2008)
JDBC (execution only)

SQL profiling with DB Optimizer is supported on the following DBMS platforms and versions:

Oracle (9i - 11g)
Sybase (12.5.0.3 - 15.5)
IBM DB2 for LUW (8.2 - 9.7)
MS SQL Server (*2000 SP3, 2005, 2008)

* On Microsoft SQL Server 2000, the Profiling feature is only supported if Service Pack 3 (SP3) is installed.

Note: DB Optimizer does not access in any way, shape or form any of the information, tables or views licensed under Oracle's diagnostic pack.

Upgrade Notes

DB Optimizer should be installed in a different directory than previous versions of DB Optimizer. If DB Optimizer is installed in the same directory, the following error will result in at application start up: "Unable to read workbench state. Workbench UI layout will be reset.
If a previous version is already installed on your machine, either change the default Workspace or delete all files from the Workspace directory. The Workspace directory location appears whenever you start Optimizer, and can be redefined from this dialog box. If the same Workspace is indicated for the new version as was indicated previously, the following error will result: "Cannot restore workbench layout".
If DB/Optimizer is already running, you can also modify the Workspace directory by choosing File > Switch Workspace > Other from the Main Menu.
We strongly suggest selecting a new workspace when upgrading from a previous version of DB Optimizer.
Saved profiling and tuning session files (.oar) created using previous versions of DB Optimizer are not compatible with DB Optimizer XE and DB Optimizer 2.5. You can however have more than one version of DB Optimizer installed on the same system. To read old .oar files, run the older version of DB Optimizer.

Bug Fixes

Use your browser Find feature to locate a particular bug number or click a link below to see the issues addressed in a specific build.

Bugs Fixed in DB Optimizer 3.5

Bugs Fixed in DB Optimizer 3.0

Bugs Fixed in DB Optimizer 3.5
OPT-3167	Profiling a Sybase 15.0.2 data source no longer breaks down when bad procedure data is received from the procedure text collection.
OPT-2964	Profiling a data source no longer breaks down when data source name is longer than 30 characters. Instead, the following message displays, "The data source name exceeds 30 characters. Please disconnect and rename the data source before profiling."
OPT-

Bugs Fixed in DB Optimizer 3.0
OPT-2642	Profiling Details for a DB2 data source now correctly reports elapsed time in the Execution Statistics section of the Profiling Details tab.
OPT-2646	Performance of a tuning job for an Oracle 9i data source has been increased. Semantic validation and query analysis are now performed more efficiently.
OPT-2647	Error in tuning job while generating cases, "object caching: could not retrieve the following object because it doesn't exist or is not qualified properly: object name" no longer displayed.
OPT-2668	The Profiler now correctly uses SYSTDATE for Oracle 8 and SYSTEMSTAMP for Oracle 9 and higher.
OPT-2696	On Oracle 9, tuning performance and semantic validations of constraint queries has been inproved.
OPT-2716	SQL Parser no longer displays error "Encountered Unexpected Token" when tuning well formed SQL.
OPT-2779	TOP keyword in Sybase 12.5.3 and higher is now recognized by the SQL Parser.
OPT-2782	Sybase object resolution no longer fails for databases created by users with SA_ROLE when tuning as sa.
OPT-2786	Tuning error for SQL Server 2008 that appeared when the database was created by a user with SA_ROLE who is not the sa, has been fixed.

Embarcadero Technologies Technical Support

If you have a valid maintenance contract with Embarcadero Technologies, our Support team will assist you with any problems you have with our applications. Our maintenance contract also entitles registered users of Embarcadero Technologies products to download free software upgrades during the active contract period.

Evaluators receive free technical support for the term of their evaluation. To download evaluations of Embarcadero Technologies products go to the www.embarcadero.com.

To contact Support, go to Support.