RE: More on dbms_stats

Ana

The following commands generate different statistics.

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

>exec dbms_stats.gather_table_stats(owname=>'DATATEL',tabname=>'<table_name>',
>cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1');

In fact when you specify GATHER AUTO you are saying to Oracle that it can choose all the settings for the gathering, especially sample size and histograms. Carefully review the description of this option in the documentation (http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_stats.htm#1036280):

"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 ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects."

In my opinion, according to your description, you should use GATHER STALE.

Hope this helps,
Chris

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