Re: Stale statistics
Date: Tue, 30 Jun 2009 10:35:18 -0700 (PDT)
On Jun 30, 5: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.
In a nutshell, whether it makes a difference depends on if the statistics that exist result in the correct plan being chosen for all the accesses of the data.
In a practical sense, this means you can either let the system gather statistics when it thinks things are stale, you can gather statistics when you think they should be gathered, or you can figure out the best statistics and lock them down. The latter is preferred, because you don't have so many emergencies operationally. It may be expensive to implement, though.
If your site does things that are normal from the point of view of the assumptions the staleness makes, it may be reasonable to just use the default gathering (with the caveat about histograms being collected), and just deal with problems as they arise. This worked ok for me for a while, until a vendor started making metadata changes while I wasn't in, and the subsequent gathering blew those all to hell. So I reverted those to the old stats and locked them down - score one for EM, as far as I'm concerned.
Since you've stated your tables are very volatile and we can guess there must be some legitimate reason you chose the 1% sampling, it sounds like it would be worth it for you to lock those statistics, document all plans accessing those tables, and research any performance problems and respond accordingly. This would be considered best development practice, anyways.
There's been a lot of debate on the web about when to gather stats. I believe people have written blogs or white papers about it but don't have time to look just now.
-- _at_home.com is bogus. If at first you don't succeed, you're not Chuck Norris.Received on Tue Jun 30 2009 - 12:35:18 CDT