Harnessing the power of Oracle Database optimizer hints
summary
Shows when hints truly control plans and when they don’t: hints are always followed only if applicable (example: FULL on a subquery is ignored unless placed in that query block), hash joins require equality predicates, and index/parallel combos must be valid. Demonstrates using DBMS_XPLAN.DISPLAY_CURSOR +ALLSTATS LAST and +alias to see E-Rows/A-Rows and query block names, extracts a full plan with +outline, and recommends SQL Plan Management and the _OPTIMIZER_IGNORE_HINTS debug knob for upgrades.
AI-written summary · about
chapters (10)
- 0:00Introduction to hints
- 0:58What are optimizer hints
- 4:43Optimizer hint categories
- 8:49Implementing hints correctly
- 12:24Managing complex query blocks
- 14:46Troubleshooting unused hints
- 17:21Achieving plan stability
- 21:07Advanced optimizer modes
- 26:47Common hint pitfalls quiz
- 31:05Final takeaways
related talks
20mElevating SQL Server Performance with Query Store Hints
29mExplaining the Postgres Query Optimizer
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)- 20mElevating SQL Server Performance with Query Store Hints
26mBe Good Friends with Query Planner
26mBeyond Joins and Indexes
51mAdvanced Plan Explorer Usage for Tuning Execution Plans
51mExplaining the Postgres Query Optimizer