RE: Fav. Urban Legend...

Jonathan,

Hash and Merge anti-join methods were there in 7.3.2. The HASH_AJ and MERGE_AJ hints, as well as the always_anti_join parameter, are in the 7.3.2 docs. Regarding semi-joins, and the HASH_SJ and MERGE_SJ hints, I don't think they came around until 8.1.5. Not real sure. I know I never used them in 7.x. But a reference of what parameters came into being and when says the always_semi_join parameter was introduced in 8.1.5.

I've talked with lots of people, including Stephane, about coding / not coding with knowledge of init parameters as well as the use of hints. Stephane makes the point that lots of code will be around for years and who knows what will change so why lock something in right now that might not be best in the long run. And I agree with that point. But I also don't think we should tune SQL in a vacuum. For example, if I have an application in 8i where 90% of the anti-joins are best served by hash aj's, and 8i can't do hash aj's when using NOT EXISTS, I might very well go with always_anti_join = HASH and code using the NOT IN (the much maligned NOT IN, my cause of the moment ;-)) and use the NOT EXISTS where a nested loops anti join is appropriate. And hopefully 9i will make the right choices and I don't even worry about it anymore.

Hints. That gets tricky. I try to avoid them if I can. I have fixed many problem queries by simply writing them in a more sane manner (just had an awful query this Friday that ran for hours, a simple rewrite, without hints, dropped it to seconds). But in some cases you just can't avoid the use of hints. In those cases where they are needed, I give as few hints as possible to get the desired result. In other words, I don't fully specify every access and join method. And the reason is similar to what you said, I want to leave the optimizer some room to make judgments as the nature of the data changes, and, changes in the CBO are introduced. And when I do use hints, I emphasize that the hint is good for the query and data at this point in time and may not be appropriate on down the road. But whether you fully hint every access and join method and the order of the joins, or, specify the minimum hints necessary to "correct" the plan, can be a double-edged sword. If you don't fully qualify, the plan can still change and hopefully for the better, but sometimes for the worse (just saw this happen to a prior client), but if you fully qualify, the data might change so that later you still need to come in and make changes. And the same holds true with outlines -- we are saying "this is the best way under I tell you otherwise". Though I still see good uses for outlines.

10053 traces and seeing inside the CBO's head. Boy I would love more information on this! I think a better understanding of this output could be very helpful. For example, how many times have you decided to, for example, not gather stats on a particular index and/or column because the CBO was always making the wrong choice? A somewhat unscientific and undisciplined approach. Though I haven't used the DBMS_STATS package to manipulate stats, I could see where a better knowledge of the CBO calcs and the 10053 trace might allow us to take a more scientific approach to fixing a problem query without touching the code (and many times we can't touch the code). Case in point is the recent example where the optimizer_index_cost_adj was set to 1 and the CBO choosing to use a full index scan on a index/column not referenced in the query to retrieve each row. Oracle Support's response is to change to 100 (which lots of people don't agree with either). But who wants to make that change on an existing production system? It's suicide since you could be changing the plan's of an untold number of queries! So, maybe leave it there (not sure *why* it is there at 1 but they had a good reason) and don't impact everything. And then tweak the stats on a single item. Sure, that can still have an impact on more than just one query, but at least it doesn't open the door to potentially changing the plans on 100's of SQL statements. Or just go ahead and modify the code to add a hint to that problem query.

Sorry for the length (it's half of what it was after I went back and edited ;-)) but the CBO behavior and query optimization fascinates me since we so often get a huge bang for the buck return. We can tune the DB and OS to death, and I don't mean to imply we shouldn't look at those things, but a SQL statement or two and/or poor application logic can bring the perfectly tuned system to it's knees. I know, that's hardly an original thought ;-). Most people, especially many of those on this list, know that, but it's surprising how many people don't and focus on the wrong areas, to the point of buying new hardware and still having poor performance. The case I'm thinking of from 3 years back, I wish they had called me *before* buying the new box -- I would have *happily* accepted half the amount of money spent on the new hardware (which didn't solve the problem), I would be semi-retired, and the 2 SQL statements that were causing all the problems would have been fixed. Everybody happy then ;-)

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonathan
> Lewis
> Sent: Sunday, March 17, 2002 4:13 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Fav. Urban Legend...
>
>
>
> Layyr,
>
> I can't remember the details of what examples
> I have tried so far, but it's certainly been entertaining
> trying to map all the things that the optimizer will do.
>
> Like Stefane, I really try to avoid fixing local problems
> with init.ora parameters (especially hidden ones) because
> of global side-effects, and I also prefer to avoid hints
> simply because they might stop Oracle from finding an
> even better path in the next release. However, I do think
> that hints are a safe option - when used judiciously and
> with full knowledge of the data - because stored execution
> paths (outlines) depend on them so much.
>
> So, in case you haven't spotted them yet in 9i, I wonder
> if the rmain reason why the anti/semi join parameters
> have disappeared is because the following 6 hints are
> now published:
>
> hash_aj
> merge_aj
> nl_aj
>
> hash_sj
> merge_sj
>
> nl_sj
>
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar - UK, April 3rd - 5th
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: 16 March 2002 18:55
>
>
> |Things start to get *really* interesting with the way the
> CBO
> |can transform and choose access paths for NOT IN / NOT EXISTS and IN
> /
> |EXISTS. For example, a NOT EXISTS can now (9i) be transformed into a
> HASH or
> |SEMI anti-join. Don't think that was possible in earlier versions (or
> at
> |least I couldn't make it happen)
> |
> |This also has a downside in a way. For example, in 8i with
> always_anti_join
> |set to hash, if I *know* a correlated nested loops anti-join approach
> is
> |preferred, I can code a correlated NOT EXISTS and rely upon a nested
> loops
> |anti-join. On the other hand, if I *know* the criteria and data is
> such that
> |a hash anti-join is preferable for that query, I would code the query
> using
> |a NOT IN, and assuming the condition for a hash anti join are met, I
> would
> |get the hash anti join. I can't depend on that in 9i unless I set the
> |"_always_anti_join" parameter. Hopefully the CBO will make the right
> choices
> |and I will not have to set it or worry about it.
> |
> |Larry G. Elkins

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).