Re: are hints still necessary in 9i?

Jonathan Lewis wrote:
> Notes in-line

Me too - with snippage

> Now wouldn't it have been nice if Oracle had coded up a nice
> static dbms_stats.correlate (tab1 col1, col2) still not perfect,
> but it avoids the combinatorial size of collecting all correlation
> co-efficients.

I guess - but even then, couldn't the possible value pairs combinations still be huge ? And then, of these possible combos, only a very small number actually have anything worth profiling ? I still think you need the 'radioactive trace' to identify what to look for. Hmm - ADDM as the barium meal for the database. I feel a tongue in cheek email to some developers (and marketeers) coming on :-P

> I'm still checking, but do profiles store generic correlation
> information, or do they store the same information once
> for every use of a particular table in each different SQL
> statement ? I suspect it is the latter.

My understanding is the same as yours - the latter. It's the statement that is profiled. Note that profiles could have other information other than correlation info - for instance, I understand that how the data is consumed is also considered, which may result in a profile containing information to the effect of a first rows hint. I have yet to understand how this is detected. Seems pretty damn clever to me.

So I think it's fair to say that Profiles are not just the same as outlines, but they are also not not as well.

> I haven't put the bits together yet, but I have an unpleasant
> feeling that you are telling me I really can run the advisor
> in pseudo-real time to update profiles and make execution
> paths change dynamically every 30 minutes if I want to.

Sure, if you want to. I'm not too sure there would be much benefit to that, however.

> Bye-bye deterministic trouble-shooting ! Why was performance
> slow yesterday afternoon - I don't know, the execution plans
> were different.
>

I think, but will need to verify, that you can historically view past execution plans as well.

The whole point of the ADDM stuff is that the question "why was performance slow yesterday" will hopefully go away. With ADDM, either 1) It wasn't, or
2) You already know.
That's the design goal at least. YMMV.

> But it brings me back to the original point,
> which is that Outlines (can) also guarantee you the correct
> execution path for a given SQL statement, but there is a
> cost associated with USING them inappropriately - will
> Profiles have the same (potential) problem.

I absolutely certain inappropriate use will introduce unnecessary cost :-)

> I think some of the advice I've been getting on test cases
> is terrific, by the way. The whole thing looks very promising.
>

It is an exciting time. It's definately a big step in the product's direction. Received on Fri Oct 10 2003 - 10:10:40 CDT