Oracle Scratchpad

January 28, 2022

Execution Plans Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:47 pm GMT Jan 28,2022

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
  •  

1 Comment »

  1. […] Execution Plans catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — January 28, 2022 @ 3:39 pm GMT Jan 28,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Website Powered by WordPress.com.

%d bloggers like this: