Skip to Main Content
  • Questions
  • Improve the performance of joining remote tables

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Saibabu.

Asked: January 23, 2020 - 3:11 pm UTC

Last updated: May 23, 2022 - 10:17 am UTC

Version: 11g/12c

Viewed 1000+ times

You Asked

Hi Tom,

Hope you are doing good !!!

I am very glad to writing this post to get valuable information from you. We were in situation to join multiple remote tables joining in order to get expected data into our schema but its taking longer execution time (i.e. 5k records - 30 secs)

Performance is good when passing hint /*+DRIVING_SITE(t)*/ when joining one remote table, but I could not much improvement with multiple remote tables join.

Could you please how can we improve the performance ?

Thank you so much in advance !!!

SELECT lai.ari_rec_id
FROM   lsmv_aer_info@db_lnk lai,
       master_tab mc
WHERE  mc.ari_rec_id = lai.ari_rec_id
-- Execution time : 30 sec, record count : 5K

Plan
SELECT STATEMENT  ALL_ROWSCost: 1,654  Bytes: 99,880  Cardinality: 4,994    
 3 HASH JOIN  Cost: 1,654  Bytes: 99,880  Cardinality: 4,994   
  1 TABLE ACCESS FULL TABLE IRT_LAND_LSP.MASTER_CASE Cost: 5  Bytes: 35,000  Cardinality: 5,000  
  2 REMOTE REMOTE SERIAL_FROM_REMOTE LSMV_AER_INFO db_lnkCost: 1,636  Bytes: 32,953,518  Cardinality: 2,534,886  


Using Oracle Hint : DRIVING_SITE

SELECT /*+DRIVING_SITE(lai)*/
       lai.ari_rec_id
FROM   lsmv_aer_info@db_lnk lai,
       master_tab mc
WHERE  mc.ari_rec_id = lai.ari_rec_id
-- Execution time : 1 sec, record count : 5K

Explain Plan
SELECT STATEMENT REMOTE  ALL_ROWSCost: 5  Bytes: 69,916  Cardinality: 4,994    
 3 NESTED LOOPS  Cost: 5  Bytes: 69,916  Cardinality: 4,994   
  1 REMOTE REMOTE SERIAL_FROM_REMOTE MASTER_CASE !Cost: 4  Bytes: 35,000  Cardinality: 5,000  
  2 INDEX RANGE SCAN INDEX ABV_LSMV10B2_SND_DM.IDX_ARI_RECID_17 LSABVDEVCost: 1  Bytes: 7  Cardinality: 1  


Regards,
Sai M

and Chris said...

When joining many tables over a database link, often you lose lots of time transferring the rows over the network.

Avoid this by joining the remote tables together in a subquery. And the same for the local tables.

Then join the result of these subqueries.

For example:

with remote as (
  select /*+ no_merge */* from t1@rem
  join   t2@rem on ...
) , local as (
  select /*+ no_merge */* from local_t1
  join   local_t2 on ...
) 
  select * from remote
  join   local on ...


For more details on this, see this Ask TOM Office Hours video on tuning distributed queries:


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sai M, January 24, 2020 - 5:54 pm UTC

Thank you so much Tom !!! It helps me a lot.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database