RE: statistics stability

Hi  

I think that scheduling weekly/monthly statistics has it's uses, but there are numerous tables that do not or hardly ever change dramatically and do not need regular statistics gathering.

I tend to look at the schema and if most tables need regular statistics I might just analyze the schema and if most do not,I script for just the tables that do need it.  

Niall has a good point but I thought that is only if histograms are used and you are not in this case  

Jack  

Van: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Namens A Joshi
Verzonden: Saturday, January 20, 2007 8:06 PM Aan: oracle-l_at_freelists.org
Onderwerp: statistics stability  

Hi,

  Question : Is it advisable to stop generating periodic (weekly, monthly) statistics if I think I am getting the right query plans for my tables and major application are running fine. Then generate statistics for new tables and tables with new index and I think new column too. I assume new statistics would be needed in case of index rebuild. I do not have histograms for any tables and the I think general distribution of data will remain same. I am thinking the reason for statistics is to guard against change in data distribution and if that is remaining same then no need to generate statistics periodically. Please correct me if I am wrong and if there are other reasons for periodic generate. I am on 9i. This way if there is reported performance degradation then we can be sure that it is not due to change in plan due to statistics. Right now I am saving statistics weekly/monthly but still sometimes it is difficult to trouble shoot. I also do not know the impact if generate statistics gets interrupted or some other impact like database crash during the generate. Like losing statistics or having only table statistics change but index statistics do not change. Or some other issue.  

Has anyone tried this approach or generate statistics with periodic interval like 6 months? Any other factors to consider?

Thanks for help.   

TV dinner still cooling?
Check out <http://us.rd.yahoo.com/evt=49979/*http:/tv.yahoo.com/> "Tonight's Picks" on Yahoo! TV.

--
http://www.freelists.org/webpage/oracle-l