More on dbms_stats
- From: Ana Choto <achoto@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 10 Aug 2005 14:56:28 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
