Re: More on dbms_stats

From: Mladen Gogala <mgogala_at_allegientsystems.com>
Date: Wed, 10 Aug 2005 17:15:11 -0400
Message-ID: <42FA6E5F.50103@allegientsystems.com>

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


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 10 2005 - 16:16:34 CDT