Re: why does CBO not use available indices

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bm8npv$kmg$1$8300dec7_at_news.demon.co.uk...

> Lag is allowable.
> Claiming an application is database independent
> by choosing to design it very badly is not.

Agreed. But nevertheless, it's the vast majority of apps out there. Reminds me of BAARF: great idea, but the darn thing is so widespread I reckon the folks pushing it have got buckleys of winning. Much better to learn to live with the problem and fix it by working within the system, IMO. Then again, I'm getting too old for "crusades"... ;)

> And Oracle Corp. does try. It is quite clear in recent
> versions of the database that features, and internal
> mechanisms have been introduced or changed to
> allow Oracle to deal with design problems.

Exactly. I think there is gonna be much MOTS in this department. They can't turn the blame on everyone else forever. Particularly now that they are providing complete outsource services and have to learn and cope first hand with the crap we've been living with for the last umpteen years. Works wonders in getting proper production controls going! :D

> instability that you complain about. If Oracle changes
> code to fix problems that should never have existed,
> some people will start having problems.

Aye, that is the reverse side of the coin...

> As another example, the following query uses an ordered hint
> to join the tables in a specific order: emp, dept, sal, and finally,
> bonus.

Ah yes, I think I know which one that was. As an aside: the ORDERED in most cases solves the problems and obviates the need for use_nl hints. Have you found this as well? It's almost like the CBO is thinking: "I'll do a hash or merge join, but if you change the order of table evaluation then I'll go for NLs". It's been my experience since the latest patched levels of 8ir3 and 9ir2 (I didn't play much with 9ir1).

> The ACTUAL effect of the use_nl(sal) component
> of the hint is that in combination with the ordered
> hint is that it stops the (apparent) path:
> sal, dept, emp, bonus
> from appearing by blocking the option for a hash
> join into salary, hence blocking the 'sides_swap'
> optimisation from kicking in.

I lost you here: "side-swap" optimisation? Not familiar with the term.

> It's all in the description, isn't it. Yes I do know about
> the options available in profiling - but your one-liner
> was open to mis-interpretation.

Sorry, my fault.

> But describe it like
> that and, hey, I can already to that with stored outlines.
> In both cases, I actually have to invalidate the current
> cursor, though.
>

Yes, that is my understanding too. They just made it look very slick with OEM. But there must be something else going on given that it appeared to start from a totally "vanilla" statement. Ie, outside of any prior outline.

> The profile gets stored - so if you enable it, it's there whenever
> the statement hits the system.

That makes sense. It wasn't clear from the presentation that was the case.

> One of the questions I ask myself on seeing any new
> feature is "who's it for ?'

Yup, same here! Often it's not immediately clear. But it is important to understand this before starting to use them, IMHO.

> I see profiles as being used
> by the DBA who needs to care for lots of 3rd party
> systems, and currently has to create outlines, or
> indexes, the hard way. The advisor is there as a
> sort of expert system to give clues about (possibly legal)
> structural changes, and produce profiles when all else
> fails.

Yes, very much so. I'm starting to learn as much as I can of them: I think they are gonna be fundamental in the next few years. DB2 in mainframes has some very good tools to handle precisely this sort of problems. And when it comes to handling large, mixed loads, those folks know what they're talking about! Attention: this does NOT apply to UDB.

> Oh yes it is !! (I win , I used two !)

¦D

> Second - to go back to your expression - sometimes
> you HAVE to bite the bullet. Do the right thing and
> then fix the fall-out. Otherwise you can waste time
> on localised surgery and still have to change the
> parameter one day - and re-fix all the patchwork.

If I can do it without risk, then it is a very good alternative. However, I'm not always given the privilege of fixing the fall-out. Often if I make a change to the optimizer pars and it fixes one problem and creates others, the only option I'm given is "put it back the way it was". Unless I have prior agreement from damagement that there will be a subsequent effort, I have to stay with the "fix just this one, now!" approach.

> My point here was that if you can do anything to tell
> the truth it helps. Long-term, deliberate lying does
> not help (because Oracle changes code to fix bad
> systems, and one day will change something that
> makes your well-intentioned lie very bad news).

I think we'll have to agree to disagree on this one. I fail to see how a globally non-intrusive technique such as changing one table's stats can cause such havoc a few years down the line.

Not more nor less than any of the other techniques that deal with changing global tuning parameters. Both impose a risk. Both have to be managed, in the absence of a perfect optimizer. We may prefer one or the other, but using the term "lie" to define one is a bit strong, IMHO.

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