RE: More on dbms_stats

My guess is that gather_auto is picking too small of a sample size, and when 
the developer runs dbms_stats, he is doing a compute (100% sample size), which 
is giving the CBO better stats on which to make decisions for the explain plan. 
 You might just have to add his command to the end of your script so that stats 
are always computed on that table.

Regards,
Brandon Allen

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ana Choto
Sent: Wednesday, August 10, 2005 11:56 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

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

Other related posts: