DBO Optimizer Versions

From DB Optimizer
Jump to: navigation, search


1.0 July 2008

SQL Profiling (cross platform)
Load Chart
SQL Tuning (Oracle only)

1.0.1 Nov 2008

Reduce Oracle collection resource usage 30% down to 1%
Sybase collection performance improvements
Max CPU line Oracle
Screen display problems (mainly 2 monitors)

1.0.2 Jan 2009 - Changes for Sanctuary Licensing and All-Access 1.5 March 2009

SQL tuning Cross platform (SQL Server,Oracle, Sybase, DB2)
Hint Injection Full Cross Platform (DB2,Oracle,SQL Server, Sybase)
Analysis tab (Oracle only )
table statistics
column frequency
Outlines tab - stored hints (Oracle only)
Details Editor (DB2,Oracle,SQL Server, Sybase)
Continuous profiling
Session and SQL extra detail collection (Oracle only)
Command Line Profiling

1.5.1 May 2009

Load Editor – new feature
Run script by X sessions for Y loops or Z amount of time
Simulates load
Tests for multiuser bottlenecks
Command line Tuning
Profile sends SQL to Tuner as executing user
Play nice with CM and RSD work

2.0 Sept 2009

Continuous profiling (as opposed to a predetermined profiling period)
Cross platform SQL and Session details
Cross platform Analysis of Indexes
Visual SQL Tuning (VST) diagram

2.0.1 Dec 2009 patches for All-Access licensing and fixes for command line profiling interface

2.5 targeting late Q2 2010

The main new feature in 2.5 will be allowing profiling data to be saved to a central repository that can be shared on the network. SQL Server, Oracle, Sybase and DB2 can all be profiled and saved to the repository. The repository itself has to be Oracle in 2.5 but we will expand the options to Sybase, SQL Server and DB2 in 3.0.
The shared repository will allow DBO to be run on different PCs and all of them can save and query the data into and out of the same repository and the profiling can run 24x7 where as currently we say that profiling should only be run 8 hours or less.
We are also improving the Visual SQL Tuning (VST) diagram adding an important fearture, the "filter ratio". The filter ratio is the amount of the table returned by using filters on that table in the diagram. The table that has the most selective filter should be where the execution plan starts. It's important information for understanding how the query should be executed. We will expand this in 3.0 to actually suggest a best plan.
Here is the basic outline.
  • Allow users to specify number of engines/CPUs for the "Max CPU" line
  • Save profile data directly to to a central shared repository on Oracle (expand to other DBs in 3.0)
  • Collect and display average SQL execution times (excluding Sybase)
  • For sql coming from procedures, show the procedure and line number and SQL (SQL Server, Oracle, Sybase)
  • Average wait times for events on Events tab (Oracle only)
  • VST diagram - display Filter ratios.
  • Add more Query re-writes (new transformation-style generated cases).
  • Save more data (all) to tuning file.
  • Remove RSD cache from the tuning editor and turn caching off by default for DBO.
Time permitting:
  • Differentiate plan hashes from executed SQL (Oracle).
  • General UI: simplified menus
  • Interbase: transformation cases and VST tuning only

3.0 targeting end of Q4 2010

3.5 targeting end of Q2 2011

Functional offering Matrices as of Sept 2009 (DB Optimizer 2.0

Profiling SQL tab enhancements

Platform Average SQL exec time Show procedure content Show executed line in procedure
Oracle yes (planned 3.0) (planned 3.0 for and higher )
Sybase (planned 3.0) (planned 2.5) (planned 2.5)
DB2 yes (planned 2.5) (planned 2.5)
SQL Server (planned 2.5) (planned 2.5) (planned 2.5)

Oracle profiling already distinguishes between the calling procedure and sql executed by the procedure. These values are displayed as different rows in TOP SQL. By contrast, SQL Server, Sybase and DB2 only report the procedure in TOP SQL thus the priority of adding procedure content and line number in 2.5 for these platforms and delaying the improvements on Oracle till 3.0 since Oracle's functionality is already sufficient for proper performance analysis.

Editor Detail Drilldown

For statements

Platform SQL Text SQL Details Events Sessions Children Details Object I/0
Oracle yes yes yes yes yes yes
Sybase yes (planned 2.5) yes yes
DB2 yes yes yes yes
SQL Server yes (planned 2.5) yes yes

For events:

Platform SQL Sessions Raw Data Analysis
Oracle yes yes yes Only for “buffer busy waits” “cache buffer chains latch”
Sybase yes yes
DB2 yes yes
SQL Server yes yes

For Session:

Platform Session Details SQL Events
Oracle yes yes yes
Sybase yes yes yes
DB2 (planned 3.0) yes yes
SQL Server yes yes yes


Platform Bind Variable Support Index Analysis Table Statistics Column Statistics Outlines/Abstract Plans VST
Oracle yes yes yes yes yes yes
Sybase (planned 2.5) yes (3.0 or 3.5) (3.0 or 3.5) (3.0 or 3.5) yes
DB2 ? yes (3.0 or 3.5) (3.0 or 3.5) (3.0 or 3.5) yes
SQL Server (planned 2.5) yes (3.0 or 3.5) (3.0 or 3.5) (3.0 or 3.5) yes