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的控制要严谨,实际效果要验证。