Avatar of NicksonKoh
NicksonKohFlag for Singapore asked on

Slow SQL when using dblink to a remote db

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

Open in new window

Oracle Database

Avatar of undefined
Last Comment
schwertner

8/22/2022 - Mon
Geert G

You are selecting from a view which includes the table NFSDBA
with an index on the table should solve the issue

4 TABLE ACCESS FULL NFSDBA.ITEMMST Cost: 97  Bytes: 444,570  Cardinality: 14,819
schwertner

Yes, but additionally you have to compute the statistics over the tables and indexes.
ASKER
NicksonKoh

I can add an index on the vitemgroupdesc column in the NFSDBA.itemmst table, but even without additional index, the performance of the SQL is already ok at a fast 234ms when executed directly. The problem just happen when I execute remotely from another database using dblinks.

As mentioned, I also tried changing the SELECT CASE to DECODE and the remote execution becomes just as fast??


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Geert G

i had this too once, sometimes the processing is done on the local side.
Like when using TO_CHAR and dates the processing is done local, not remote...
maybe same thing

my problem was

SELECT * FROM LOGS
WHERE TO_CHAR(DT_CREATED, 'YYYY') >= '2007'

This was processed on the local side (processing 1.4 million records -> 2 minutes)

SELECT * FROM LOGS
WHERE DT_CREATED > TO_DATE('2007-01-01 00:00:00',  'YYYY-MM-DD HH24:MI:SS')

This was processed on the remote side (processing 1.4 million records -> 4 seconds )


sventhan

> The problem just happen when I execute remotely from another database using dblinks.

try the hint "driving_site"

Forces query execution to be done at a user selected  site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.

/*+ DRIVING_SITE([@queryblock] <tablespec>) */
SELECT /*+ DRIVING_SITE(departments) */ *
FROM employees, departments@rsite
WHERE employees.department_id = departments.department_id;
schwertner

To speed up the dblink create a view based on the query.
Place this view on the remote site.
Create dblink on the local site.
Query the view from the local site using the dblink.

I have just experienced the same problem and this
approach speed up the query 8-10 times.

Using view on the remote site you force the remote site to do all
process steps decreasing the transfer of the data.

Also take in account that you use Sort operation - GROUB BY.
This will force the sort to be done at the local site.
This also increases the volume of the transfered data.
To avoid this - use remote view.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
NicksonKoh

Driving Site hints have no impact. Schwertner, I am also unable to use that suggested option as I can only query from the remote database.

Anymore ideas?
schwertner

If you can not create a view on the remote site use Donald Burleson suggestion:

Instead

SELECT a,b,c FROM gh@klm;

Use

SELECT * FROM (SELECT a,b,c FROM gh@klm);

Burleson says that this is the same, but my experiments this week
shows that this is not so.

Creating views on the remote site is the best option for me.

Also have a look here:

http://www.dba-oracle.com/t_sql_dblink_performance.htm
dbmullen

it's hard to tell but if ALL of the tables are on the remote site, try adding a HINT
 /*+ DRIVING_SITE(a) */

that will make the query run on the remote database, and just return the results.

SELECT   /*+ DRIVING_SITE(a) */
         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

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
NicksonKoh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
schwertner

9.2.0.1 has many bugs.
You can easily upgrade to 9.2.0.8.
Driving site doesn't impact the speed even in Oracle 11g.
Only creating and using a view is a working solution.