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:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
