Re: How "deterministic" are EXPLAIN PLAN results?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 10 Apr 2003 10:08:56 +0100
Message-ID: <3e9534a8$0$22006$ed9e5944@reading.news.pipex.net>

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0304091535.27de7bb_at_posting.google.com...
> Spam_at_DefinitiveSolutions.com (Larry Leonard) wrote in message
news:<27270a53.0304090254.6c48b3f_at_posting.google.com>...

> I think CBO is deterministic in each of it's parts, but all together
> it is effectively non-deterministic, since too much of it is magic -
> we don't get enough information to make it deterministic. So we poke
> and prod and use plan stability.

I don't entirely disagree with your statement, but it is worth noting that plan stability is implemented using optimizer hints. If the average practitioner (or even just the advanced ones) were as clued up as the plan stability code then we'd get a lot of sql statements with the full set of hints (as per Jonathans post). There probably is enough information around for us to make it deterministic - 10053 traces spring to mind, but we don't use it.

All this of course assumes that a stable execution plan is a good thing, which is a debatable assumption.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Thu Apr 10 2003 - 04:08:56 CDT