Re: dbms_stats / 9204

"Jan Jansen" <jan_at_jansen.com> wrote in message news:1104237419.57999.0_at_doris.uk.clara.net...
> Oradba Linux wrote:
> > 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 ......
> >
> >
>
> We tried dbms_stats for a while, with various settings and parameters on
> 9.2.0.5, and had following observations:
>
> takes much much longer than analyze .. compute statistics in most cases
>
> did not improve query (optimizer) results, in several cases it got worse
>
> need to do an analyze .. estimate statistics in any case if you want to
> see statistics on chained rows etc.
>
> harder to quickly call interactively (may/should wrap normal cases in
> own SP's though).
>
> advantage: can be called without dynamic sql ('execute immediate') in
SP's.
>
> So we decided to wait and keep using analyze table for the time being.

I think there was an issue with dbms_stats ( according to one of the bug notes) that dbms_stats was gathering stats twice if it thinks the sample size was small. Did you run tkprof /trace on the command to see if it is happening? If the sample size is less than 919 blocks ( refer jonathan) then it will sample what ever % you asked to sample then do a higher or 100% sampling. It is supposed to be fixed in 9205 patchset.

We introduced dbms_stats on one of our databases. It seems to be working fine. We did not run into major issues. One of the issues that i am noticing is sometimes distinct keys are undercalculated like connor mentioned. Also in one other test server we are seeing a constant sample size which is less than < 1% of the sample given any sample size. My sample size is not less than 919 blocks. Received on Tue Dec 28 2004 - 20:06:26 CST