Previous installment: Why is it so hard to get SQL performance right the first time?
First installment: DON’T PANIC

“I know a funny little man,
As quiet as a mouse,
Who does the mischief that is done
In everybody’s house!
There’s no one ever sees his face,
And yet we all agree
That every plate we break was cracked
By Mr. Nobody.
The finger marks upon the door
By none of us are made;
We never leave the blinds unclosed,
To let the curtains fade.
The ink we never spill; the boots
That lying round you see
Are not our boots,—they all belong
To Mr. Nobody.”

—Anonymous

If the inputs to the CBO change, the plan can change. For example, changes to bind variables can cause the plan to change when it is hard parsed. Therefore one should never be surprised when plans change. Here are ten possibilities.

  1. Changes to bind variables caused the plan to change when it was hard parsed.
  2. The table, index, or system statistics changed. Small changes to statistics can cause big changes to query plans.
  3. Dynamic sampling is being used.
  4. A change has been made to the schema.
  5. You have installed patches or upgraded the Oracle Database software.
  6. Some of the parameters that influence the optimizer have changed. This includes the parameters that control the size of the PGA because they impact the amount of memory that can be used for hash joins and sorting.
  7. Auto-DOP (degree of parallelism) is in effect.
  8. Cardinality feedback is in effect.
  9. Adaptive optimization is being used.
  10. VPD (Virtual Private Database) is being used.

What would be surprising if the plan changed if none of the CBO inputs changed. Richard Foote demonstrates how that can happen at https://richardfoote.wordpress.com/2010/02/16/how-does-an-execution-plan-suddenly-change-when-the-statistics-remain-the-same-in-limbo/.

Previous installment: Why is it so hard to get SQL performance right the first time?
First installment: DON’T PANIC

Copyright © 2015 Iggy Fernandez