What is DB Optimizer?

Embarcadero DB Optimizer is a SQL profiling and tuning IDE that maximizes database and application performance by enabling DBAs and developers to quickly discover, diagnose, and optimize poor-performing SQL. DB Optimizer eliminates performance bottlenecks by identifying data intensive or frequently executed queries, focusing on specific SQL statements through query statistics (CPU, I/O, wait times), and fine-tuning problematic statements.

What's New in DB Optimizer 2.0?

DB Optimizer 2.0 takes SQL tuning beyond standard hint injections and SQL rewrites and offers innovative, new features for faster and more detailed SQL tuning and analysis.

• The Visual SQL Tuning (VST) diagram, which can be displayed in either Summary Mode or Detail Mode, displays indexes and constraints on tables and views, as well as the joins used in a SQL statement such as Cartesian joins, implied Cartesian joins and many-to-many relationships.
• The color-coded Index Analysis feature let DBAs and developers fully examine SQL execution paths to better understand which indexes are used (green), not used (blue), or missing (orange). If an index is missing, DB Optimizer will offer indexing recommendations for optimum performance.
• The Load Editor does SQL stress testing simulates  a number of parallel users and executions over a specific period of time or execution cycle
• Profiling Details of the SQL Profiler have been expanded to show Session Details for Sybase, and SQL Server and the SQL that ran in the selected session for Sybase, SQL Server, and DB2

What is the difference between profiling and tuning?

Profiling is the process of analyzing a single SQL statement or an entire database to determine which queries should be focused on for improvement. In some cases these may be long-running queries. In other cases they may be queries that execute repetitively. Both are worth the effort to try and improve overall performance. That brings us to tuning.

You wouldn't try to tune an entire database all at once so it's advisable begin the optimization process with profiling to find the problem areas first. Once you have pin-pointed the problem statements you can begin tuning. Tuning is the process of actually making changes: studying a number of different options (known as cases), the "costs" of each option, and implementing the case that provides the best execution plan. DB Optimizer provides both profiling and tuning for Oracle, SQL Server, DB2 LUW, and Sybase within a complete SQL IDE. DB Optimizer also provides batch tuning of DML statements, stored routines, and entire SQL files, producing a number of execution plans, displayed on a grid, with detailed statistics for each case. The best case can easily be selected to automatically replace the existing poor-performing SQL statement.

What are Explain Plans and how do they help?

Explain plans are used to understand what execution plan a SQL statement is actually using. With this information, the user can determine if a certain plan is the optimal execution plan. DB Optimizer displays this information as a tree with columns and collapsible column groups to make it easy to follow. Explain plans can be calculated based on DB Optimizer's execution statistics table without actually executing the query - this comes in handy with queries that take hours to return results.

Can DB Optimizer help with packaged applications with database backends?

Absolutely. There are two main ways DB Optimizer can help. One is to use the profiling capabilities to provide feedback to the application vendors on what exactly is causing the performance problems. The second is to use DB Optimizer's stored outline feature. After running a tuning job, you can automatically generate the DDL to create a stored outline for the optimal execution plan. Once this is placed on the database, SQL statements from the packaged application will follow the optimized execution plan.