Re: Musings on tuning and the optimizer

On Monday 28 May 2001 04:45, Nuno Souto wrote:
> 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!>

Nuno, here's an interesting bit in the tuning manual that may take care of the hash join problem.

"For a view with multiple base tables on the right side of an outer join, the  optimizer can push the join predicate into the view (see "Pushing the  Predicate into the View") if the initialization parameter  _PUSH_JOIN_PREDICATE is set to TRUE or the accessing query contains the  PUSH_PRED hint.

 Pushing a join predicate is a cost-based transformation that can enable more  efficient access path and join methods, such as transforming hash joins into  nested loops joins, and full table scans to index scans. "

I haven't had a chance to try this, as I don't currently have access to a database with real data in it, but this could be the way to prevent those hash join problems when migrating to 8i from 7.x.

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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