Re: collecting statistics on table those have bulk inserts everyday..
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 12 Apr 2004 08:10:23 -0600
To quote the eminent Dave Ensor, "DBMS_STATS Paradox:The only
safe time to analyze the database is when it will make no
difference whatsoever." Anytime you analyze an object, you make
it possible for the CBO to decide on a new execution plan. This
plan may be better (run faster) or worse (run slower). So, I'd
propose the following corollary to the DBMS_STATS Paradox, "You
should analyze an object when ALL execution plans for that
object will perform better." IF you run tests and it shows that
a certain analyze strategy improves the performance, then use
it. You may not even want to analyze, but use DBMS_STATS to
insert statistics that enable the queries to run better. Always
be careful of improving 1 query at the expense of 9 others.
As for the "fresh statistics", you should not worry about that.
AFAIK, the CBO does not check the last_analyzed_date when
performing optimization. I use the example of the STATE table
for the United States. Since the last state was added in 1959
(before Oracle v1 was vaporware), why should I analyze the table
every day/week/month? It does not change and the analyze
consumes resources, perhaps causing other problems.
Wolfgang Breitling has some excellent papers on his site
(www.centrexcc.com) and Tim Gorman has another excellent paper
"The Search for Life in the CBO" at www.evdbt.com.
Regards,
Daniel Fink
Syed Jaffar Hussain wrote:
>
> Williams,
>
> I can understand that, the thing is, there are some queries which always
> scan those heavy inserts tables regularly. I have thought from this point
> view that if those quieries didn't get the fresh statistics on the tables,
> it might produce not reliable explain plans.
>
> Thanks for your time
> Jaffar
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
