11G Statistics Collection
Date: Wed, 11 Apr 2012 16:39:30 +0000 (UTC)
Message-ID: <jm4c41$s2l$3_at_solani.org>
A very, very long time ago, I wrote the following article:
http://www.dba-oracle.com/t_gogala_cbo_oltp2.htm
In that article, I wrote the following:
So, how necessary periodic statistics computation really is? To make the long story short, it isn't. Every statistics re-computation will change execution plans, sometimes quite unpredictably. What is the purpose of having an optimizer statistics? The purpose of statistics c is to have a good input values for the CBO to be able to come up with intelligent plans. As long as the statistics reflects realistic relationships among the object sizes, it doesn't need to be changed.
The so called “Ensor's paradox” (named after Dave Ensor, famous Oracle DBA and an Oak Table member) states that the only time when it is safe to collect statistics is when the statistics is not needed. Essentially, following the usual business logic, after each statistics computation, the system should undergo an extensive QA test, to make sure that the overall performance is still acceptable. Collecting statistics is something that should be done rarely, only when the data volume changes significantly.
The only thing I would retract is Dave Ensor's Oak Table Net membership. Apparently, he isn't a member. There is, however a very significant new mechanism to avoid collecting stats when that is not necessary. In Oracle 11G, Oracle allows setting preferences, on the per table basis, for statistics collection. One of these preferences is STALE_PERCENT which defines the percent of data that needs to be changed for the table stats to be considered stale and table stats re-gathered during the next statistics gathering. The default value for this parameter is 10. That parameter allows manipulating how frequently will the stats be collected. If the percentage is set to 99, the new stats will not be gathered for a very, very long time. There are tables where we want the stats to be as accurate as possible. For those, we can set the stale percentage to 1, which means that the stats will be gathered very frequently. However, it is still very important to know thy data and make an informed decision about when to gather stats. Every time stats is gathered, an element of unpredictability enters the system. There is no guarantee that all SQL will continue running as it was. QA of the stats on a similarly sized staging system looks like the only reasonable, albeit very expensive, option.
-- http://mgogala.byethost5.comReceived on Wed Apr 11 2012 - 11:39:30 CDT