Re: hmmm....
Date: Tue, 1 Mar 2011 01:33:20 -0800 (PST)
Message-ID: <bd821863-efdf-449e-834a-6393ec48aa73_at_x13g2000vbe.googlegroups.com>
>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...
Surely using any size sample, even auto, means that the actual rows sampled (ignoring sample size aspect) are different each time so in theory the statistical nature/distribution/etc of the data could be different each time, even in the same environment. That's why these plan stability features keep evolving.
> 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 * .
From http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94721:
"When the ESTIMATE_PERCENT parameter is manually specified, the
DBMS_STATS gathering procedures may automatically increase the
sampling percentage if the specified percentage did not produce a
large enough sample."
Maybe it can decrease as well? Too clever for its own good?
It reminds me a bit of dynamic sampling in SQL statements where it can
discard the dynamic sampling results if it doesn't think it's
representative.
I can't remember what the entry looks like in a 10053 when it does
this.
Cheers,
Dominic
Received on Tue Mar 01 2011 - 03:33:20 CST