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:
- Number of Query Optimizer Parameters by Release
- Number of Query Optimizer Bugs Fixed by Patchset
- 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
- Plan Stability
- 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)
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?
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.