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
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: