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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ana Choto Sent: Wednesday, August 10, 2005 11:56 AM To: oracle-l_at_freelists.org
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 Received on Wed Aug 10 2005 - 14:26:23 CDT