Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: statistics stability

Re: statistics stability

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Sat, 20 Jan 2007 21:02:04 +0000
Message-ID: <7765c8970701201302r61de6701xca91ec083fa70120@mail.gmail.com>


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_at_yahoo.com> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 20 2007 - 15:02:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US