Oracle Database11g SQL Performance Advisor

The declarative nature of SQL syntax has always made it difficult to perform SQL tuning. The basic tenet of cost-based SQL optimization is that the person who writes a SQL query simply "declares" what columns they want to see (the SELECT clause), the tables where the columns reside (the FROM clause), and the filtering conditions (the WHERE clause). It's always up to the SQL optimizer to determine the optimal execution plan. This is a formidable challenge, especially in a dynamic environment, which is why Oracle introduced the 10g new feature of CBO dynamic sampling.

More on Oracle Oracle Database 11g tutorial Oracle Database 11g comes through the channel

Oracle tuning consultants have know for many years that the best way to tune an Oracle system is to take a top-down approach, finding the optimal configuration for external factors (OS kernel settings, disk I/O subsystem) and determining the best overall setting for the Oracle instance (init.ora parameters).

Holistic tuning involves tuning a representative workload, adjusting global parameters in order to optimize as much SQL as possible. Only then is it prudent to start tuning individual SQL statements. Many Oracle professionals who adopt a bottom-up approach (tuning the SQL first) find all of their hard work undone when a change is made to a global setting, such as one of the SQL optimizer parameters or recomputing optimizer statistics. Oracle's holistic SQL tuning approach is new, and many Oracle professionals find it difficult to embrace, but this is about to change. The Oracle 11g SQL Performance Analyzer (SPA) is primarily designed to speed up the holistic SQL tuning process, automating much of the tedium.