Re: Stale statistics
Date: Tue, 30 Jun 2009 06:04:16 -0700 (PDT)
On Jun 30, 8:29 am, Nag <naga.cha..._at_gmail.com> wrote:
> 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.
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