Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How "deterministic" are EXPLAIN PLAN results?

Re: How "deterministic" are EXPLAIN PLAN results?

From: Joel Garry <joel-garry_at_home.com>
Date: 14 Apr 2003 18:46:08 -0700
Message-ID: <91884734.0304141630.5ba1b351@posting.google.com>


Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3E968797.2DF9_at_yahoo.com>...
> 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 :-)

Or maybe getting rid of FET$ problems and such with LMT made the rest of it small enough where CBO performance issues are insignificant. :-O

jg

--
@home.com is bogus.
Man, it feels good to be done with taxes.  Oops, they're filed
electronically and computers hate me and there's no paper trail... oh
nooooooooooo!
Received on Mon Apr 14 2003 - 20:46:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US