Re: best way to gather schema statistics ...

On Tue, 20 Jan 2004 16:09:15 +0100, "Giovanni Azua" <bravegag_at_hotmail.com> wrote:

>Hello all,
>
>While digging into the Oracle API there
>are some built-in packages with overlapping
>functionality e.g.
>
>DBMS_STATS.GATHER_SCHEMA_STATS
>
>and
>
>DBMS_UTILITY.ANALYZE_SCHEMA
>
>My question is, which of the two should
>I preferably use and why, to have the
>histograms done for the cost based
>optimizer?
>
>My requirements are simple, I need to move
>the complete system to another fresh Oracle
>instalations, I use exp/imp but the statistics
>are not always properly transported, then I have
>the following:
>
>declare
> type strTable is table of varchar2(100);
> pvschema_names strTable := strTable('AR', 'DR', 'MD', 'AL', 'WFL');
>begin
> for i in pvschema_names.first .. pvschema_names.last
> loop
> dbms_stats.gather_schema_stats(
> ownname => pvschema_names(i),
> options => 'GATHER AUTO',
> estimate_percent => dbms_stats.auto_sample_size,
> method_opt => 'for all indexed columns for all indexes size
>repeat',
> degree => 10
> );
> end loop;
>end;
>/
>
>Almost forgot, it also needs to be very fast :)
>
>Thanks in advance,
>Best regards,
>Giovanni
>

dbms_stats produces more accurate results especially with respect to histograms. Oracle has being making clear for a few years that the ANALYZE command (which is being called by analyze_schema) shouldn't be used for gathering statistics anymore.
You can set up monitoring on your tables, so you need to analyze stale tables only. Consult the dbms_stats documentation for further details

--
Sybrand Bakker, Senior Oracle DBA