Re: Stale statistics

From: Mark D Powell <>
Date: Tue, 30 Jun 2009 06:04:16 -0700 (PDT)
Message-ID: <>

On Jun 30, 8:29 am, Nag <> wrote:
> Hi,
> We have OLTP system on 10gR2 with RAC (3 nodes) with lot of active
> tables. Means deletes, inserts and updates. We are only gathering 1%
> sample statistcs. After bringing the system for couple of hours all
> the statistics are becoming stale. All our queries use bind varaibles
> and cursor sharing parameter is set to EXACT.
> Because of this only the first time we do hard parse and the plan is
> set. After that though the statistics are stale, it is still using the
> same plan.
> What kind of impact will be on the system with stale statistcs?
> Any input will be appreciated.
> Thanks,
> Naga

On 10g Oracle provides a dbms_scheduler job that automatically gathers statistics. Did you disable it or modify it in order to do the 1% sample size? The default sample size is auto which means the optimizer picks one based on what it finds.

By default on 10g histograms are collected. I do not think a 1% sample will result in very good histograms.

How are you determining that the statistics are stale after only a couple of hours?

HTH -- Mark D Powell -- Received on Tue Jun 30 2009 - 08:04:16 CDT

Original text of this message