Re: Re: DBMS_STATS

Hi, friends:

	How do you use dbms_stats.gather_schema_stats in OLTP production system?
	I ever used estimate statistics =20% percent, and some time have serious performance impact while two big table join in my production changed. Later I changed to compute and till now , It is ok.
	And another question about CBO stability:
	Do you always analyze your database weekly(or with less data changed, monthly)? Some times because of something wrong, the db is performance poor. While development team will always say: it must be your CBO that is misfunction, repair it! Faint, I think CBO is ok in most case with compute statistics, but that kind of question is really headache, right? How do you friends answer that kind of questions? Sometimes I even want to stop analyze the db weekly.As far as I know, some site like Ebay do not analye database.Can it because they use rbo?
	And to jeff, for DW application, for indexed columns size 1 is good because it gather histrogram data, but for OLTP system, do you think it is necessary? Friends please share your opinions? Do you use dbms_stats.gather_table_stats, or dbms_stats.gather_schema_stats?

My scripts:
>echo begin to analyze user bidder at `date +%x%T` >>/export/home/oracle/log/analyze.log
>sqlplus /nolog <<EOF
>connect / as sysdba
>alter session set sort_area_size=100000000;
>alter session set sort_multiblock_read_count =128;
>execute dbms_stats.gather_schema_stats(ownname=>'bidder',estimate_percent=>99,degree=>8,cascade=>true,method_opt=>'for all columns');
>quit
>EOF
>echo finished analyze user bidder at `date +%x%T` >>/export/home/oracle/log/analyze.log

        Thanks.

Regards
zhu chao
msn:chao_ping_at_163.com
www.cnoug.org(China Oracle User Group)

• 2003-02-27 08:09:00 ,you wrote£º=======

>Terrian, Tom (Contractor) (DAASC) wrote:
>
>> I have never had good luck with DBMS_STATS. It seems that the old
>> analyze runs much faster.....Runs in 45 seconds:analyze table
>> log_trans partition (log_trans_20030104) estimate statistics sample 5
>> percent; Takes over 2 hours:execute
>> dbms_stats.gather_table_stats(ownname => 'LDGADMIN', -
>> tabname => 'LOG_TRANS', -
>> partname =>
>> 'LOG_TRANS_20030102', -
>> estimate_percent => 5);
>> Am I missing something? Aren't both commands the same?Thanks,Tom
>
> Hello Tom
>
>We too experienced terrible performance upon first using dbms_stats.
>There
>are two things we did that brought the performance in line with the
>analyze.
>1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1'
>2. We set estimate_percent = 15
>
>Hope this helps.
>
>BTW:
>Sun/Solaris 2.6 & 2.8
>Running 8i, 9i, 9.2
>5 terabyte db's
>
>Jeff

• = = = = = = = = = = = = = = = = = = =

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).