Re: Creating Histograms
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 23 Jul 2004 10:10:16 +0100
On Thu, 22 Jul 2004 16:34:17 -0600, Wolfgang Breitling
<breitliw@xxxxxxxxxxxxx> 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@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:
