best way to gather schema statistics ...

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Tue, 20 Jan 2004 16:09:15 +0100
Message-ID: <bujgbj$i2o6s$1@ID-114658.news.uni-berlin.de>

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 Received on Tue Jan 20 2004 - 09:09:15 CST