Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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 -----------------------------------------------------------------Received on Fri Jul 23 2004 - 04:06:51 CDT
![]() |
![]() |