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