Re: Musings on tuning and the optimizer

• Original Message -----

> Have run across some interesting things while reading up on the
optimizer.

Same here! :-)

>
> Always thought that the RBO joins your tables in the order found in
the
> FROM clause? Think again.

Actually, I found one in Metalink that says with RBO, it's the reverse order of the FROM clause. Which I was aware of. But it also says: if there are NO STATS whatsoever and you use a hint that forces the CBO to be used or it is the default, the order of tables is left to right. Like what you get when using the hint ORDERED.

Also, a few other interesting "rules":

• FIRST_ROWS tends to favour NESTED LOOPS.
• ALL_ROWS tends to favour HASH and table scans. <old hat in the above>

EXCEPT (!) if the CBO finds a table with no stats in a join. Then it's most likely hash, for both settings above.

And optimizer_mode is CHOOSE and there is a mix of tables in the join with/without stats, then ALL_ROWS is the result.

>
> Speaking of the ORDERED hint, it can greatly reduce parse times when
> joining many tables. Obvious when you think about it.

That, it certainly does! My experience too. It never hurts to "help" the optimizer do its job.

> All kinds of interesting stuff when you Read The Fine Manual. ;)
>

And a few others. I found out the problem I reported a while ago with CBO suddenly going South on hash scan joins and completely ignoring nested loops or indexes is actually an introduced problem due to a change in CBO rules after 8.0.4. It first affected SAP users. It used to be fixed by a couple of events which due to their usefulness, became the two "new" optimizer cost adjust parameters in later versions of 8.0 and some of the 8.1.

<groan... roll on 9i, I've had enough of this optimizer!>

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au
http://www.users.bigpond.net.au/the_Den

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nuno Souto
  INET: nsouto_at_nsw.bigpond.net.au

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