Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Creating Histograms

Re: Creating Histograms

From: Niall Litchfield <>
Date: Fri, 23 Jul 2004 10:10:16 +0100
Message-ID: <>

On Thu, 22 Jul 2004 16:34:17 -0600, Wolfgang Breitling <> wrote:
> At 01:55 PM 7/22/2004, you wrote:
> >take note of how many records you add to relatively *small* tables. 13
> >rows added to one of our tables caused hell until we gathered stats
> >again (and it took ages for anyone to admit that anything ahd
> >changed). That would be 13 rows in the sense of another financial year
> >to add to the 2 existing ones - so hardly significant at all :).
> Can you give more details on that and why 13 more rows caused hell until
> stats were gathered again. What were the execution plans before and after?
> Were there histograms involved?

I knew someone would ask that. Unfortunately this will have to be anecdotal evidence since it was 18 months ago. I was trying to be ironic about the significance of the change. The table was a table of valid periods it did have 26 rows in it before the change, after the change it had 39 rows in it ( 3 years worth of financial periods not 2). I think it ought to be obvious that adding 50% more records to a table is worth telling the optimizer about. My point is that the administrators of the system who made this change swore blind that nothing had changed and there was a big problem with the database.
>From their point of view I think this was not unreasonable, it was a
small routine change. Now I'm arguing out of experience rather than theory here, but it seems to me that this pattern of thinking a significant change is insignificant is likely to happen with small tables a lot more frequently than with large ones. Hence my suggestion to pay special attention to the small tables.

I think Jonathan has already mentioned that small stats errors will likely have a larger effect when made on small tables rather than large ones. I'd be a little wary of monitor/gather stale which is what Donald was suggesting for small tables when we know that these are sensitive to changes, that gathering accurate stats on them is really cheap.

> Whenever you drastically change your operations - going from RBO to CBO,
> going from nightly/weekly gather to no-gather with exceptions, going from
> no system stats to system stats, or vice-versa is always a big risk and
> should be tested very thoroughly.

Fortunately there are the export stats procedures in dbms_stats to alleviate this risk somewhat.

Niall Litchfield
Oracle DBA
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 Fri Jul 23 2004 - 04:06:51 CDT

Original text of this message