Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Challenges and Chances of the 11g Query Optimizer

12 December 2011 2 Comments Written by Christian Antognini

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)
11gR1, 11gR2, Bug, Indexes, Object Statistics, Query Optimizer, Speaking, System Statistics
Ad: Trivadis TechEvent 2011
Ad: The Oracle Query Optimizer 2-Day Seminar

2 Comments

  1. Vijay Vijay
    16 August 2012    

    Hi,

    Regarding Adaptive cursor sharing:
    1)After how many ineffecient executions by reutilizing old cursor,does
    the oracle engine/optmizer choose to switch to new plan/child cursor?
    2)Where is the information stored to know about wrong estimates?

    Reply
    • Christian Antognini Christian Antognini
      16 August 2012    

      Hi Vijay

      According to my observations (since I’m not aware of an “official statement”) when the number of inefficient executions is equal to the number of efficient ones. I observed this through v$sql_cs_histogram.

      HTH
      Chris

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.