Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> 9i CBO favoring nested loops?

9i CBO favoring nested loops?

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Thu, 08 May 2003 09:32:44 -0800
Message-ID: <F001.0059408D.20030508093244@fatcity.com>


One of our remote production DBAs passed the following note from another client of
his. Intuitively, this sounds all wrong but I'm not knowledgeable enough to

eloquently state exactly why, other then that it sounds like a simplification that just
does not make sense considering the complexity of the CBO and the various parameters
that can influence it's behavior. I was hoping one of the gurus on this list
could comment as to it's validity.

Begin note:

A query took 9 minutes to complete in 8i and after 24 hours of CPU time in 9i, it still
hadn't completed and customer ended up killing the job. In 8i, CBO favored mostly Hash
Joins and in 9i it favored Nested Loops.

The culprit was the changing of the default setting for several hidden parameters.  

In 8i, the default setting was FALSE for the following hidden parameters:
_PUSH_JOIN_PREDICATE (enable pushing join predicate inside a view)
_PUSH_JOIN_UNION_VIEW (enable pushing join predicate inside a union view)
_UNNEST_SUBQUERY (enable unnesting of correlated subqueries)

In 9i, the default was changed to TRUE. By setting the parameters back to FALSE, the explain
plans and the performance are close to what they were in 8i.



Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.

Email: jeff.thomas_at_thomson.net

Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thomas Jeff
  INET: jeff.thomas_at_thomson.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Thu May 08 2003 - 12:32:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US