Re: stale stats

From: Mark D Powell <>
Date: Wed, 19 Jan 2011 06:55:12 -0800 (PST)
Message-ID: <>

On Jan 17, 8:32 pm, "RF" <> wrote:
> Is there any link to those options you provided below tied to locking plans
> ? I know of hints.. but the others are new to me.
> We do know that we have some massive data inserts at times and right now we
> dont have any procedures in place as far as when to collect stats.. we have
> tons of nightly jobs that run and is it best to collect stats after every
> job run ?
> "Mark D Powell" <> wrote in
> On Jan 16, 4:10 pm, "RF" <> wrote:
> > We keep running into suboptimal query plans with 11g and then we run into
> > slow queries and once we collect stats, things get better again. How can
> > we
> > ensure our plans dont change as such ?
> > Please advice. It is very frustrating and leads to downtime of our apps..
> You should look at the plans and compare the before and after
> statistics to see why the plan is changing.  If you have an index on a
> column which constantly have new values inserted on one end of a value
> range while having the other end of the value range deleted and
> histograms exist on the column the histograms can result in misleading
> information to Oracle since the histograms can quickly become out of
> date.
> To lock a plan in depending on Oracle version (11g in your case) and
> purchased options you can use hints, Outlines, SQL Profiles, or
> Adaptive Cursor Sharing to deal with the statements in question.  When
> you have access to the SQL I like hints since their presence in the
> code is visible to future developers/DBA's to see.
> HTH -- Mark D Powell --

If you are currently running "massive" inserts at irregular intervals then updating the statistics immediatly after such inserts may well be a good idea at least until such time as the amount of data inserted is not longer relatively a significant percentage of the data.

Locking and unlocking statistics using dbms_stats is covered in the PL/ SQL Types and References Manual while Adaptive Cursor Sharing is discussed in the Performance and Tuning manual.

HTH -- Mark D Powell -- Received on Wed Jan 19 2011 - 08:55:12 CST

Original text of this message