Re: Creating Histograms

On Thu, 22 Jul 2004 16:34:17 -0600, Wolfgang Breitling <breitliw_at_centrexcc.com> 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
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------