DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
        b=YNQZBKykFAWRw5Z40q8LQ5WhPQZxO4a2+2OusdVKqke8kWEexwMXijQ4wNhu8oC7FhNeLxA6Iw3FpXqpcWw+hLr00DGnHX9qKensGGE+53HPZvh2RxFnMSJqSKJzFxBGpvkB2cGTTrIeZ+nZpiOFVbZKGkw1KoTWbEea5BmD8B0=
Message-ID: <7765c8970701201302r61de6701xca91ec083fa70120@mail.gmail.com>
Date: Sat, 20 Jan 2007 21:02:04 +0000
From: "Niall Litchfield" <niall.litchfield@gmail.com>
To: ajoshi977@yahoo.com
Subject: Re: statistics stability
Cc: oracle-l@freelists.org
In-Reply-To: <398604.30993.qm@web58004.mail.re3.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_117098_33114062.1169326924975"
References: <398604.30993.qm@web58004.mail.re3.yahoo.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: niall.litchfield@gmail.com
------=_Part_117098_33114062.1169326924975
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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@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

------=_Part_117098_33114062.1169326924975
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

In many/most database operations a lot of data access is rather date dependent &#39;orders in the last week&#39;,&#39;profits this quarter&#39; etc etc. After a while of not gathering stats, the optimizer will &#39;know&#39; that it is returning no data for this sort of query. :( 
<br><br><div><span class="gmail_quote">On 1/20/07, <b class="gmail_sendername">A Joshi</b> &lt;<a href="mailto:ajoshi977@yahoo.com">ajoshi977@yahoo.com</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>Hi,</div>  <div>&nbsp;&nbsp;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.&nbsp;Then generate statistics for new tables and tables with new index and I think new column too.&nbsp; 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.&nbsp;I am on 9i. This way if there is reported performance degradation&nbsp;then&nbsp;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. 
</div>  <div>&nbsp;</div>  <div>Has anyone tried this approach or generate statistics with periodic interval like 6 months? Any other factors to consider?&nbsp; </div>  <div>Thanks for help. </div><span class="ad"><p> 

</p><hr size="1">TV dinner still cooling?<br><a href="http://us.rd.yahoo.com/evt=49979/*http://tv.yahoo.com/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">Check out &quot;Tonight&#39;s Picks&quot;
</a> on Yahoo! TV.
<p></p></span></blockquote></div><br><br clear="all"><br>-- <br>Niall Litchfield<br>Oracle DBA<br><a href="http://www.orawin.info">http://www.orawin.info</a>

------=_Part_117098_33114062.1169326924975--
--
http://www.freelists.org/webpage/oracle-l