Wednesday, October 21, 2009

DB Optimizer

DB Optimizer makes powerful use of graphics to communicate data faster , clearer and with less effort by the user than text representations. (see

DB Optimizer has three key factors
  1. Provides clear tuning methodology
  2. Opens up communication between QA, DEV and DBAs
  3. Seamless integration of 4 major functionalities
1. Methodology:
  • profile database
  • identify bottlenecks
  • if bottleneck is a SQL, send to tuner
  • identify best execution path, proper indexes, correct SQL, correct schema definitioins
  • load test new sql verses old and profile to verify improvement
go to back to profiling and see if load is under the max CPU line. If not tune the next highest SQL.
Without a methodology we find people misdirected and often afflicted with "compulsive tuning disorder" (as my esteemed colleague Gaja put it).
  • Profiler identifies any performance issues and quantifies the magnitude.
  • Tuner identifes any issues with SQL
  • Load tester does a sanity check on the performance and tests concurrent users impact of SQL.
2. Communication
An under appreciated area of development is communication between teams such as development and DBAs. DBAs complain about bad coders, coders complain that the database is slow. Who is right? The answer is clear with DB Optimizer. DB Optimizer's profiler shows clearly whether the issue is a badly written SQL or a database configuration issue. If the problem is database configuration, the the developer can email the profiling session (a flat file containing an HSQL database of data) that the DBA can open and quickly identify and solve any database issues. If the the DBA find a bad SQL the DBA can send the saved profiling session to the developer indication the impact of the SQL on the system. The SQL can be quickly analyzed and tuned in the SQL tuner, and the developer can sanity check the new SQL by load testing it and comparing the load of the new SQL to the old SQL.
DB Optimizer is graphical and simple making communicaiton of issues between development, QA and DBAs efficient and clear.
If QA runs into a performance problem they can email the saved profile session to the DBA who can review the issues offline. The save profile session is interactive allowing the DBA to drill in on spikes and drill down on SQL, Sessions or Events to see exactly what the issues were on any time frame from 5 seconds up to hours wide.
I've seen time spent on meetings go from hours over weeks to a single quick meeting to pin point clearly problem issues and allocate resources to address the issues. Without a clear presentation of the bottleneck and precise identification of the causes, meetings tend to become arguing matches. Once there is a clear view, thanks to DB Optimizers profiling page, the decisions on whether to configure the database, add more CPU, tune application SQL or change schema design become clear. Allocating resources to address these issues can be costly and thus the information used to makes these decisions has to be clear, reliable and understandable by all parties. Try showing a statspack report to a general audience and see the eyes glaze over. Now show a DB Optimizer profile report and everyone comes to attention and clearly the contents are understand and consensus is reached quickly. (see previous post on text verses graphics : )

3. Integration
DB Optimizer seamlessly integrates in a single product profiling, tuning, load testing and SQL IDE with datasource browser. No other product on the market fully integrates all this functionality. For example at Quest to have the same functionality you'd have to have Toad, Performance Analyzer , SQL Optimizer and Benchmark Factory. At Oracle you'd have to SQL Developer, Diagnostics Pack, SQL Tuning Pack and Real Application Testing (RAT) to the tune of $15,000 per CPU per database*. For example a 2 database each on a 4 CPU machine would be 2*4*15,000 or $120,000 for the Oracle solution, where as they would be $1500 for DB Optimizer!

*Oracle recently raised Diagnostics Pack and SQL Tuning Pack from $3000 to $5000 per CPU.
The price for Real Application Testing is unclear to me, but reports I've read put it on the same order as Diag and Tuning packs.
Of course OEM Diag, Tuning, and RAT do more than DB Optimizer but that also makes them harder to install, harder to use, more sluggish, less interactive and more difficult to manage resulting in a tool that one doesn't typically give to developers. Developers get left out of the benefits of load profiling, load testing and sql tuning with OEM packs and RAT but now with DB Optimizer, developers and QA, as we well as DBAs can have quick easy access to load profiling, load testing and SQL tuning.

No comments:

Post a Comment