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

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

RE: 9i CBO favoring nested loops?

From: <Govind.Arumugam_at_alltel.com>
Date: Fri, 09 May 2003 11:02:30 -0800
Message-ID: <F001.00595A18.20030509110230@fatcity.com>


When we had severe performance problems after upgrading to 9i, I came up with a work-around.  

explain plan for
select c.marketcd, c.nodenbr from pvxdbe1a.tmp_collnodesummary c where c.marketcd in ('261','262','281','320','370') and exists (SELECT 1 FROM noderelated nr, nodemaster nm WHERE nr.nodenbr = nm.nodenbr
AND c.nodenbr = nr.nodeabovenbr
AND c.marketcd = nm.marketcd) ;

Plan Table


   | Operation                                 |  Name                    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------------------------------------------
0,0| SELECT STATEMENT | | 1 | 25 | 11982 | | | | | | 1,0| HASH JOIN SEMI | | 1 | 25 | 11982 | 9,03 | P->S | QC (RANDOM)| | | 2,1| INLIST ITERATOR | | | | | 9,00 | S->P | HASH | | | 3,2| INDEX RANGE SCAN |TMP_COLLNODESUMMARYC | 288K| 3M| 1008 | | | | | | 4,1| VIEW |VW_SQ_1 | 2M| 28M| 11629 | 9,02 | P->P | HASH | | | 5,4| HASH JOIN | | 2M| 89M| 11629 | 9,02 | PCWP | | | | 6,5| PARTITION RANGE INLIST | | | | | 9,02 | PCWP | |KEY(I) |KEY(I) | 7,6| TABLE ACCESS FULL |NODEMASTER | 734K| 10M| 1655 | 9,01 | P->P | BROADCAST |KEY(I) |KEY(I) | 8,5| PARTITION RANGE ALL | | | | | 9,02 | PCWP | | 1 | 20 | 9,8| TABLE ACCESS FULL |NODERELATED | 21M| 460M| 9975 | 9,02 | PCWP | | 1 | 20 |
-------------------------------------------------------------------------------------------------------------------------------------------

Changed EXISTS clause into 1 IN  

explain plan for
select c.marketcd, c.nodenbr from pvxdbe1a.tmp_collnodesummary c where c.marketcd in ('261','262','281','320','370') and 1 in (SELECT 1 FROM noderelated nr, nodemaster nm WHERE nr.nodenbr = nm.nodenbr
AND c.nodenbr = nr.nodeabovenbr
AND c.marketcd = nm.marketcd) ;  

Plan Table


   | Operation                                 |  Name                    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------------------------------------------
0,0| SELECT STATEMENT | | 14K| 183K| 353 | | | | | | 1,0| INLIST ITERATOR | | | | | | | | | | 2,1| INDEX RANGE SCAN |TMP_COLLNODESUMMARYC | 14K| 183K| 1008 | | | | | | 3,2| TABLE ACCESS BY GLOBAL INDEX ROWID |NODEMASTER | 1 | 15 | 2 | | | | ROWID | ROW L | 4,3| NESTED LOOPS | | 3 | 111 | 3 | | | | | | 5,4| INDEX RANGE SCAN |NODERELATEDNC1 | 3 | 66 | 4 | | | | | | 6,4| PARTITION RANGE ITERATOR | | | | | | | | KEY | KEY | 7,6| INDEX RANGE SCAN |NODEMASTERC | 1 | | 1 | | | | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------------------------------

This plan was identical to 8.1.7 query plan.  

I have tried the work-arounds suggested by Thomas Jeff.  

alter session set "_PUSH_JOIN_PREDICATE" = false; 
alter session set "_PUSH_JOIN_UNION_VIEW" = false;
alter session set "_UNNEST_SUBQUERY" = false;
 

explain plan for
select c.marketcd, c.nodenbr from pvxdbe1a.tmp_collnodesummary c where c.marketcd in ('261','262','281','320','370') and exists (SELECT 1 FROM noderelated nr, nodemaster nm WHERE nr.nodenbr = nm.nodenbr
AND c.nodenbr = nr.nodeabovenbr
AND c.marketcd = nm.marketcd) ;

Plan Table


   | Operation                                 |  Name                    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------------------------------------------
0,0| SELECT STATEMENT | | 14K| 183K| 353 | | | | | | 1,0| INLIST ITERATOR | | | | | | | | | | 2,1| INDEX RANGE SCAN |TMP_COLLNODESUMMARYC | 14K| 183K| 1008 | | | | | | 3,2| TABLE ACCESS BY GLOBAL INDEX ROWID |NODEMASTER | 1 | 15 | 2 | | | | ROWID | ROW L | 4,3| NESTED LOOPS | | 3 | 111 | 3 | | | | | | 5,4| INDEX RANGE SCAN |NODERELATEDNC1 | 3 | 66 | 4 | | | | | | 6,4| PARTITION RANGE ITERATOR | | | | | | | | KEY | KEY | 7,6| INDEX RANGE SCAN |NODEMASTERC | 1 | | 1 | | | | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------------------------------

This is also identical to the query plan in 8.1.7.  

I had mentioned this improvement during the Round Table discussion on Dataabse Performance Tuning during IOUG last week.  

Still Oracle has not come back with a solid answer on this, so to speak. They have noted this as a bug # 2875453  

Thanks Tom!  

Govind  


 

Sent: Thursday, May 08, 2003 1:33 PM
To: Multiple recipients of list ORACLE-L

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: <Govind.Arumugam_at_alltel.com
  INET: Govind.Arumugam_at_alltel.com

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 Fri May 09 2003 - 14:02:30 CDT

Original text of this message

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