Q: What is DB Optimizer XE2?

A: Embarcadero DB Optimizer XE2 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 XE2 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.

Q: What is the difference between profiling and tuning?

A: 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 XE2 provides both profiling and tuning for Oracle, SQL Server, DB2 LUW, and Sybase within a complete SQL IDE. DB Optimizer XE2 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.

Q: What are Explain Plans and how do they help?

A: 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 XE2 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 XE2's execution statistics table without actually executing the query - this comes in handy with queries that take hours to return results.

Q: Can DB Optimizer XE2 help with packaged applications with database backends?

A: Absolutely. There are two main ways DB Optimizer XE2 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 XE2'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.