| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 9i CBO favoring nested loops?
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.
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
 |
 |