Re: hmmm....

From: Noons <wizofoz2k_at_gmail.com>
Date: Mon, 28 Feb 2011 22:59:05 -0800 (PST)
Message-ID: <9c316134-2b92-4691-87e3-bcd20026773a_at_y36g2000pra.googlegroups.com>

On Mar 1, 8:21�am, joel garry <joel-ga..._at_home.com> wrote:

> > How is it that dbms_stats accepts "estimate_percent" as a percentage
> > of the rows to sample, when it obviously doesn't know what 100% is
> > until it's done a full sample?
> > Something's amiss and I reckon it's a thing called logic...
>
> Season as with all MOS, but see Why or When does
> Dbms_stats.Auto_sample_size sample all rows [ID 343849.1]

Problem is: I'm not using auto_sample_size, I'm using estimate_percent=>33 explicitly.
And it does anything but that...

No, it does NOT switch automagically to 100%. I've got tables with nearly the same number of rows and blocks, some go num_rows=sample_size while others go num_rows=sample_size * .3!

Nor is it dependent on 5000 or 50000 rows: we don't know how many rows are there in the first place, why would it switch based on that info? And it doesn't.

And then, only sometimes.
Hate that word: "sometimes"....
I like predictability, not randomness.

> "...but then dbms_stats will take an accurate row count by using
> SELECT COUNT(*) from the table and index"
>
> http://structureddata.org/2007/09/17/oracle-11g-enhancements-to-dbms_...
> shows an interesting test.
>

Yeah, yeah, I know all about that.
This is 10gr2, not 11g.
And even in 11g the 33% is not guaranteed...

I wish MOS would come up with somethjing consistent.

As is it doesn't help for acceptance testing, when stats for the same size table become differently calculated across dbs and can produce different plans. It stops being an acceptance test right there... Received on Tue Mar 01 2011 - 00:59:05 CST