New Oracle 11g SQL tuning tool

Oracle has lifted their non-disclosure to the 11g Beta testers, and we are now starting to see details about the amazing new features of Oracle 11g.  One of the hottest new tools is the 11g SQL Performance Analyzer (SPA).

Oracle has made exciting headway with SQL profiles in Oracle 11g by "closing the loop" and allowing the SQL profiles to be automatically implemented. Traditionally, Oracle has tuned SQL by making recommendations (i.e. recommending new indexes, recommending new materialized views), but the Oracle 11g automated SQL tuning takes this one step further, with a holistic workload-based approach to SQL tuning.

In Oracle 11g fully automated SQL tuning, the DBA defines a representative workload, and Oracle tests the work load empirically, against the database. Instead of using theory and calculations, Oracle tests the SQL in a real world environment running it repeatedly and determining heuristically the optimal execution plan for the SQL. Oracle then builds SQL profile and implements it directly.

The challenge of tuning SQL

The declarative nature of the SQL access 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 up to the SQL optimizer to always determine the optimal execution plan, a formidable challenge, especially in a dynamic environment.


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 (i.e. OS kernel settings, disk I/O subsystem), and determining the best overall setting for the Oracle instance (i.e. init.ora parameters). I've been advocating holistic tuning for many years, and I go into great detail in my book "Oracle Tuning: The Definitive Reference".

Holistic tuning involves tuning a representative workload first, 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 professional who adopt a bottom-up approach (tune the SQL first), find all of their hard-work un-done when a change is made to a global setting, such as one of the SQL optimizer parameters.

Continue reading here:


Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.


Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link: