thanks, you are 101% right :)
"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
news:dbpq005gk76hf6bg6g8rhm4l1co6rre340_at_4ax.com...
> 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
Received on Wed Jan 21 2004 - 11:59:47 CST
