Re: dbms_stats / 9204

From: Oradba Linux <techiey2k3_at_comcast.net>
Date: Tue, 28 Dec 2004 12:35:10 GMT
Message-ID: <2qcAd.577911$wV.371937@attbi_s54>

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:41CAD42F.1226_at_yahoo.com...
> Jonathan Lewis wrote:
> >
> > I haven't followed this through, but it looks as if it's
> > related to the issue that Oracle apparently wants a
> > minimum sample of 919 leaf blocks when sampling
> > an index. So the theory is that if you do something like:
> > estimate_percent=>10,
> > Oracle can work out if this meets the 919 block limit,
> > and change the sample size if necessary. But it's only
> > supposed to change the sample size upwards. When
> > (as your bug note shows) you set 100% as the sample
> > size, Oracle still adjusted it down to about 54% then
> > got a small sampling error that resulted in a num_rows
> > that was a little off.
> >
> > In reply to your earlier question about sample_size - for
> > indexes, this is the number of index entries that existed
> > in the leaf blocks that were sampled.
> >
> > --
> > Regards
> >
> > Jonathan Lewis
> >
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> > The Co-operative Oracle Users' FAQ
> >
> > http://www.jlcomp.demon.co.uk/seminar.html
> > Optimising Oracle Seminar - schedule updated Sept 19th
> >
> > "Oradba Linux" <techiey2k3_at_comcast.net> wrote in message
> > news:0S3yd.237219$V41.231021_at_attbi_s52...
> > > Are you using dbms_stats on 9204? Did you come across a bug 3301328 ?
> > > Index stats being collected is not accurate when compared to analyze
> > > commands.
> > > How about analyze commands using partition tables ? Any issues ......
> > >
> > >

>

> On 9205 I've still noticed a number of hassles with dbms_stats when it
> comes to index usage. One killer thats hurting us is the num_distinct
> being vastly undercalculated on many occasions.

>

> At this current site, we've taken the "backward" step of setting event
> 38019 at level 1 to force calls to dbms_stats for indexes to revert to
> analyze.

>

> :-(

>

> --
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> ISBN: 1590592174

>

> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com

>

> Coming Soon! "Oracle Insight - Tales of the OakTable"

>

> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
> and...he will sit in a boat and drink beer all day"
>
> ------------------------------------------------------------

With using analyze is it still possible to copy the statistics from the prod database to a test database.
Any caveats with that ? Received on Tue Dec 28 2004 - 06:35:10 CST