Re: stale stats

From: onedbguru <onedbguru_at_yahoo.com>
Date: Mon, 17 Jan 2011 14:18:20 -0800 (PST)
Message-ID: <43520886-2212-4455-9d65-26e0fbed6cd6_at_q18g2000vbk.googlegroups.com>

On Jan 17, 2:43 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> RF:
>
> # 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..
>
> No silver bullets but a ton of very powerful techniques and potential
> useful solutions these days with 11g.
>
> You won't get very far though without investing the time and energy
> into coming up to speed technically with what is going on.
>
> Work on a short term strategy and long term strategy for how you are
> going to manage and maintain optimizer statistics!

I have had at least one instance where the table was partitioned and the new partitions with no stats performed very badly. By just adding stats with 0 (zero) values the query plans would go back to "normal".

What the OP may need to do is run a nightly or weekly job to gather the stats - depending in the rate of change in the table (inserts, deletes etc..). Received on Mon Jan 17 2011 - 16:18:20 CST