On 30/03/12 17:16, Ann Harrison wrote:
> That way, if the stats are wrong it's the database administrator's fault
> that the performance is bad. Knowing how to fix that bad performance makes
> the DBA more magical, raising his value.
Hmm, I see what you are saying! I'm a DBA!! ;-)

> I really prefer self-tuning, self-managing statistics. We don't have them
> for indexes, but as I said, index selectivity is not normally as volatile as
> cardinality.
Oracle are working their way around to "everything tunes itself" but
they are not there yet. A lot of things we used to have to tune -
rollback segments, sort space etc, are now self organising and need
little or no attention, other than the odd occasional check that all is
well.

> There's problem with keeping data ranges (or histograms), given that
> Firebird prepares a statement before it knows the values of parameters.
I wasn't aware of this, which is why I mentioned that I didn't know how
useful a suggestion it would be.

Oracle's optimiser takes these figures into account as part of the
execution plan analysis phase of preparing a statement for the first time.

> select first_name, last_name from rugby_players where gender = ?
>
> Assuming an index on gender, you'd handle the query one way if the
> ? = M vs. F
I'd expect the optimiser to assume, without stats, that there is an even
spread of values over the number of distinct keys in that index. So, 50%
M and 50% F.

You and I both know that's unlikely to be the case - unless women's
rugby has become much more popular that in the past - so a histogram on
that column would assist the optimiser in choosing to use the index or
not. (In Oracle anyway.)

Now, up until Oracle 10g, Oracle used "bind variable peeking" to see if
it could generate a better plan. Unfortunately, it kept the same plan
for the statement each time, so if you hit the indexed case (F) in the
first prepare of the statement, t would be using the index for the Ms as
well.

From 10g onwards it does something different and manages to optimise
the Ms as well. I can't remember exactly what it does though!

> And if it happens during a nested loop join? Re-optimize on every record?
I'd expect a nested loop join to already have the rows it needs in the
driving table by the time it hits the nested loop?


> Good luck (and we all need it)
Indeed we do. There's no right way and plenty of wrong ways to do
something in databases. I'm just a DBA not a designer. I can see how
difficult it is for the designers.

Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767