Re: Fed Up with being a DBA

"Domenic G." <domenicg_at_hotmail.com> wrote in message news:c7e08a19.0309131019.1f03442c_at_posting.google.com...

> 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.

Just a quick note here. It's not just "all the paths are indexed". It's also the nature of the application. Small result sets and small number of rows in EACH transaction, with very little DML processing needed. Which favours indexed paths. Versus the usual "hash join everything" strategy. You can make CBO behave the same way as the RBO. But only in the latest versions, and it needs someone to dwell into Metaclick and find the appropriate docs. Which is IMHO the wrong way to go about it. Oracle needs to correct these inconsistencies between versions and stabilize the darn thing. THEN, we'll all start using it.

> 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.

And in some versions that won't be enough. It's all to do with the point version, the patch level and what needs to be done to get it behaving in a PREDICTABLE fashion. Which is the most important thing for ANY optimizer in ANY environment.

IMHO, the biggest block to 3rd party apps using later versions of Oracle has always been the way Oracle approached the CBO: it was always "someone else's fault, not theirs". Wrong, so wrong I can't even begin to understand why they did it.

The point for a 3rd party writer is: they do NOT want to spend ANY time pouring over 10046 dumps figuring out why the darn thing has suddenly started to use hash instead of nested loops or vicky-the-versa. They want to write their SQL once, and have it behave THE SAME EXACT WAY in ANY Oracle db they throw it at. Which is most definitely not what you get with the CBO in all versions up until 9ir2! Jury still out on 10g.

> 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.

Don't dismiss Daniel off-hand. He knows quite a lot about Oracle and makes a true effort to learn it in detail. Even though he doesn't have to. It's a rare combination in this day and age! He might not have yet what is usually called "real world experience" and I prefer to just call "been there, done that". But it will come. He's quite right in his efforts to make CBO more used and get people to upgrade. We just disagree on the methods used to achieve it.

> 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.
>

I haven't yet seen details of how it works in 10G. I'm sure Jonathan and others will chip-in with them when they figured it out. I certainly don't have the time to do it and thank them for doing so and being so forward with the "good oil". But I'll bet if anything, Oracle will have expanded on the ability and ease of use of setting it in one path and leaving it alone. And I'll also bet you won't need extended stats analyze to get it going solid in OLTP.

What I'd like to see (and I believe is not there) is a way for a global setting whereby I can tell it: "tune yourself for OLTP" or "tune yourself for DSS" or "tune yourself for DWS" and the darn thing just gets out of my way and mind. Of course there will always be exceptions, but the above will cater for 90% of the cases out there. Good enough for me.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam