We use dbms_stats.gather_schema_stats and do not pass it sys as a schema owner. Under 8i this method did not work for "gather empty", but did fine with "gather stale". This has been fixed in 9i. However, it still abends if one of the schema owns an iot_overflow table.
Ian
-----Original Message-----
Sent: Wednesday, May 22, 2002 8:29 AM
To: Multiple recipients of list ORACLE-L
Cherie,
Had some problems with DBMS_STATS, but we are making
our way towards it. Just have to be careful, since it
can do quirky things like analyze the dictionary. We
are using dba_tab_modifications to monitor our tables.
Thanks for the input.
Jack
- Cherie_Machler_at_gelco.com wrote:
>
> Jack,
>
> What version are you on? Are you able to utilize
> the gather_stale option.
> That way you would not only be optimizing the amount
> you are estimating but
> the interval between analyzing.
>
> Of course, that option of DBMS_STATS is not
> available on older versions.
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
>
>
>
>
>
> Jack Silvey
>
>
> <jack_silvey_at_y To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> ahoo.com> cc:
>
>
> Sent by: Subject:
> Re: Statistical sampling and representative stats
>
> root_at_fatcity.c collection
>
>
> om
>
>
>
>
>
>
>
>
> 05/21/02 06:19
>
>
> PM
>
>
> Please respond
>
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> Hi Rafiq,
>
> We have been using 35 percent on our warehouse, even
> on our fact partitions. Now that I have thought
> about
> it for a while, that seems like a lot given the
> volume
> of data. If a representative sample can be gathered
> with 10,000 or 50,000 or 100,000 rows, and our fact
> partitions have millions of rows each, seems like we
> could go 1% on our analyze and it would be within
> acceptable tolerances.
>
> /jack
>
>
> --- Mohammad Rafiq <rafiq9857_at_hotmail.com> wrote:
> > The most of the list memeber agrees on estimate
> with
> > 30%....
> >
> > Regards
> > Rafiq
> >
> >
> >
> >
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > Date: Tue, 21 May 2002 13:43:33 -0800
> >
> > Hi all,
> >
> > Did some investigation about statistical sampling
> > this
> > weekend since we are going to optimize our analyze
> > process soon, and would like some input from all
> you
> > orabrains on this one.
> >
> > I opened a TAR with Oracle asking about the
> sampling
> > algorithm of stats collection, and they assured me
> > it
> > was random.
> >
> > The goal of analyze...estimate is to collect stats
> > that are representative of the data population as
> a
> > whole using a given sample set. Since analyzing
> > tables
> > takes up resources (does sorts to order the data
> for
> > investigation) the fewer rows you use in estimate,
> > the
> > less system resources you use and the faster the
> > analyze will go.
> >
> > Since our goal is to get as small a sample as
> > possible
> > and still have stats that are representative, my
> > contention is that we could start by finding what
> > the
> > margin of error will be for each sample size and
> > gauge
> > our tolerance for it.
> >
> > One standard way to calculate margin of error for
> a
> > given sample is by using this formula:
> >
> > M = 1/SQRT(N)
> >
> > where:
> > M = margin of error
> > N=sample size
> >
> > So, if we can tolerate stats that have a 1% a
> margin
> > of error (will deviate from representative of the
> > whole population by 1%), our sample size should be
> > 10,000 rows.
> >
> > Also, a corollary (not a toyota corollary, though)
> > to
> > this would be that the more rows you add to your
> > sample, the closer to representative your sample
> > will
> > be. So, in order to test whether your sample is
> > representative enough, you could analyze using
> > either
> > estimate 49% or compute, take a snapshot of the
> > stats,
> > and then compare the stats from a 10,000 row
> > estimate
> > to those. Then, add rows to your estimate until
> you
> > are satisfied with the stats.
> >
> > This of course is a pie in the sky mathematical
> > model,
> > but seems like a reasonable place to start with
> > testing.
> >
> > Input? Input? Buhler? Buhler?
> >
> >
> > /jack silvey
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > LAUNCH - Your Yahoo! Music Experience
> > http://launch.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Jack Silvey
> > INET: jack_silvey_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > 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
>
=== message truncated ===
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Wed May 22 2002 - 16:33:26 CDT
