Recent site activity

Resources‎ > ‎

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      Sep 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      Released May 19, 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 linenumber 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 Jan 2011

     Reports, Alerts, Email
     Enhancements to VST diagram
     Small changes to improve profiling

3.5 targeting end of Q2 2011


Functional offering Matrices as of May 2010 (DB Optimizer 2.5):

Profiling SQL tab enhancements 

Platform

Average SQL exec time

Show procedure content

Show executed line in procedure

 average wait
    time
 

Oracle

yes

(planned OPT-2150)

(planned 3.0 for 10.2.0.3 and higher )

 
    yes
 

Sybase

(planned 3.0)

OPT-1963

yes

(planned)

  

DB2

yes

(planned OPT-2137  )

(planned )

  

SQL Server

yes

yes

(planned )

  

For procedure/line#, what do we want exactly
- When user selects parts of a chart, and selects a procedure form main procedures tab, in the profiling details/sql text the executed part will be highlighted
- When user selects a statement from Overview or SQL, he will get in profiling details/procedure the procedure name and a column with line in the procedure


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

yes OPT-1538 Data added to main SQL tab (too few fields)

yes

yes

 

 

DB2

yes

yes

yes

yes

 

 

SQL Server

yes

yes OPT-1536 Data added to main SQL tab (too few fields)

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 OPT-1378 )

yes

yes

SQL Server

yes

yes

yes



Tuner


Platform

Bind Variable Support

Index Analysis

Table Statistics

Column Statistics

Outlines/Abstract Plans

VST

  Exist/In/Between 
 re-writes   

Oracle

yes

yes

yes

yes

yes

 yes

 
  yes

Sybase

yes

yes

(3.0 or 3.5)

OPT-1386

(3.0 or 3.5)

OPT-1387

 (3.0 or 3.5)

Abstract Query Plans

yes 
  ?

DB2

?

yes

(3.0 or 3.5)

OPT-1386

(3.0 or 3.5)

OPT-1387

 (3.0 or 3.5)

 yes

   
   ?

SQL Server

yes

yes

(3.0 or 3.5)

OPT-1386

(3.0 or 3.5)

OPT-1387


 (3.0 or 3.5)

OPT-1387

OPT-2226 create outlines for SQL Server using SP_CREATE_PLAN_GUIDE

yes     ?





DBO 1.5 DBO 2.0 DBO 2.5 DBO 2.5.1  (same as 2.5 if not specified)
General





Load Generation no
yes yes

SQL IDE yes yes yes

Debugging no no no

Eclipse 3.5 compatibility no no yes

indexing improvements no no yes






Profiling
yes yes yes

average SQL exec time no no Oracle, DB2, SQL Server

average wait time no no Oracle

show procedure content no no Sybase, SQL Server

statements SQL details Oracle Oracle, DB2 Oracle, DB2, Sybase, SQL Server

max cpu no no yes

profiling repository no no Oracle

profiling optimisations no no yes






Tuning
yes yes yes

bind variable support Oracle Oracle Oracle, Sybase, SQL Server

index analysis Oracle Oracle, DB2, Sybase, SQL Server Oracle, DB2, Sybase, SQL Server

new transformations/query rewrites no no yes

editor updates no yes yes

save jobs details
no no yes

datasources support added NA SQL Server 2008 DB2 for LUW 9.5






VST
no yes yes

filter ratios NA no yes

indexing improvements NA no yes

sub-queries support NA no no yes

new relations support NA no no yes

saved in tuning job NA no no yes



Future Directions
Comments