RE: statistics stability
- From: "Jack van Zanen" <jack@xxxxxxxxxxxx>
- To: <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 21 Jan 2007 00:23:22 +0100
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Namens A Joshi
Verzonden: Saturday, January 20, 2007 8:06 PM
Aan: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
