DBO Optimizer Versions
Contents
Versions
1.0 July 2008
- SQL Profiling (cross platform)
- Load Chart
- Top SQL
- 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 )
- index,
- table statistics
- column frequency
- Outlines tab - stored hints (Oracle only)
- Profiling
- 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
- Profiler
- Continuous profiling (as opposed to a predetermined profiling period)
- Cross platform SQL and Session details
- Tuner
- 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.
- Profiling:
- 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)
- Tuning:
- 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 10.2.0.3 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 |
Tuner
| 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 |