Oracle-L: RE: Re: DBMS

From: gmei <gmei_at_incyte.com>
Date: Fri, 28 Feb 2003 07:44:07 -0800
Message-ID: <F001.0055CF7A.20030228074407@fatcity.com>

We run DBMS_STATS.GATHER_TABLE_STATS nightly as cron job using this script to spool the result to a temp file, then use sqlplus to run that file.

select 'execute DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||

segment_name||''',cascade => TRUE);' from ( select owner,segment_name, sum(bytes) from dba_segments s where segment_type like 'TABLE%' and

        owner in ('XYZ') and
        s.partition_name is null

group by owner,segment_name
order by sum(s.bytes) desc);

We use multi-thread (3 threads) to run this script and it cuts the whole time by half. Now it takes about 1.5 hours (our data set is small). For another larger schema, we do this once a month.

Guang Mei

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of chao_ping
> Sent: Friday, February 28, 2003 3:49 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 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_perc
> ent=>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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: gmei
  INET: gmei_at_incyte.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).

Received on Fri Feb 28 2003 - 09:44:07 CST

RE: Re: DBMS_STATS