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
--
//www.freelists.org/webpage/oracle-l
Other related posts: