troubleshooting Question

Slow SQL when using dblink to a remote db

Avatar of NicksonKoh
NicksonKohFlag for Singapore asked on
Oracle Database
11 Comments1 Solution7039 ViewsLast Modified:
Hi,

I have the attached SQL which when run in a remote db takes about 30s but when executed directly, it takes less than 500ms! Both database are Oracle 9.2.0.1. A TNSPing to the remote database takes only 80ms.

Can someone advise how I can make the SQL run just as fast on the remote db? Could this also be a Oracle bug? Because I notice if I change to decode, the timing improves dramatically and performs almost the same as remote.
SELECT   vitemgroupdesc,
         SUM (CASE
                 WHEN (a.nsalesyear * 100 + a.nsalesmonth) BETWEEN 200701 AND 200704
                    THEN DECODE (i.vbaseuom, 'LB', a.nbaseqty * 0.453592, 'GAL', a.nbaseqty * 3.7854, a.nbaseqty)
                 ELSE 0
              END
             ) qty1
    FROM monthendsales a, itemmst i, itemclassificationmst ic
   WHERE (a.vitemcode = i.vitemcode AND i.vitemclassificationcode = ic.vitemclassificationcode)
     AND a.vdivcode IN ('123')
GROUP BY vitemgroupdesc
 
Explain Plan From the Remote Database (Time Taken 39 s)
SELECT STATEMENT  CHOOSECost: 126  Bytes: 274,833  Cardinality: 3,393  				
	6 SORT GROUP BY  Cost: 126  Bytes: 274,833  Cardinality: 3,393  			
		5 NESTED LOOPS  Cost: 101  Bytes: 274,833  Cardinality: 3,393  		
			3 HASH JOIN  Cost: 101  Bytes: 74,921  Cardinality: 973  	
				1 REMOTE  Cost: 2  Bytes: 45,731  Cardinality: 973  
				2 REMOTE  Cost: 97  Bytes: 444,570  Cardinality: 14,819  
			4 REMOTE  Cost: 4  Bytes: 12  Cardinality: 3  	
 
Explain Plan from Actual DB (Time Taken 234 ms)
SELECT STATEMENT  CHOOSECost: 390  Bytes: 274,833  Cardinality: 3,393  						
	9 SORT GROUP BY  Cost: 390  Bytes: 274,833  Cardinality: 3,393  					
		8 NESTED LOOPS  Cost: 322  Bytes: 274,833  Cardinality: 3,393  				
			6 MERGE JOIN  Cost: 322  Bytes: 74,921  Cardinality: 973  			
				3 SORT JOIN  Cost: 43  Bytes: 45,731  Cardinality: 973  		
					2 TABLE ACCESS BY INDEX ROWID NFSDBA.MONTHENDSALES Cost: 2  Bytes: 45,731  Cardinality: 973  	
						1 INDEX RANGE SCAN NON-UNIQUE NFSDBA.IND_MTHENDSALS_DIVCODE_DIVMST Cost: 1  Cardinality: 389  
				5 SORT JOIN  Cost: 280  Bytes: 444,570  Cardinality: 14,819  		
					4 TABLE ACCESS FULL NFSDBA.ITEMMST Cost: 97  Bytes: 444,570  Cardinality: 14,819  	
			7 INDEX UNIQUE SCAN UNIQUE NFSDBA.PK_ITEMCLASSIFICATIONMST Bytes: 12  Cardinality: 3
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros