Re: More on dbms_stats

  • From: Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • To: achoto@xxxxxxxxxxxx
  • Date: Wed, 10 Aug 2005 17:15:11 -0400

Ana Choto wrote:

exec dbms_stats.gather_schema_stats(ownname=>'DATATEL',options=>'GATHER
AUTO',degree=>8,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1');

That would create an awful lot of histograms if method_opt was observed.. Fortunately, it is ignored with
GATHER AUTO. Here is what the fine manual says:
options| Further specification of which objects to gather statistics for:

|GATHER|: Gathers statistics on all objects in the schema.

|GATHER| |AUTO|: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When |GATHER AUTO| is specified, the only additional valid parameters are |stattab|, |statid|, |objlist| and |statown|; all other parameter settings are ignored. Returns a list of processed objects.

This is done on all the datawarehouse schemas and the dbms_stats job runs
after the daily load has finished. If I understand correctly 'gather auto'
collects statistics for those tables that have experienced a 10% change or
more. I have checked these tables and they have been analyzed. But, the
report still won't run until the developer reanalyzes (now with dbms_stats)
those tables. He runs dbms_stats with the following options:

What kind of a report from heck are we talking about? Statistics should be completely transparent
for any report?

-- Mladen Gogala Oracle DBA Ext. 121


Other related posts: