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...

On Feb 28, 12:46*pm, Noons <wizofo... (AT) gmail (DOT) com> wrote:
Season as with all MOS, but see Why or When does
Dbms_stats.Auto_sample_size sample all rows [ID 343849.1]

"...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...to-dbms_stats/
shows an interesting test.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...ng-mail-fraud/

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.

From http://download.oracle.com/docs/cd/E...tm#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

dombrooks wrote,on my timestamp of 1/03/2011 8:33 PM:

I think you misread what I said. Likely my fault for not explaining it well.
May I suggest you re-read the strange outcome in my previous posts? There are no
significant differences. That is the problem.



What's missing from that quotation is the actual end of that paragraph:
"This ensures the stability of the estimated values by reducing fluctuations."
(and as well the definition of what is a "large enough sample")

I can't for the life of me fathom how introducing a random element can ensure
stability of anything, but I'm sure that's just me.
Yes indeed: too clever for its own good!

In fact, I still can't understand how can they tell me that I'm sampling 30% -
or whatever % - of the rows of a table that I have not counted the rows of: it's
simply impossible to determine any percentage of an unknown quantity without
first measuring the whole of said quantity! And doing so defeats the whole
purpose of doing only a percentage of it in the first place...

Unless of course we're talking histograms as well. But if I didn't ask for
them, then just get out of my way and do a 100% scan every time, there is a good
boy!

Exactly. The problem here is that without going through extensive profiling or
fudging the stats manually or one of the other plan stability options, it is
impossible to confidently state what plan the CBO will be considering between,
say, acceptance and production! Now multiply that by thousands of tables and
indexes and you got the root of an unmanageable problem, grid or no grid, ASH or
no ASH, 10053 or no 10053!

This has been one of the losing battles between dbas in the field who have to
put up with erratic performance and rdbms developers, always too eager to
introduce "kewl" new features that serve no purpose whatsoever in normal
production environments...

Let's hope one day they'll realize that performance in Oracle's RDBMS has never
been a widespread issue. What is unacceptable is the erratic nature of said
performance. And random behavior switches are NOT the way to resolve that
shortcoming...

Look at the differences between auto_sample_size between 10g and 11gr2!
One favors lower percentages, the other favors higher ones: talk about
unpredictable outcome!

On Mar 1, 2:33*am, Noons <wizofo... (AT) yahoo (DOT) com.au> wrote:
Let's say you are Bugs Bunny, sitting in front of an assembly line of
mortar shells. Every third one you hit with a hammer to be sure it is
a dud. You don't know how many will come down the assembly line, but
you are sampling 33% (for a while, anyway).

No argument from me there. Like any utility, I expect it all to just
work, I shouldn't need advanced training in reading traces just to fix
random problems with someone breathing down my neck.

Yes, erratic is the issue. But it does work most of the time, and
tools exist to tell it what to do. There are a couple of different
failure modes, you'd have to be advocating RBO to address the problem
of app developers not being good as good as CBO at figuring out what
the best plan is. The erratic behavior in production is mostly
boundary conditions, CBO suddenly deciding to use an inappropriate
plan. We only notice the fails there, not the vast majority of
successes. (Of course I'm ignoring bind peeking and many other kewl
things that are silly in retrospect - but known soon enough).

So there is a legitimate purpose to the kewl features of CBO, and that
is to address the changing skew and distribution of data. The only
thing that is really missing is a big red button to say "lock it down
like so." There's too many little knobs and switches to do that, but
you can. And you can't really ignore all the various ways a general
purpose rdbms can be used and abused. Maybe it's too much to expect
app vendors to tweak the knobs - no, make that definitely, they have
no idea how the data for any customer is going to spread, and I'm sure
most of us are no stranger to silliness in vendor requirements.
That's the customer dba's responsibility. Bespoke developers should
be expected to be able to do that, but we all know that's too much to
ask.

Good example, but I'm not convinced that the general trend is not
towards "better." The only real problem I see is Oracle not
addressing the range of desire, some production environments value
stability, predictability and quality more than others.

We had a power outage Sunday night, the power-protected server had to
be bounced twice in order to fix the NFS mounts that got screwed up
(there's probably some way to get around that, but it's so rare the
sysadmins haven't figured it out yet) which bothered Oracle not at
all, but a SS database on another server was corrupted, which of
course was something visible corporate wide the next morning (active
directory or something).

jg
--
@home.com is bogus.
What happens with bad email admin:
http://www.signonsandiego.com/news/2...acial-tension/

On Mar 2, 4:23*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:

I'd hate to be Bugsy if estimate_percent is set to 0.000001%...

According to the doco it can be done, although: do you know anyone who
has?
Oh hang on, someone will now butt in claiming it is essential and
we're "bad dbas" for not sampling at that value...

Ever wondered why "dba" is an anagram of "bad"? <g,d&r>

Exactly. The problem is not a generalized one. It's usually the one
in 90000 SQL statement that makes the payroll calc take 7 days instead
of 5 minutes, or the financial report for upstairs take 3 hours
instead of a few seconds.
Hardly the time to start polishing off 10053s: bosses don't grok
"traces"...


Absolutely. I said it here before, not long ago: the performance
problems nowadays - and been like that for a long time - are not
"generalized". I can't remember the last time I saw a site with
performance across the board at the atrocious level, other than the
odd dinossaur who persists in running critical systems with hardware
and software that were old last millenium...

That is due in no small part to the CBO and its new features, but also
to long running campaigns to convince developers to start using bind
variables and such.

What we have now is the odd SQL that takes off on a tangent. In a
sense, that's perceived as worse by the "don't grok" brigade: they
don't like surprises and they are now used to a certain good level of
performance. When a single event happens of bad performance, it
stands out like a sore thumb.

What rattles me is that, sort of associating a profile with every
statement or fudging and locking down stats, we have no mechanism to
hold things quiet and avoid these blowouts.

Big red button indeed: that is sorely needed!

But we also need a way of ensuring that once tested in acceptance, a
new release won't go into production and blow out in unexpected
places. I've got it clamped down now to the odd SQL in the odd month
or so. But I still get differences in execution, even with everything
the same including sysstats and undoco params. Someone needs to find
a way of allowing us to do reliable and consistent releases to
production where not one SQL blows out in execution. I think
consistent and reliable stats are a very big and essential part of
that. But getting them is not easy when the mechanism to do it
decides to pull the rug, because it can...


Indeed. I've been on both sides of the fence. Sites that do
extensive development are usually more open to the odd blowout: they
understand the complexities involved and get involved in the fixing.
Sites that do little or sporadic development are at the opposite end:
they generaly can't afford extended test/release cycles and want
everything to go in first time and work first go. Not easy at all,
even with 11g.



Aye! Yeah, I know exactly that feeling...
Very good points, jgarr. Thanks for the feedback.

On Mar 1, 7:05*pm, Noons <wizofo... (AT) gmail (DOT) com> wrote:

You obviously haven't been to forums.oracle.com for a while ;-)

jg
--
@home.com is bogus.
"...Legionella is common in moist places..."
http://www.huffingtonpost.com/2011/0...n_829896..html

On Mar 3, 3:35*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:

A "while"?
More like never...
Not my cuppa.