Re: execution plan and poor statistics

From: Jonathan Lewis <>
Date: Tue, 12 Apr 2011 16:36:41 +0100
Message-ID: <>

"onedbguru" <> wrote in message This is an easy fix. Stop collecting statistics on the table. - with a caveat.

  1. while the table is full - gather statistics - do this one time
  2. save these statistics somewhere
  3. when your table is full again, restore those statistics.
  4. If you never run stats against it again, then it is unlikely that you will collect stats while the table is empty thereby averting your problem.

Doesn't necessarily work - sometimes you have to keep updating the states if you want the plans to stay the same.
This is a side effect of the "linear decay" algorithm that the optimizer uses to pro-rate statistics when the query predicates move outside the low/high column values. If the user has time-based (or sequence-based) data then there may be cases where (in the absence of hinted paths) he will see execution plans change as time passes.


Jonathan Lewis
Received on Tue Apr 12 2011 - 10:36:41 CDT

Original text of this message