More on dbms_stats

Yesterday I posted a question regarding dbms_stats and analyze. A developer was running analyze compute on some tables because a report wouldn't run unless he analyzed compute statistics on those tables. I received great responses from the list, for which I'm really thankful. The developer now is using dbms_stats and the report works just fine.

The only thing is that he still has to run dbms_stats even after the daily job that gathers statistics finishes because the report doesn't work. I'm gathering schema stats with the following options:

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

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:

exec
dbms_stats.gather_table_stats(owname=>'DATATEL',tabname=>'<table_name>',cascade=>TRUE,method_opt=>'FOR  ALL COLUMNS SIZE 1'); Now, my understanding is that smon flushes statistics every 15 minutes, and that the statistics are flushed from the SGA when dbms_stats start so the segments that need statistics are examined. Could the combination of these two processes and the fact that maybe, just maybe, these tables have not experienced a 10% change, or that the changes are still not in the DBA_TAB_MODIFICATIONS table what cause the report to hang?

We're thinking of running another job to just analyze the tables involved. Ideally we shouldn't do this, but I don't know what else to try, unless we change the daily dbms_stats job to run without the gather auto option, but this job will run for about 2 hours instead of 40 minutes, and at the time we need to run this job there is a lot of activity on the database and can incur in great overhead.

Any suggestions?

Thanks

Ana E. Choto
American University
e-Operations - Information Technology
Phone (202) 885-2275
Fax (202) 885-2224

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