How often to reanalyze statistics Tips

Question:  I have analyzed my database using dbms_stats but I don't know how often I need tore-analyze by statistics.  All of my execution plans are good, and I need direction on the optimal frequency for re-analyzing dbms_stats for table and indexes.  What is the optimal interval for running dbms_stats?

Answer: There is the old saying "if it ain't broke, don't fix it", and so long as your tables remain "stable", a single deep sample may be sufficient.  How often to reanalyze statistics depends upon two factors, the time required to re-analyze the schema and he rate of change of the schema data.

• If analyzing a schema took zero time nor resources, you would want to re-analyze frequently, so that as soon as changes to the table metadata was detected, the execution plan would change.  If course, analyzing a schema can cause a tremendous overhead, which is why we use estimate_percent = auto_sample_size (in 11g and beyond).
  
• If a table never changed, there would be no need to ever re-analyze schema statistics.  However, most OLTP table tend to remain stable, with large tables remaining large and small tables remaining small, with data value distributions being roughly the same.  In these types of databases, we must remember that the main change from re-analyzing tables are changes to execution plans!

  TEMP tables which are constantly changing wildly can use dynamic sampling

In sum, the optimal frequency for re-analyzing optimizer statistics ids a function of the tale/index sizes and the rate of change to the tables.

Also see re_analyze tips and Oracle dbms_stats tipd