Re: How "deterministic" are EXPLAIN PLAN results?

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3e9534a8$0$22006$ed9e5944_at_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.

That's an excellent point, I was coming from the viewpoint that plan stability was basically like RBO, effectively turning off the CBO and doing RBO with an arbitrary set of rules. But that's all semantics, more important is your point about the good thing, which a stale stable plan is not. And even using periodic analysis without stability can leave you in a reactive mode as the data distribution changes over time to something the CBO does stupid.

Overall, I think CBO is a good thing. But I get worried when I read the RBO going-away announcement, and cynically read it as "we are going to break stuff in the kernel now that we don't have to support old RBO, and it will be too difficult to QC in anything less than the time when the sun goes into its helium cycle and swallows the Earth."

But that's just me looking at past CBO bugs.

jg

--
@home.com is bogus.
http://mindprod.com/unmain.html