Re: statistics stability
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: ajoshi977@xxxxxxxxx
- Date: Sat, 20 Jan 2007 21:02:04 +0000
In many/most database operations a lot of data access is rather date
dependent 'orders in the last week','profits this quarter' etc etc. After a
while of not gathering stats, the optimizer will 'know' that it is returning
no data for this sort of query. :(
On 1/20/07, A Joshi <ajoshi977@xxxxxxxxx> wrote:
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 "Tonight's
Picks"<http://us.rd.yahoo.com/evt=49979/*http://tv.yahoo.com/>on Yahoo! TV.
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
