Re: Stale statistics

From: Nag <naga.challa_at_gmail.com>
Date: Tue, 30 Jun 2009 07:15:54 -0700 (PDT)
Message-ID: <9c349d56-b886-4f73-8a62-47c2ede30434_at_z9g2000yqi.googlegroups.com>

On Jun 30, 9:04 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

user_tab_statistics/dba_tab_statistics with column name STALE_STATS = 'YES' Received on Tue Jun 30 2009 - 09:15:54 CDT