Re: DBMS_STATS [resend chomped version]

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Jun 2004 13:48:24 +0100
Message-ID: <022101c45920$62bc2720$7102a8c0@Primary>

Note in-line

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Niall Litchfield" <niall.litchfield_at_gmail.com> To: <oracle-l_at_freelists.org> Sent: Monday, June 21, 2004 11:57 AM Subject: Re: DBMS_STATS [resend chomped version]

> 3. I like your idea of creating a table to hold the sample size for each
> table in the schema. Other columns could hold the reanalyze interval, date
> of next analyze, etc. This would ensure each table was analyzed
> appropriately.

I like the idea as well, with the following caveats.

                                                            Oh and it might

suffer in a
similar way from a lack of understanding by end-users as to what it was doing and why.

<JPL>
The critical point is that the control table is populated intelligently by someone who understand the data.

The biggest drawback is the traditional battle-cry:

We do that because the person who worked here 12 years ago set it up that way, and we're not going to change it.

The requirement for statistics will change with the evolution of the database and its users. My viewpoint is that this is a reference table that summarises the documentation that specified the database in the first place. Technically it should be derived from the database specification (the bit that goes:

table X will grow at M rows per month critical entry points to table X are

        predicate 1
        predicate 2

For each row in table X there will be an average of N rows in table Y, varying from 0 to 5N with a sigma of S ) not that I've come across many of those.

</JPL>

For example what my comments in 1 above mean - if correct and its a while since I was at college - is that the package would need to answer the following question about each analyzed segment.

<JPL>

Definitely not the intention.

But a good reason for suitably size LMTs, so you can easily spot the tables or indexes that are growing faster than predicted and revise you understanding of the data, hence revisit you assumptions about required statistics.

</JPL>

I think what I am saying that if we buy the argument that one should not sample every x days (hours, years), because we care about the appropriateness of the stats and not their 'freshness' then building an automated system to gather appropriate stats becomes a non-trivial task.

<JPL>
Quite agree.
Fortunately, most tables and most columns can be covered adequately by a small sample which is affordable (in terms of resources); so we only need to be intelligent about a few critical columns

I forgot to mention the bit where you don't analyze, but just use dbms_stats.set_system_stats to put in the correct numbers where Oracle can't work them out (or spends too much time trying).

</JPL>

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 Wed Jun 23 2004 - 07:45:14 CDT