Home » RDBMS Server » Performance Tuning » Merge over dblink - too slow..
Merge over dblink - too slow.. [message #212570] Fri, 05 January 2007 21:52 Go to next message
lotusdeva
Messages: 201
Registered: March 2005
Senior Member
Hello --

I am using MERGE in Oracle 9i to synchronize data between two databases using a dblink. My MERGE looks as follows:

MERGE INTO pws_crs_info n1
USING (SELECT ssbsect_term_code, ssbsect_crse_numb,
ssbsect_subj_code, ssbsect_seq_numb,
ssbsect_crn, ssbsect_crse_title
FROM ssbsect@PROD.US.ORACLE.COM,
scbcrse@PROD.US.ORACLE.COM,
sobptrm@PROD.US.ORACLE.COM,
stvsubj@PROD.US.ORACLE.COM,
stvterm@PROD.US.ORACLE.COM
WHERE stvsubj_code = ssbsect_subj_code
AND scbcrse_subj_code = ssbsect_subj_code
AND scbcrse_crse_numb = ssbsect_crse_numb
AND scbcrse_eff_term =
(SELECT MAX(scbcrse_eff_term)
FROM saturn.scbcrse@PROD.US.ORACLE.COM x
WHERE x.scbcrse_eff_term <= ssbsect_term_code
AND x.scbcrse_subj_code = ssbsect_subj_code
AND x.scbcrse_crse_numb = ssbsect_crse_numb)
AND sobptrm_term_code = ssbsect_term_code )) e1
ON ( pws_ssbsect_term_code = e1.ssbsect_term_code
AND n1.pws_ssbsect_crn = e1.ssbsect_crn )
WHEN MATCHED THEN UPDATE
SET n1.pws_ssbsect_crse_numb = e1.ssbsect_crse_numb,
n1.pws_ssbsect_subj_code = e1.ssbsect_subj_code,
n1.pws_ssbsect_sec_numb = e1.ssbsect_seq_numb,
n1.pws_ssbsect_crs_title = e1.ssbsect_crse_title
WHEN NOT MATCHED THEN
INSERT ( pws_ssbsect_term_code, pws_ssbsect_crse_numb,
pws_ssbsect_subj_code, pws_ssbsect_sec_numb,
pws_ssbsect_crn, pws_ssbsect_crs_title)
VALUES(e1.ssbsect_term_code, e1.ssbsect_crse_numb,
e1.ssbsect_subj_code, e1.ssbsect_seq_numb,
e1.ssbsect_crn, e1.ssbsect_crse_title,);

-----------------------------------------------

The problem is that the select iteself takes like 2 seconds to run, but the above merge statement takes 5 minutes to run. Is there anyway to speed it up? Thank you!
Re: Merge over dblink - too slow.. [message #212709 is a reply to message #212570] Sun, 07 January 2007 21:15 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try a DRIVING_SITE hint.

MERGE INTO pws_crs_info n1
USING (SELECT /*+ DRIVING_SITE(ssbsect)*/
ssbsect_term_code, ssbsect_crse_numb,
ssbsect_subj_code, ssbsect_seq_numb,
ssbsect_crn, ssbsect_crse_title 
FROM ssbsect@PROD.US.ORACLE.COM,
scbcrse@PROD.US.ORACLE.COM, 
sobptrm@PROD.US.ORACLE.COM, 
stvsubj@PROD.US.ORACLE.COM, 
stvterm@PROD.US.ORACLE.COM
WHERE stvsubj_code = ssbsect_subj_code
AND scbcrse_subj_code = ssbsect_subj_code
AND scbcrse_crse_numb = ssbsect_crse_numb
AND scbcrse_eff_term = 
(SELECT MAX(scbcrse_eff_term) 
FROM saturn.scbcrse@PROD.US.ORACLE.COM x
WHERE x.scbcrse_eff_term <= ssbsect_term_code 
AND x.scbcrse_subj_code = ssbsect_subj_code 
AND x.scbcrse_crse_numb = ssbsect_crse_numb) 
AND sobptrm_term_code = ssbsect_term_code )) e1
ON ( pws_ssbsect_term_code = e1.ssbsect_term_code
AND n1.pws_ssbsect_crn = e1.ssbsect_crn )
WHEN MATCHED THEN UPDATE 
SET n1.pws_ssbsect_crse_numb = e1.ssbsect_crse_numb,
n1.pws_ssbsect_subj_code = e1.ssbsect_subj_code, 
n1.pws_ssbsect_sec_numb = e1.ssbsect_seq_numb,
n1.pws_ssbsect_crs_title = e1.ssbsect_crse_title
WHEN NOT MATCHED THEN 
INSERT ( pws_ssbsect_term_code, pws_ssbsect_crse_numb, 
pws_ssbsect_subj_code, pws_ssbsect_sec_numb,
pws_ssbsect_crn, pws_ssbsect_crs_title)
VALUES(e1.ssbsect_term_code, e1.ssbsect_crse_numb,
e1.ssbsect_subj_code, e1.ssbsect_seq_numb,
e1.ssbsect_crn, e1.ssbsect_crse_title,);


Ross Leishman
Re: Merge over dblink - too slow.. [message #212863 is a reply to message #212709] Mon, 08 January 2007 11:08 Go to previous message
lotusdeva
Messages: 201
Registered: March 2005
Senior Member
its much better with the hint, thank you!
Previous Topic: Performance Forms 6i - Where clause in block
Next Topic: MERGE but no PK on the table
Goto Forum:
  


Current Time: Sun Dec 08 07:23:13 CST 2024