oracle跨库关联慢,跨DBLINKInsertinto慢的问题解决

一、问题描述

今天用户给出一个SQL&＃xff0c;select执行很快&＃xff0c;但是insert进表时就很慢&＃xff0c;半天没个动静。

SQL执行用时&＃xff1a;310s

INSERT INTO uop_sta_cen.WXL_IDC_STATE

(INS_DATE, DOMAIN_ID, EPARCHY_CODE, CUST_ID, USER_ID, SERIAL_NUMBER)

SELECT SYSDATE,

&＃39;3&＃39;,

EPARCHY_CODE,

to_char(CUST_ID),

to_char(USER_ID),

SERIAL_NUMBER

FROM UCR_CRM3.TF_F_USER&＃64;UQRY_SEL_TO_HACRMDB22 T

WHERE T.REMOVE_TAG &＃61; &＃39;0&＃39;

AND T.USER_ID IN

(SELECT A.USER_ID

FROM UCR_CRM3.TF_F_USER_SVC&＃64;UQRY_SEL_TO_HACRMDB22 A

WHERE A.SERVICE_ID IN (&＃39;46000003&＃39;, &＃39;46000005&＃39;, &＃39;46000006&＃39;)

AND SYSDATE BETWEEN A.START_DATE AND A.END_DATE);

单独执行SELECT,用时0.078s

SELECT SYSDATE,

&＃39;3&＃39;,

EPARCHY_CODE,

to_char(CUST_ID),

to_char(USER_ID),

SERIAL_NUMBER

FROM UCR_CRM3.TF_F_USER&＃64;UQRY_SEL_TO_HACRMDB22 T

WHERE T.REMOVE_TAG &＃61; &＃39;0&＃39;

AND T.USER_ID IN

(SELECT A.USER_ID

FROM UCR_CRM3.TF_F_USER_SVC&＃64;UQRY_SEL_TO_HACRMDB22 A

WHERE A.SERVICE_ID IN (&＃39;46000003&＃39;, &＃39;46000005&＃39;, &＃39;46000006&＃39;)

AND SYSDATE BETWEEN A.START_DATE AND A.END_DATE)

二、原因分析

从上面执行计划&＃xff0c;可以看出&＃xff0c;连接方式有变化&＃xff0c;Nested loops-->Hash join&＃xff0c;这就是慢的原因。

不用DBLINK&＃xff0c;直接本地INSERT很快&＃xff0c;也是使用NL的方式。

三、问题解决

可用两种方法解决&＃xff1a;

不用跨DBLINK&＃xff0c;直接本地INSERT,再通过DBLINK读取即可;

使用hint方式&＃xff0c;强制使用NL连接。

INSERT INTO uop_sta_cen.WXL_IDC_STATE

(INS_DATE, DOMAIN_ID, EPARCHY_CODE, CUST_ID, USER_ID, SERIAL_NUMBER)

SELECT

/*&＃43; use_nl(A,T)*/

SYSDATE,

&＃39;3&＃39;,

T.EPARCHY_CODE,

to_char(T.CUST_ID),

to_char(T.USER_ID),

SERIAL_NUMBER

FROM UCR_CRM3.TF_F_USER&＃64;DBLNK_NGCRM2 T,(SELECT distinct USER_ID

FROM UCR_CRM3.TF_F_USER_SVC&＃64;DBLNK_NGCRM2

WHERE SERVICE_ID IN (&＃39;46000003&＃39;, &＃39;46000005&＃39;, &＃39;46000006&＃39;)

AND SYSDATE BETWEEN START_DATE AND END_DATE) A

WHERE T.REMOVE_TAG &＃61; &＃39;0&＃39;

AND T.USER_ID &＃61;A.user_id

用时

0.109S