Problem with Query Speed on different instances

From: Marc Eggenberger <marc.eggenberger_at_remove.itc.alstom.com>
Date: Thu, 10 Feb 2005 14:26:01 +0100
Message-ID: <MPG.1c757f5327b49469896a8@iww.cacti.ch.alstom.com>

Hi there ...

I have the following environment:

Windows 2000 Adv Server with SP4, Dual CPU Pentium III 1.2GHz with 4GB RAM. Oracle 8.1.7.0.0

I have 2 instances, one a productive and a test instance. Both instances have the same objects (tables, views, indexes etc). Statistics have been updated on both instances. The productive instance is not very busy. I also tested those queries on times when there is none to maybe very little activity, same results.

The table TDM_TOOL has about 19k entries, TDM_TOOLVALUES about 62k rows.

The application is done by an external partner. Just got a message that this query is fast on one instance, but very slow on the other:

The query is:

SELECT a.toolid, c1.valnum AS YS , c1.TOOLCLASSFIELDSPOS AS POS1, c1.EMPTYFLAG AS FLAG1,

                 c2.valnum AS YS , c2.TOOLCLASSFIELDSPOS AS POS2, 
c2.EMPTYFLAG AS FLAG2,
				 c3.valnum AS YS , c3.TOOLCLASSFIELDSPOS AS 
POS3, c3.EMPTYFLAG AS FLAG3,
				 c4.valnum AS YS , c4.TOOLCLASSFIELDSPOS AS 
POS4, c4.EMPTYFLAG AS FLAG4,
				 c5.valnum AS YS , c5.TOOLCLASSFIELDSPOS AS

POS5, c5.EMPTYFLAG AS FLAG5

 FROM tms.TDM_TOOL a,  		
      tms.TDM_TOOLVALUES c1,		
      tms.TDM_TOOLVALUES c2,
      tms.TDM_TOOLVALUES c3,
      tms.TDM_TOOLVALUES c4,
      tms.TDM_TOOLVALUES c5

WHERE TOOLCLASSID IN

('D01','D04','D06','D07','D08','M01','M02','M03','M04','M05','M06','M07'
,'M08','M09','M10','M12','T01','T02','T03','T04','T05','T06','T07','T08'
,'T09','T10')

AND c1.toolid (+) = a.toolid
AND c1.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM tms.TDM_TOOLCLASSFIELDS h

	                                    WHERE a.TOOLCLASSID = 
h.TOOLCLASSID 	
                                              AND h.functypeid  =

'YS'),0)

AND c2.toolid (+) = a.toolid
AND c2.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM tms.TDM_TOOLCLASSFIELDS h

	                                    WHERE a.TOOLCLASSID = 
h.TOOLCLASSID 	 
                                             AND h.functypeid  =

'XS' ),0)

AND c3.toolid (+) = a.toolid
AND c3.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM tms.TDM_TOOLCLASSFIELDS h

	                                    WHERE a.TOOLCLASSID = 
h.TOOLCLASSID 	
                                              AND h.functypeid  = 'R

CORNER' ),0)
AND c4.toolid (+) = a.toolid
AND c4.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM tms.TDM_TOOLCLASSFIELDS h

	                                    WHERE a.TOOLCLASSID = 
h.TOOLCLASSID 	
                                              AND h.functypeid  =

'SPIN' ),0)

AND c5.toolid (+) = a.toolid
AND c5.TOOLCLASSFIELDSPOS (+) = NVL((SELECT h.TOOLCLASSFIELDSPOS FROM tms.TDM_TOOLCLASSFIELDS h

	                                    WHERE a.TOOLCLASSID = 
h.TOOLCLASSID 	
                                              AND h.functypeid  = 'A

KAPPA' ),0) On the test instance I get the following explain path:

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out
	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		176  	 	904  	 
	      	             	 
  NESTED LOOPS OUTER		176  	26 K	904  	 	      
	             	 
    NESTED LOOPS OUTER		176  	21 K	728  	 	      
	             	 
      NESTED LOOPS OUTER		176  	17 K	552  	 	      
	             	 
        NESTED LOOPS OUTER		176  	12 K	376  	 	      
	             	 
          NESTED LOOPS OUTER		176  	7 K	200  	 
	      	             	 
            TABLE ACCESS FULL	TMS.TDM_TOOL	176  	3 K	24  	 
	      	             	 
            TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLVALUES	61 K	1 M
	1  	 	      	             	 
              INDEX UNIQUE SCAN	TMS.TDM_TOOLVALUES_IDX	61 K	 
	 	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS
	1  	25  	4  	 	      	             	 
                  INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 
	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLVALUES	61 K	1 M
	1  	 	      	             	 
            INDEX UNIQUE SCAN	TMS.TDM_TOOLVALUES_IDX	61 K	 
	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  
	25  	4  	 	      	             	 
                INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 
	2  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLVALUES	61 K	1 M
	1  	 	      	             	 
          INDEX UNIQUE SCAN	TMS.TDM_TOOLVALUES_IDX	61 K	 	 
	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  
	25  	4  	 	      	             	 
              INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 
	2  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLVALUES	61 K	1 M	1  
	 	      	             	 
        INDEX UNIQUE SCAN	TMS.TDM_TOOLVALUES_IDX	61 K	 	 
	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  
	25  	4  	 	      	             	 
            INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 
	2  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLVALUES	61 K	1 M	1  
	 	      	             	 
      INDEX UNIQUE SCAN	TMS.TDM_TOOLVALUES_IDX	61 K	 	 
	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  
	25  	4  	 	      	             	 
          INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 	2

On the productive instance I get the following explain path:

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out
	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		9 K	 	4921  	 
	      	             	 
  FILTER		  	 	 	 	      	             	 
    MERGE JOIN OUTER		9 K	1 M	4921  	 	      
	             	 
      MERGE JOIN OUTER		9 K	1 M	3973  	 	      
	             	 
        MERGE JOIN OUTER		9 K	871 K	3025  	 	      
	             	 
          SORT JOIN		9 K	639 K	2077  	 	      
	             	 
            HASH JOIN OUTER		9 K	639 K	1826  	 	      
	             	 
              HASH JOIN OUTER		9 K	409 K	751  	 
	      	             	 
                TABLE ACCESS FULL	TMS.TDM_TOOL	9 K	181 K	24  	 
	      	             	 
                TABLE ACCESS FULL	TMS.TDM_TOOLVALUES	61 K	1 M	41  
	 	      	             	 
              TABLE ACCESS FULL	TMS.TDM_TOOLVALUES	61 K	1 M	41  
	 	      	             	 
          SORT JOIN		61 K	1 M	948  	 	      
	             	 
            TABLE ACCESS FULL	TMS.TDM_TOOLVALUES	61 K	1 M	41  
	 	      	             	 
        SORT JOIN		61 K	1 M	948  	 	      
	             	 
          TABLE ACCESS FULL	TMS.TDM_TOOLVALUES	61 K	1 M	41  	 
	      	             	 
      SORT JOIN		61 K	1 M	948  	 	      
	             	 
        TABLE ACCESS FULL	TMS.TDM_TOOLVALUES	61 K	1 M	41  	 
	      	             	 
    TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  	13  
	5  	 	      	             	 
      INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 	2  
	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  	13  
	5  	 	      	             	 
      INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 	2  
	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  	13  
	5  	 	      	             	 
      INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 	2  
	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  	13  
	5  	 	      	             	 
      INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 	2  
	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	TMS.TDM_TOOLCLASSFIELDS	1  	13  
	5  	 	      	             	 
      INDEX RANGE SCAN	TMS.TDM_TOOLCLASSFIELDS_IDX	1  	 	2

I'm a bit confused .. can someone direct me to the right directions? What should I try, change? And why?
I'm already taking snapshots but they couldnt help me yet ... and Oraperf.com is temporarly offline ...

Thanks for any help, hints.

-- 
mfg
Marc Eggenberger

Received on Thu Feb 10 2005 - 07:26:01 CST

Problem with Query Speed on different instances - different execution plan