Home » RDBMS Server » Performance Tuning » Merge over dblink - too slow..
Merge over dblink - too slow.. [message #212570] |
Fri, 05 January 2007 21:52 |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Sun Dec 08 07:23:13 CST 2024
|