dblink跨库查询引发的性能问题

【背景】库历史订单转储，把生产库的竣工单转移到历史库。

生产库的订单表大概4kw数据，历史库上的一个辅助表HIS_ORDER_DELETE_INTELLIGENCE大概6w数据。

 

【现象】

Job在历史库执行，

 1 SELECT /*+ index(o IDX_OO_S_D) */
 2 
 3                     O.ID, O.ORDER_CLASS
 4 
 5                             FROMOM_ORDER@BAK2HIS O
 6 
 7                     WHERE O.ORDER_STATE IN ('10A', '10C', '10F','1DF','1RC')
 8 
 9                              AND O.ORDER_CLASS IN ('10S', '1MP')
10 
11                              AND O.STATE_DATE < TODATE
12 
13                              AND O.STATE_DATE >= FROMDATE
14 
15                              AND ROWNUM <= 5000
16 
17                              AND NOT EXISTS
18 
19                     (SELECT 1 FROM HIS_ORDER_DELETE_INTELLIGENCEWHERE O.ID = ORD_ID);

 

 

非常慢，并行执行后生产库CPU持续达到了100%

 

【分析】

到生产库抓SQL，发现生产库实际执行的SQL是

1 SELECT /*+ INDEX ("O""IDX_OO_S_D") */"ID","ORDER_STATE","STATE_DATE","ORDER_CLASS"FROM "OM_ORDER" "O" WHERE ("ORDER_CLASS"='10S' OR"ORDER_CLASS"='1MP') AND ("ORDER_STATE"='10A' OR"ORDER_STATE"='10C' OR "ORDER_STATE"='10F' OR"ORDER_STATE"='1DF' OR "ORDER_STATE"='1RC') AND"STATE_DATE">=:1-31 AND "STATE_DATE"<:2

单条bufferget达到了11101

仔细看了一下，ROWNUM <= 5000的条件不见了

检查历史库上的执行计划

结合生产库捕获的实际执行SQL来看，生产库上没有按照5000行过滤，是按照其他条件筛选了数据，并且把中间结果集拉到了历史库执行关联，之后选了5000条。所以生产库上其实是把除了5000之外的条件的数据全部给选中了。

 

【解决】

改了历史库上的SQL

 1 select a.id,a.ORDER_CLASS from
 2 
 3 (SELECT /*+ index(o IDX_OO_S_D) driving_site(o)*/
 4 
 5      O.ID, O.ORDER_CLASS
 6 
 7       FROM OM_ORDER@BAK2HIS O
 8 
 9      WHERE O.ORDER_STATE IN ('10A', '10C', '10F', '1DF','1RC')
10 
11        AND O.ORDER_CLASS IN ('10S', '1MP')
12 
13        AND O.STATE_DATE < sysdate
14 
15        AND O.STATE_DATE >= sysdate-31
16 
17        AND ROWNUM <= 5000 ) a
18 
19        where  NOT EXISTS
20 
21      (SELECT 1 FROM HIS_ORDER_DELETE_INTELLIGENCE WHERE a.ID = ORD_ID)

改了两处，一个是把生产库选5000条的语句写到了一个子查询里面，一个是增加了driving_site(o)的hint。

 

driving_site(o)是在使用dblink关联表时指定SQL执行的具体库。

修改后的执行计划发生了变化

果然有了不同，虽然在历史库上执行这个SQL，但是历史库的表反而变成了remote

 

测试了一下，生产库捕获的SQL变成了

SELECT"A1"."ID","A1"."ORDER_CLASS" FROM  (SELECT /*+ INDEX ("A3""IDX_OO_S_D") */ "A3"."ID""ID","A3"."ORDER_CLASS" "ORDER_CLASS"FROM "OM_ORDER" "A3" WHERE("A3"."ORDER_STATE"='10A' OR"A3"."ORDER_STATE"='10C' OR"A3"."ORDER_STATE"='10F' OR"A3"."ORDER_STATE"='1DF' OR"A3"."ORDER_STATE"='1RC') AND("A3"."ORDER_CLASS"='10S' OR"A3"."ORDER_CLASS"='1MP') AND "A3"."STATE_DATE"<SYSDATE@!AND "A3"."STATE_DATE">=SYSDATE@!-31 AND ROWNUM<=5000)"A1" WHERE  NOT EXISTS (SELECT1 FROM "HIS_ORDER_DELETE_INTELLIGENCE"@! "A2" WHERE "A1"."ID"="A2"."ORD_ID")

确实把历史库的表拉到了生产库关联了

在看单条bufferget，下降到了203，只有之前的1/54

 

【总结】

1、dblink表关联，要把小数据的驱动表发至大表所在的库进行关联。

2、rownum的控制要严谨，实际效果要验证。

转载于:https://www.cnblogs.com/tonnytangy/p/7553054.html