Re: Fed Up with being a DBA

Nuno,

Your commentary was simply brilliant and I really enjoyed reading it. I too have struggled with CBO many times over -- in my experience, in basic OLTP, where all the paths are indexed - RBO is much faster, doesn't require a million permutations during parse time, and guarantees execution plan stability. This is especially important if that one OLTP SELECT retrieves one or two rows and joins along a chain of let's say ten tables on the PKs. Throw that same query at CBO and I swear to God it will insist on using hash joins somewhere in the join strategy. To counteract this, I must order the tables a certain way in the FROM and issue a /*+ ORDERED INDEX (driving_table driving_table_index) USE_NL (driving_table join1 join2 join3 ... */ to achieve the exact same result as RULE which zeroes in on the best plan right away.

Daniel is so busy teaching theoretical examples he just doesn't have the real-world experience that comes with an application where you CANNOT inject hints such as these into the code. I use COST as well, when appropriate, but if Oracle really has fully dropped RBO in 10g, I think it will be suicide for them in some way. Don't abandon something that works. And, by the way, the reason why RBO doesn't pick up on new features is because Oracle is too lazy to retrofit the RBO code with them -- function index, partition, etc.

Cheers ...
Domenic.

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<3f632853$0$14561$afc38c87_at_news.optusnet.com.au>...
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1063413657.422501_at_yasure...
>
> > >ones, look at all the hints. However, the OP has a good point, and
> > >that is, those are rarely relevant to OLTP queries in commercial
> > >packages that have been wrung out for years.
> > >
>
> for the reasons exposed. They do small, direct I/O, which is
> pre-defined and needs to be as fast as possible. You just can't
> do that with a CBO that analyzes to death each statement. You
> do NOT NEED to do so. The I/O in OLTP is tuned once and stays that
> way. It's a fixed system. Period.
>
>
> > On this I'll agree. But one must remember that those commercial packages
> > were, for the most part, written a decade ago, have little more advanced
> > than v7 code, and desparately need work.
>
> No they don't. We are talking OLTP. There is nothing that needs "work"
> in that environment. All work was done ages ago when the darn thing
> was invented back in the 60s. Let's not re-invent boiled water just because
> we now have a u-beaut kettle to fill with. I/O in OLTP is pretty basic
> and MUST remain that way or you drop performance and scalability.
> Simple as that.
>
>
> >
> > My statement was that CBO will equal or outpeform RBO if in the hands of
> > someone that knows how to work with it.
>
>
> I disagree. The whole point behind CBO is PRECISELY to NOT have to know it
> in detail. Otherwise it's useless. If I have to know intimately an optimizer
> in order to make it work properly, I'll tell you one thing right away: there is
> something fundamentally WRONG with that optimizer...
>
>
> > Same thing goes for playing golf
> > or picking a good wine.
>
> Hehehe! You don't play much golf, do you? There is very little of
> automated optimal behaviour there... :D
>
>
> > Too many people are still thinking v7-8 when
> > they should be thinking v9-10. And likely it is those same people that
> > will be complaining soon that their jobs were off-shored.
>
>
> Daniel, the "threat to job" impending event sales line has been tried, many
> times before, in the Oracle world. It doesn't work. And the proof is all
> those "too many people" still in 7-8. Have you ever wondered why there are
> so many? They quite rightly don't give a hoot about new features unless they can
> use them. Stop blaming customers: they are the reason you and all of us
> are here. They ARE right. You (and I and many others) aren't.
>
> This is just an encore of the old marketing crap of new versions:
> "all before was wrong and old, only the new is good". It's utter rubbish!
> And you know that as well as anyone else. Don't use it here, there are too
> many people in this newsgroup on which it just doesn't work.
>
> I understand you want to capitalize on your hard preparation work for 10G,
> and all those months using the pre-release version. By all means do so,
> but don't do it by aggravating DBAs: it *will* backfire. Been there, seen
> it all before.
>
>
> I'd prefer to see a more complete explanation of why. There are HEAPS
> of reasons why the CBO is a better solution. But so far I've only heard
> the "marketing" ones. There are many ways the CBO can be made more
> reliable and efficient than the RBO. Yet people insist here on lengthy
> explanations about function-based this or parallel-that. Those have
> NOTHING to do with how the CBO can be made more efficient than the RBO.
>
> All this means zilch to most DBAs that have to deal with "locked-in" apps.
> And guess what? They are the VAST majority of DBAs out there.
> Ignore them and their constraints, or threaten them, at your own risk.
Received on Sat Sep 13 2003 - 13:19:22 CDT