Execution Plans Catalogue

This is a list of all articles I’ve written discussing execution plans. Each entry has a date stamp and a short note of the contents. The articles are (may be / will be) grouped in a couple of different categories – “long case studies” and “short notes” are a couple of obvious classes –  but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

• Case Study (Sept 2022): an example of tkprof showing you which of several scalar subqueries might be worth a tweaking.
• Upgrade issues (July 2022): an example of how new transformations can change plans that had been hinted as completely as possible.
• Pagination Costs (July 2022): select with offset N next M can change plans as N and/or M vary.
  • Fetch Fetch and FBIs (June 2019): a error of omission (with workaround) in the optimizer’s treatment of FBIs with Fetch First / Top-N (last tested 19.11)
  • Fetch First under the covers (July 2013): How does Oracle transform Fetch First / Top-N before optimising – should be retested for newer versions.
  • Parallel Fetch First under the covers (July 2013): How effective is the optimizer with Fetch First / Top-N when executing in parallel
• Index Wildcard (July 2022): Looking closely at Predicate Information to understand what cursor_sharing has done to an execution plan.
• Case Study (June 2022): an example of the SQL Monitor report showing that a no_merge hint was the appropriate solution
• Case study (Dec 2021): Looking at the right execution plan in tkprof output.
• Case study (Oct 2021):A problem arising from adaptive plans and first_rows_N optimisation.
• Bind variables and ranges (Oct 2021): if you use bind variable to define ranges in query predicate make sure they are in the right order
• qb_registry (Aug 2021): Part 1 of a pair of articles examining the qb_registry option of dbms_xplan.display_xxx()
  • qb_registry (Aug 2021): Part 2 of a pair of articles examining the qb_registry option of dbms_xplan.display_xxx()
• Hash Joins (Dec 2020): How memory is used during multi-table hash joins
• Interval partition predicate oddity (Oct 2020): Interval partitioning leaves in predicates that “normal” range partitioning can remove.
• Quiz Night 30 (June 2016): What’s the clue that this plan came from 12c?
• Explain Plan (2014-2015): index to a series of articles that I wrote for Simpletalk
• Old Plan_table (Jan 2010): why dbms_xplan.display() might have a note: ‘PLAN_TABLE’ is old version
• Rowsource order (Mar 2007): A nested loop will return results in a different order from a hash join – and this can make a big difference to query performance.
• Predicate Section (Dec 2006): you can’t work out how good the plan is if you don’t report this part of the plan
•