RE: Should we stop analyzing?

Mogens

> Friends,
>
> I'd like to start a debate, which perhaps has already taken
> place, but
> if so I don't recall it: Should we stop analyzing tables and indexes?
>
> Let me clarify:
>
> I've always told people that using the 'monitoring' option
> (alter table
> X monitoring in 8i, plus alter index I monitoring in 9i) was a good
> thing, because they would make sure that after a certain
> amound of data
> changes you got fresh stats (after, of course, using
> dbms_stats.gather_stale_statistics, etc. on the collected
> objects). We
> can always discuss whether the 10% threshold that
> gather_stale_statistics is based on is sound or not, but it can be as
> good as any other number. Except 42 :).
>
> But then I listened to Dave Ensor at the UKOUG conference,
> and he said
> roughly this:
>
> * Stop analyzing after the first analyze. It's the new stats
> that cause
> the optimizer to change execution plans.
> * "I know that big tables tend to stay big. Small tables stay small.
> Unique indexes stay unique and non-unique indexes stay non-unique..."
> * If the data changes A LOT you should of course re-analyze.

I too listened to Dan Fink at UKOUG and he made what I think is a really important distinction.

You don't want statistics to be up-to-date, you want them to be accurate.

In other words the frequency of analysis is not what we should care about, but how well the statistics reflect the data. The quote about the US_STATE table reflects this, when was the last time the US modified the states that make it up (the UK does the equivalent about every 10 years BTW). I don't think that your summary of what Dave Ensor said contradicts either this, or your recommendations. The stats become inaccurate when the data changes 'A LOT'. My gut feeling is that saying 'A LOT' = 10% is better than 'A LOT' = 'every 7 days', but probably not much. For one of our apps 'A LOT' turned out (predictably, but unpredicted :( ) to mean 13 rows in a single table, for the same app 250,000 rows in another table is irrelevant. 13 < 10% and 250k > 10%. Obviously the people who had added the 13 rows 'haven't made any changes, what did you do to the database'. 'A LOT' is in my view almost certainly application and data dependent.

Niall

( who admits that we still gather stats on the schema each week, but thinks this might be a bad risk :( )

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).