Re: How "deterministic" are EXPLAIN PLAN results?

Joel Garry wrote:
>
> "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

Interesting that in 9.2 now, stats on the dictionary are now supported. Which leads me to think that Oracle have managed to mimic rule-like behaviour in the dictionary by simply supplying sufficient hints in the recursive sql :-)

Connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"