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.
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 profiling for Oracle, SQL Server, DB2 LUW, and Sybase, and tuning for Oracle within a complete SQL IDE. DB Optimizer also provides batch tuning of DML statements, stored routines, entire SQL files, and the System Global Area, 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.

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 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.

A: 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.