RE: silly dbms_stats question
- From: "Crisler, Jon" <Jon.Crisler@xxxxxxx>
- To: "Thomas Day" <tomdaytwo@xxxxxxxxx>, <yong321@xxxxxxxxx>
- Date: Thu, 30 Apr 2009 12:18:39 -0400
If the stats are stale, it will still use them. This is one of those
cases where if the data cardinality and number of rows is consistent,
you can ignore the stale stats issue. To prove this, you would have to
compare an explain plan with a before and after test- you would probably
find them identical. You can easily run into a case where you spend
more resources running dbms_stats that you ever get back in a better
execution plan.
________________________________
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?
Other related posts:
