Re: silly dbms_stats question
- From: Thomas Day <tomdaytwo@xxxxxxxxx>
- To: Jon.Crisler@xxxxxxx
- Date: Fri, 1 May 2009 07:33:36 -0400
Oracle doesn't export stale stats, which is why I asked if Oracle uses stale
stats in optimizing an execution plan. I guess that I'll have to do some
testing to see if the explain plan changes when the stats go stale. Dynamic
sampling might produce the same plan as fresh stats so that won't be
definitive if the plan doesn't change but if it does change then I'll have
all the proof that I need.
The schema stats are locked but they still go stale because of the truncate
and reload. Also, objects that are dropped and recreated do not inherit the
schema lock. I know that it's silly of me but I had assumed that a schema
lock was a schema lock.
Importing good stats daily is our current plan, I'm just searching for a
better alternative.
I'd have to look into SET_TABLE_STATS and see what that involves. There are
over 4000 tables, indexes and partitions involved. I suspect that it will
be more work (shudder) than importing schema stats daily.
I'm also looking at stored outlines. That would solve the problem with
canned reports (though managing stored outlines is another can of worms) but
it wouldn't help with ad hoc queries.
Thanks for all of your input. It's helped me clarify my understanding of
stats and how to approach this problem.
On Thu, Apr 30, 2009 at 11:35 PM, Crisler, Jon <Jon.Crisler@xxxxxxx> wrote:
> This sounds like a great suggestion- I am kicking myself for not thinking
> of it when I asked the original question.
>
>
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Fuad Arshad
> *Sent:* Thursday, April 30, 2009 4:37 PM
> *To:* oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: silly dbms_stats question
>
>
>
> you can always lock the stats so oracle will never change the stats
>
>
> ------------------------------
>
> *From:* "Brady, Mark" <Mark.Brady@xxxxxxxxxxxxxxxxx>
> *To:* Thomas Day <tomdaytwo@xxxxxxxxx>
> *Cc:* "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
> *Sent:* Thursday, April 30, 2009 1:49:01 PM
> *Subject:* RE: silly dbms_stats question
>
> >Other than exporting the good stats and re-importing them daily, is there
> any way to stop Oracle from marking the stats as stale?
>
>
>
>
>
> Do you have a particular issue with this approach? Why doesn’t this work
> for you?
>
>
>
> * *
>
> * *
>
> * *
>
> * *
> ------------------------------
>
> *From:* Thomas Day [mailto:tomdaytwo@xxxxxxxxx]
> *Sent:* Thursday, April 30, 2009 7:57 AM
> *To:* yong321@xxxxxxxxx
> *Cc:* Crisler, Jon; oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: silly dbms_stats question
>
>
>
> Does Oracle use stale stats for the CBO if fresh stats aren't available or
> does Oracle treat stale stats the same as non-existant stats? Is there a
> way, other than re-analyzing the table or index, to keep stats from being
> marked as stale?
>
>
>
> We have tables that are being truncated nightly and then being re-loaded
> with data that is 99.99% identical with the previsous data. (Don't bother
> telling me that we shouldn't be doing it that way; I have to play with the
> cards I'm dealt.) I have the schema stats locked and the automatic stats
> job is disabled. (It insisted on computing new stats on the table when it
> was at 0 rows.) Oracle is marking the stats as STALE.
>
>
>
> Other than exporting the good stats and re-importing them daily, is there
> any way to stop Oracle from marking the stats as stale?
>
> * *
>
> >>> This e-mail and any attachments are confidential, may contain legal,
>
> professional or other privileged information, and are intended solely for the
>
> addressee. If you are not the intended recipient, do not use the information
>
> in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2
>
>
Other related posts:
