DB Optimizer

I’ve rediscovered this list of articles by Stefan Koehler on my laptop; it has a bias towards SAP users, and it’s several years old judging by the dates so some details will have changed, but there still seem to be plenty of people using 12c (and SAP) so I thought I’d tidy it up and publish it

• Part 0: Analyze vs dbmsstats or How to confuse the optimizer
• Part 1: Understanding FOR ALL ENTRIES and its processing by sap dbsl / Oracle
• Part 2: Extending execution plans or How to identify the bad one
• Part 3: Extending and tuning of execution plans in practice
• Part 4: What the heck! Troubleshooting why hints are not considered
• Part 5: Introduction to dynamic sampling and why it is used in SAO BI environments by (SAP) default
• Part 6: Effects of disabling bind variable peeking, adaptive cursor sharing, and cardinality feedback on the cbo in SAP environments
• Part 7: Looking under the hood of adaptive query optimization / adaptive plans (Oracle 12c)
• Part 8: Looking under the hood of query transformation (by the CBO) with simple real life example
• Part 9: Session specific statistics for GTT (global temporary tables) with Oracle 12c
• Part 10: Looking under the hood of adaptive query optimization / adaptive statistics – sql plan directives (Oracle 12c)
• Part 11: Query transformation fixes false cardinality estimation with multiple OR predicates (in newer Oracle releases)
• Part 12: Revealing sql plan directive details for existing loaded cursor from cbo (and sql dynamic sampling services) trace