Re: Stale statistics

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 30 Jun 2009 06:04:16 -0700 (PDT)
Message-ID: <7fc56b6b-9dd6-4054-91e1-0b6e896cfa07_at_33g2000vbe.googlegroups.com>

On Jun 30, 8:29 am, Nag <naga.cha..._at_gmail.com> 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