Re: dbms_stats / 9204

"Oradba Linux" <techiey2k3_at_comcast.net> wrote in message news:2qcAd.577911$wV.371937_at_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 ?
>
>

By using the event 38019 at level 1 , i am still seeing calls to dbms_stats for indexes not reverting to analyze.
It is still running the SQL statements. Has anybody faced this issue ? Oracle 9204 / Solaris 32 bit Received on Tue Dec 28 2004 - 20:08:15 CST