Challenges and Chances of the 11g Query Optimizer

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following:

With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.

The aim of this short post is to point out that I made available the current version of the slides and all the scripts that go with them here.

The structure of the presentation (incl. a reference to the available scripts) is the following:

• Observations
  • Number of Query Optimizer Parameters by Release
  • Number of Query Optimizer Bugs Fixed by Patchset
• Indexing
  • Invisible Indexes (ex_invisible_index.sql)
  • Index Support for Linguistic LIKE (ex_linguistic_like.sql)
  • INDEX REBUILD and Statistics History (ex_index_rebuild.sql)
• Optimization Techniques
  • Full Outer Join (ex_full_outer_join.sql)
  • Join-Filter Pruning (ex_join_filter_pruning.sql)
  • Table Expansion (ex_table_expansion.sql)
  • Join Factorization (ex_join_factorization.sql)
  • OR Expansion (ex_or_expansion.sql)
  • Join Elimination (ex_join_elimination.sql)
  • Subquery Unnesting (ex_subquery_unnesting.sql)
• System and Object Statistics (DBMS_STATS)
  • Workload System Statistics
  • Object Statistics – Default Preferences
  • Object Statistics – Auto Sample Size
  • Object Statistics – Pending Statistics (ex_pending_object_statistics.sql)
  • Object Statistics – Incremental Statistics (ex_incremental_stats.sql)
  • Object Statistics – Extended Statistics on Expressions (ex_extended_statistics1.sql)
  • Object Statistics – Extended Statistics on Column Groups (ex_extended_statistics2.sql)
  • Object Statistics – Seeding Column Groups
  • Object Statistics – Comparing Statistics (ex_comparing_statistics.sql)
  • Object Statistics – Locks not Exported
  • JOB_QUEUE_PROCESSES
• Plan Stability
  • CURSOR_SHARING
  • SQL Plan Baselines (ex_execution_plan_stability.sql, ex_execution_plan_stability_10g.sql, ex_execution_plan_stability_11g.sql)
  • Stored Outlines
  • Adaptive Cursor Sharing (ex_bind_peeking.sql, ex_bind_peeking_bind_aware.sql)
  • Cardinality Feedback (ex_cardinality_feedback.sql)