Oracle Execution Plan: Stability or Lability?

From statistics derived from data distribution and hardware performance measurements, and the SQL to be executed, the Oracle Cost Based Optimiser (CBO) is the pivotal Oracle component responsible for determining the execution plan – the sequence and type of operations that the Oracle DBMS will use to retrieve data in the most most efficient manner, where most efficient is a function of time. Sometimes the CBO gets the execution plan right, sometimes it does not, and sometimes it just gives up and the plan used is the one that is deemed the most efficient thus far.

This house of cards is all built upon statistics (ie. not facts, but glorified coin flipping). Furthermore

• in the case of an OLTP system, the source statistical data is almost always stale. To put this another way, a database transaction is performed, the data changes, and the underlying statistics derived from this data and used by the CBO do not immediately change/are not recalculated on the fly
• the execution plan used for a prior execution of the same piece of SQL, if available, is used even if the SQL is not the same (this is not a contradictory statement – see Tanal Poder whinging about this in his blog for example)
• the tiniest of data updates, some edge-case in data distribution, can tip the CBO into a selecting a suboptimal execution plan from this point on thereby resulting in a drastic change in performance for some database queries (once fresh statistics are gathered or sampled).

Much has been written on Oracle Execution Plan Stability, a term used to describe approaches to preserving the execution plan, or limiting changes to the execution plan, as a result of changes in the underlying data. Despite my opinions on using statistics to determine access path/execution plans in the way it has been implemented, and the potential for this rapid change in performance (as a result of choosing a different execution path), I believe the terminology used is wrong, and I finally come to the point of this brief blog article.

When I think of a chemical compound (or nuclear reactor) as being unstable, my first thoughts are that it is going to go bang! When I think of some software system or component being stable/unstable, I think of it as being robust/crashing and buggy. Despite execution plan stability being a hot topic in the Oracle world, the word stable is not the right one to choose, in my opinion. Changes to the execution plan after all don’t cause the Oracle to crash or lose data! Or cause fires (OK, it is starting to show now that I was once a Synthetic Organic Chemist; fires, bangs, and pops were very common – I often thought I didn’t need fire drills/exercises as I had the real thing, and often). I believe the right term is lability; the execution plan is prone to change (it is a design decision – it has been designed to change) as a result of the logic encapsulated within the CBO and underlying statistics derived from the data distribution and hardware performance (measured times for single/multi-block read-writes etc).

In upcoming blog articles I will be using the words labile and lability, and not stable and stability. It is a fine point, but I am paving the way for what I intend to write in the future.

— Published by Mike, 13:24:04 25 April 2017 (CET)