Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: collecting statistics on table those have bulk inserts everyday..

Re: collecting statistics on table those have bulk inserts everyday..

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 12 Apr 2004 08:10:23 -0600
Message-id: <>

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 ( and Tim Gorman has another excellent paper "The Search for Life in the CBO" at

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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Apr 12 2004 - 09:07:39 CDT

Original text of this message