Home » RDBMS Server » Performance Tuning » Difference in Execution timing
Difference in Execution timing [message #163670] Fri, 17 March 2006 15:37 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi

This is the SQL
SELECT l.LOCKBOX_NB ,SUM(bd.VOL_CT) VOL_CT , dd.DDA_NB, sc.GPC_ID, dd.BNK_ID
	FROM BILL_DATA bd, LOCKBOX l, LOCKBOX_DDA ldda, DDA dd, GPC_CD sc
	WHERE bd.LOCKBOX_ID = l.LOCKBOX_ID
	AND l.SITE_ID NOT IN (SELECT SITE_ID FROM SITE WHERE SITE_CD = '06')
	AND l.LOCKBOX_ID = ldda.LOCKBOX_ID
	AND ldda.DDA_ID = dd.DDA_ID
	AND bd.GPC_CD_ID = sc.GPC_ID
	AND ldda.DDA_TYPE_CD = 'B'
	AND bd.BILL_DT BETWEEN '01-dec-2005' AND '21-jan-2006'
	GROUP BY l.LOCKBOX_NB, dd.DDA_NB, sc.GPC_ID, dd.BNK_ID
	ORDER BY l.LOCKBOX_NB ASC


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

SELECT STATEMENT Optimizer Mode=CHOOSE		3 K	 	95  	 	      	             	 
  SORT GROUP BY		3 K	261 K	95  	 	      	             	 
    NESTED LOOPS		3 K	261 K	43  	 	      	             	 
      HASH JOIN ANTI		3 K	240 K	43  	 	      	             	 
        HASH JOIN		3 K	227 K	37  	 	      	             	 
          HASH JOIN		1 K	46 K	16  	 	      	             	 
            HASH JOIN		1 K	30 K	9  	 	      	             	 
              TABLE ACCESS FULL	R1APP60.LOCKBOX_DDA	1 K	12 K	4  	 	      	             	 
              TABLE ACCESS FULL	R1APP60.DDA	1 K	29 K	4  	 	      	             	 
            TABLE ACCESS FULL	R1APP60.LOCKBOX	2 K	32 K	6  	 	      	             	 
          TABLE ACCESS FULL	R1APP60.BILL_DATA	7 K	164 K	19  	 	      	             	 
        TABLE ACCESS FULL	R1APP60.SITE	1  	8  	2  	 	      	             	 
      INDEX UNIQUE SCAN	R1APP60.XPK_SRVCD	1  	6  	 	 	      	             	 

This will get 6500 rows in Developement and 35000 rows in Production.

Time taken in Dev is 3 sec and in Production 30 sec, why so much Difference .
The plan is from Development i can't take it form Production since i don't have access, In Development i see that it's doing FTS, STATS are updated, Tables and Indexes are analyzed,
Is some thing wrong in SQL, any thing can be done or written better..

Thanks.
Re: Difference in Execution timing [message #163689 is a reply to message #163670] Sat, 18 March 2006 00:52 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Tuning on a development-database is a complete waste of time if your development-database has other data (volume and distribution) than your production-database. Better take the day off, because you are tuning for the _wrong_ data.
Previous Topic: Log File Sync
Next Topic: Transaction never fails ..where
Goto Forum:
  


Current Time: Sat Nov 23 15:07:59 CST 2024