首页 > 代码库 > dblink导致执行计划出错,hint也无效
dblink导致执行计划出错,hint也无效
开发的同事发来一条语句,让我帮忙查看下ods和源端的结果是否一致。因为一下执行没出来,问开发人员,这个语句要跑2-3分钟。 因为他们是从本地用dblink连到ods的,我这里把dblink去掉直接从ods查看执行计划。 SELECT XSY_CODE,--发展销售员编码 SLY_CODE,--受理销售员编码 XSD_CODE,--销售点编码 DZS_CODE,--店中商编码 JYZT_CODE--销售员所属经营主体编码 FROM (select /*+parallel(a,10) parallel(b,10) parallel(c,10) parallel(d,10)*/a.subs_id,c.serv_id,b.action_id,d.action_type,a.stat,a.stat_date from crm_gz.tb_ba_subscription_hist a, crm_gz.tb_ba_action_hist b, crm_gz.tb_ba_serv_hist c, crm_gz.tb_pm_action d where a.subs_id=b.subs_id and a.subs_id=c.subs_id and b.action_id=d.action_id and d.city_id='200' and a.stat='S0C') a, (select * from crm_gz.tb_ba_channelstaff where modi_date>=to_date('20141123','yyyymmdd')) b WHERE a.subs_id=b.subs_id(+) and a.stat_date>=to_date('20141123','yyyymmdd') and ((a.action_type='NEW' and a.action_id not in(14030,14266,7021)) or a.action_id in(14099,14260,6448)) AND SERV_ID in ('3751990561', '3751991941', '3751992281', '3751992431', '3751992831', '3751994541', '3751994561', '3753633921', '3753633981', '3753634021', '3753634041', '3753634111', '3753634271', '3753622431', '3753644141', '3753645051', '3753645261', '3753647021', '3745498320', '3751978420', '3751978950'); --于是看下执行计划 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 232 | 296 (0)| 00:00:05 | | | |* 1 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_BA_SERV_HIST | 1 | 15 | 79 (0)| 00:00:02 | | | | 2 | NESTED LOOPS | | 2 | 232 | 296 (0)| 00:00:05 | | | | 3 | NESTED LOOPS OUTER | | 2 | 202 | 139 (0)| 00:00:02 | | | | 4 | NESTED LOOPS | | 2 | 98 | 136 (0)| 00:00:02 | | | | 5 | NESTED LOOPS | | 12 | 396 | 124 (0)| 00:00:02 | | | | 6 | PARTITION RANGE ALL | | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| ORDER_ITEM_HIST | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | |* 8 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 | | 79 (0)| 00:00:02 | 1 | 39 | | 9 | TABLE ACCESS BY INDEX ROWID | TB_BA_ACTION_HIST | 1 | 13 | 3 (0)| 00:00:01 | | | |* 10 | INDEX RANGE SCAN | IX_BA_ACT_SUBSID_HIST | 1 | | 2 (0)| 00:00:01 | | | |* 11 | TABLE ACCESS BY INDEX ROWID | TB_PM_ACTION | 1 | 16 | 1 (0)| 00:00:01 | | | |* 12 | INDEX UNIQUE SCAN | PK_PM_ACTION | 1 | | 0 (0)| 00:00:01 | | | |* 13 | TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF | 1 | 52 | 2 (0)| 00:00:01 | | | |* 14 | INDEX UNIQUE SCAN | PK_CHANNELSTAFF_SUBS_ID | 1 | | 1 (0)| 00:00:01 | | | | 15 | PARTITION RANGE ALL | | 1 | | 78 (0)| 00:00:02 | 1 | 39 | |* 16 | INDEX RANGE SCAN | IX_BA_SERVSUBSID_HIST | 1 | | 78 (0)| 00:00:02 | 1 | 39 | ----------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C"."SERV_ID"=3745498320 OR "C"."SERV_ID"=3751978420 OR "C"."SERV_ID"=3751978950 OR "C"."SERV_ID"=3751990561 OR "C"."SERV_ID"=3751991941 OR "C"."SERV_ID"=3751992281 OR "C"."SERV_ID"=3751992431 OR "C"."SERV_ID"=3751992831 OR "C"."SERV_ID"=3751994541 OR "C"."SERV_ID"=3751994561 OR "C"."SERV_ID"=3753622431 OR "C"."SERV_ID"=3753633921 OR "C"."SERV_ID"=3753633981 OR "C"."SERV_ID"=3753634021 OR "C"."SERV_ID"=3753634041 OR "C"."SERV_ID"=3753634111 OR "C"."SERV_ID"=3753634271 OR "C"."SERV_ID"=3753644141 OR "C"."SERV_ID"=3753645051 OR "C"."SERV_ID"=3753645261 OR "C"."SERV_ID"=3753647021) 8 - access("STAT"='S0C' AND "STATUS_DATE">=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 10 - access("ORDER_ITEM_ID"="B"."SUBS_ID") 11 - filter("D"."ACTION_TYPE"='NEW' AND "B"."ACTION_ID"<>14030 AND "B"."ACTION_ID"<>14266 AND "B"."ACTION_ID"<>7021 OR "B"."ACTION_ID"=6448 OR "B"."ACTION_ID"=14099 OR "B"."ACTION_ID"=14260) 12 - access("B"."ACTION_ID"="D"."ACTION_ID" AND "D"."CITY_ID"='200') 13 - filter("MODI_DATE"(+)>=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 14 - access("ORDER_ITEM_ID"="TB_BA_CHANNELSTAFF"."SUBS_ID"(+)) 16 - access("ORDER_ITEM_ID"="C"."SUBS_ID") --再查下相关的对象情况 ......getting segment size...... OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb) -------------------- ------------------------------ -------------------- ---------- CRM2_PUB PK_PM_ACTION INDEX 3 CRM_GZ PK_CHANNELSTAFF_SUBS_ID INDEX 1993 CRM_GZ IX_BA_ACT_SUBSID_HIST INDEX 8644.5 CRM_GZ IX_BA_SERVSUBSID_HIST INDEX PARTITION 3779.75 CRM_GZ IXH_ORDERITEM_STATDATE INDEX PARTITION 7927.5625 CRM2_PUB TB_PM_ACTION TABLE 34 CRM_GZ TB_BA_CHANNELSTAFF TABLE 9961 CRM_GZ TB_BA_ACTION_HIST TABLE 29305 CRM_GZ TB_BA_SERV_HIST TABLE PARTITION 30768.25 CRM_GZ ORDER_ITEM_HIST TABLE PARTITION 58503.625 10 rows selected. Elapsed: 00:00:00.34 ......getting table infomation...... OWNER TABLE_NAME Size(Mb) PAR DEGREE NUM_ROWS GLO STATS GATHER TIME -------------------- ------------------------------ ---------- --- ---------- ---------- --- ------------------ CRM2_PUB *TB_PM_ACTION 26.6463165 NO 1 120434 YES 78.6312153 CRM2_PUB TB_PM_ACTION 26.6463165 NO 1 120434 YES 78.6312153 CRM_GZ *TB_BA_CHANNELSTAFF 7909.30271 NO 1 94244375 YES 23.6510532 CRM_GZ TB_BA_CHANNELSTAFF 7909.30271 NO 1 94244375 YES 23.6510532 CRM_GZ *TB_BA_SERV_HIST 23083.6036 YES 1 129438036 YES 78.5309028 CRM_GZ TB_BA_SERV_HIST 23083.6036 YES 1 129438036 YES 78.5309028 CRM_GZ *ORDER_ITEM_HIST 43997.6631 YES 1 212603196 YES 78.6465972 CRM_GZ ORDER_ITEM_HIST 43997.6631 YES 1 212603196 YES 78.6465972 CRM_GZ *TB_BA_ACTION_HIST 22367.6905 NO 1 279216946 YES 78.6315972 CRM_GZ TB_BA_ACTION_HIST 22367.6905 NO 1 279216946 YES 78.6315972 10 rows selected. Elapsed: 00:00:00.12 ......getting index infomation...... OWNER INDEX_NAME TABLE_NAME PAR UNIQUENES DEGREE INDEX_TYPE LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR -------------------- ------------------------------ ------------------------------ --- --------- ---------- ---------- ----------- ---------- ----------------- ----- CRM_GZ IX_BA_ACT_SUBSID_HIST TB_BA_ACTION_HIST NO NONUNIQUE 1 NORMAL 492002 2 220159998 100 CRM_GZ IX_BA_SERVSUBSID_HIST TB_BA_SERV_HIST YES NONUNIQUE 1 NORMAL 196468 2 97894132 100 CRM_GZ IXH_ORDERITEM_STATDATE ORDER_ITEM_HIST YES NONUNIQUE 1 NORMAL 433702 2 153506102 17.3428035 CRM_GZ PK_CHANNELSTAFF_SUBS_ID TB_BA_CHANNELSTAFF NO UNIQUE 1 NORMAL 116755 2 83251565 100 CRM2_PUB PK_PM_ACTION TB_PM_ACTION NO UNIQUE 1 NORMAL 155 1 98209 100 5 rows selected. 其实,在olap中走3分钟是还能接受的,但是看了一下执行计划,觉得还能再进一步优化。很明显,这里的hint没有起作用 /*+parallel(a,10) parallel(b,10) parallel(c,10) parallel(d,10)*/,执行计划并不走并行。其实,走并行使错的。 因为下面3个表非常大,加起来有100G,走再多的并行也难在3分钟内扫描完。 OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb) -------------------- ------------------------------ -------------------- ---------- CRM_GZ TB_BA_ACTION_HIST TABLE 29305 CRM_GZ TB_BA_SERV_HIST TABLE PARTITION 30768.25 CRM_GZ ORDER_ITEM_HIST TABLE PARTITION 58503.625 |* 8 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 | 执行计划中第8歩有疑问,cbo认为只返回12行。直接手工算下,果然有问题。 select count(*) from CRM_GZ.ORDER_ITEM_HIST where "STAT"='S0C' AND "STATUS_DATE">=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss'); --220647 实际上返回220647行。那么问题来了,这里明显要走hash才是最优化的。这里有好几种方法都有效。 --方法1:/*+leading(c)*/ SELECT <span style="color:#ff0000;">/*+leading(c)*/</span>XSY_CODE,--发展销售员编码 SLY_CODE,--受理销售员编码 XSD_CODE,--销售点编码 DZS_CODE,--店中商编码 JYZT_CODE--销售员所属经营主体编码 FROM (select a.subs_id,c.serv_id,b.action_id,d.action_type,a.stat,a.stat_date from crm_gz.tb_ba_subscription_hist a, crm_gz.tb_ba_action_hist b, crm_gz.tb_ba_serv_hist c, crm_gz.tb_pm_action d where a.subs_id=b.subs_id and a.subs_id=c.subs_id and b.action_id=d.action_id and d.city_id='200' and a.stat='S0C') a, (select * from crm_gz.tb_ba_channelstaff where modi_date>=to_date('20141123','yyyymmdd')) b WHERE a.subs_id=b.subs_id(+) and a.stat_date>=to_date('20141123','yyyymmdd') and ((a.action_type='NEW' and a.action_id not in(14030,14266,7021)) or a.action_id in(14099,14260,6448)) AND SERV_ID in ('3751990561', '3751991941', '3751992281', '3751992431', '3751992831', '3751994541', '3751994561', '3753633921', '3753633981', '3753634021', '3753634041', '3753634111', '3753634271', '3753622431', '3753644141', '3753645051', '3753645261', '3753647021', '3745498320', '3751978420', '3751978950'); --执行计划: ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 232 | 325 (1)| 00:00:05 | | | | 1 | NESTED LOOPS OUTER | | 2 | 232 | 325 (1)| 00:00:05 | | | | 2 | NESTED LOOPS | | 2 | 128 | 322 (1)| 00:00:05 | | | | 3 | NESTED LOOPS | | 12 | 576 | 310 (1)| 00:00:05 | | | |* 4 | HASH JOIN | | 12 | 420 | 274 (1)| 00:00:04 | | | | 5 | PARTITION RANGE ALL | | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 | | 6 | INLIST ITERATOR | | | | | | | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_BA_SERV_HIST | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 | |* 8 | INDEX RANGE SCAN | IX_BA_SERVSERVID_HIST | 98 | | 100 (1)| 00:00:02 | 1 | 39 | | 9 | PARTITION RANGE ALL | | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | | 10 | TABLE ACCESS BY LOCAL INDEX ROWID | ORDER_ITEM_HIST | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | |* 11 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 | | 79 (0)| 00:00:02 | 1 | 39 | | 12 | TABLE ACCESS BY INDEX ROWID | TB_BA_ACTION_HIST | 1 | 13 | 3 (0)| 00:00:01 | | | |* 13 | INDEX RANGE SCAN | IX_BA_ACT_SUBSID_HIST | 1 | | 2 (0)| 00:00:01 | | | |* 14 | TABLE ACCESS BY INDEX ROWID | TB_PM_ACTION | 1 | 16 | 1 (0)| 00:00:01 | | | |* 15 | INDEX UNIQUE SCAN | PK_PM_ACTION | 1 | | 0 (0)| 00:00:01 | | | |* 16 | TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF | 1 | 52 | 2 (0)| 00:00:01 | | | |* 17 | INDEX UNIQUE SCAN | PK_CHANNELSTAFF_SUBS_ID | 1 | | 1 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------------- --方法2:/*+cardinality(a,220647)*/ SELECT XSY_CODE,--发展销售员编码 SLY_CODE,--受理销售员编码 XSD_CODE,--销售点编码 DZS_CODE,--店中商编码 JYZT_CODE--销售员所属经营主体编码 FROM (select <span style="color:#ff0000;">/*+cardinality(a,220647)*/</span>a.subs_id,c.serv_id,b.action_id,d.action_type,a.stat,a.stat_date from crm_gz.tb_ba_subscription_hist a, crm_gz.tb_ba_action_hist b, crm_gz.tb_ba_serv_hist c, crm_gz.tb_pm_action d where a.subs_id=b.subs_id and a.subs_id=c.subs_id and b.action_id=d.action_id and d.city_id='200' and a.stat='S0C') a, (select * from crm_gz.tb_ba_channelstaff where modi_date>=to_date('20141123','yyyymmdd')) b WHERE a.subs_id=b.subs_id(+) and a.stat_date>=to_date('20141123','yyyymmdd') and ((a.action_type='NEW' and a.action_id not in(14030,14266,7021)) or a.action_id in(14099,14260,6448)) AND SERV_ID in ('3751990561', '3751991941', '3751992281', '3751992431', '3751992831', '3751994541', '3751994561', '3753633921', '3753633981', '3753634021', '3753634041', '3753634111', '3753634271', '3753622431', '3753644141', '3753645051', '3753645261', '3753647021', '3745498320', '3751978420', '3751978950'); --执行计划: ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1624 | 679 (1)| 00:00:10 | | | |* 1 | HASH JOIN OUTER | | 14 | 1624 | 679 (1)| 00:00:10 | | | | 2 | NESTED LOOPS | | 14 | 896 | 667 (1)| 00:00:10 | | | | 3 | NESTED LOOPS | | 98 | 4704 | 569 (1)| 00:00:08 | | | |* 4 | HASH JOIN | | 98 | 3430 | 275 (2)| 00:00:04 | | | | 5 | PARTITION RANGE ALL | | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 | | 6 | INLIST ITERATOR | | | | | | | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_BA_SERV_HIST | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 | |* 8 | INDEX RANGE SCAN | IX_BA_SERVSERVID_HIST | 98 | | 100 (1)| 00:00:02 | 1 | 39 | | 9 | PARTITION RANGE ALL | | 220K| 4309K| 88 (0)| 00:00:02 | 1 | 39 | | 10 | TABLE ACCESS BY LOCAL INDEX ROWID | ORDER_ITEM_HIST | 220K| 4309K| 88 (0)| 00:00:02 | 1 | 39 | |* 11 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 | | 79 (0)| 00:00:02 | 1 | 39 | | 12 | TABLE ACCESS BY INDEX ROWID | TB_BA_ACTION_HIST | 1 | 13 | 3 (0)| 00:00:01 | | | |* 13 | INDEX RANGE SCAN | IX_BA_ACT_SUBSID_HIST | 1 | | 2 (0)| 00:00:01 | | | |* 14 | TABLE ACCESS BY INDEX ROWID | TB_PM_ACTION | 1 | 16 | 1 (0)| 00:00:01 | | | |* 15 | INDEX UNIQUE SCAN | PK_PM_ACTION | 1 | | 0 (0)| 00:00:01 | | | | 16 | TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF | 19 | 988 | 11 (0)| 00:00:01 | | | |* 17 | INDEX RANGE SCAN | IDX_BA_CHANNELSTAFF_MODI_DATE | 19 | | 3 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------------------- <span style="color:#ff0000;">于是,将方法告诉开发人员,结果本地执行还是很慢,查看执行计划,因为dblink的关系,用上面的2种方法都没生效。 那怎么办呢,用了各种hint都没法让他走hash,于是想到了查询块。 </span> --原来的执行计划和outline信息: PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 931544562 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | Inst | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE | | 2 | 232 | 296 (0)| 00:00:05 | | | | |* 1 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_BA_SERV_HIST | 1 | 15 | 79 (0)| 00:00:02 | | | GZODSD | | 2 | NESTED LOOPS | | 2 | 232 | 296 (0)| 00:00:05 | | | | | 3 | NESTED LOOPS OUTER | | 2 | 202 | 139 (0)| 00:00:02 | | | | | 4 | NESTED LOOPS | | 2 | 98 | 136 (0)| 00:00:02 | | | | | 5 | NESTED LOOPS | | 12 | 396 | 124 (0)| 00:00:02 | | | | | 6 | PARTITION RANGE ALL | | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| ORDER_ITEM_HIST | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | GZODSD | |* 8 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 | | 79 (0)| 00:00:02 | 1 | 39 | GZODSD | | 9 | TABLE ACCESS BY INDEX ROWID | TB_BA_ACTION_HIST | 1 | 13 | 3 (0)| 00:00:01 | | | GZODSD | |* 10 | INDEX RANGE SCAN | IX_BA_ACT_SUBSID_HIST | 1 | | 2 (0)| 00:00:01 | | | GZODSD | |* 11 | TABLE ACCESS BY INDEX ROWID | TB_PM_ACTION | 1 | 16 | 1 (0)| 00:00:01 | | | GZODSD | |* 12 | INDEX UNIQUE SCAN | PK_PM_ACTION | 1 | | 0 (0)| 00:00:01 | | | GZODSD | |* 13 | TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF | 1 | 52 | 2 (0)| 00:00:01 | | | GZODSD | |* 14 | INDEX UNIQUE SCAN | PK_CHANNELSTAFF_SUBS_ID | 1 | | 1 (0)| 00:00:01 | | | GZODSD | | 15 | PARTITION RANGE ALL | | 1 | | 78 (0)| 00:00:02 | 1 | 39 | | |* 16 | INDEX RANGE SCAN | IX_BA_SERVSUBSID_HIST | 1 | | 78 (0)| 00:00:02 | 1 | 39 | GZODSD | -------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$F5BB74E1 / A3@SEL$1 7 - SEL$F5BB74E1 / ORDER_ITEM_HIST@SEL$2 8 - SEL$F5BB74E1 / ORDER_ITEM_HIST@SEL$2 9 - SEL$F5BB74E1 / A4@SEL$1 10 - SEL$F5BB74E1 / A4@SEL$1 11 - SEL$F5BB74E1 / A2@SEL$1 12 - SEL$F5BB74E1 / A2@SEL$1 13 - SEL$F5BB74E1 / A1@SEL$1 14 - SEL$F5BB74E1 / A1@SEL$1 16 - SEL$F5BB74E1 / A3@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$F5BB74E1" "A3"@"SEL$1") USE_NL(@"SEL$F5BB74E1" "A1"@"SEL$1") USE_NL(@"SEL$F5BB74E1" "A2"@"SEL$1") USE_NL(@"SEL$F5BB74E1" "A4"@"SEL$1") LEADING(@"SEL$F5BB74E1" "ORDER_ITEM_HIST"@"SEL$2" "A4"@"SEL$1" "A2"@"SEL$1" "A1"@"SEL$1" "A3"@"SEL$1") INDEX(@"SEL$F5BB74E1" "A3"@"SEL$1" ("TB_BA_SERV_HIST"."SUBS_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "A1"@"SEL$1" ("TB_BA_CHANNELSTAFF"."SUBS_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "A2"@"SEL$1" ("TB_PM_ACTION"."ACTION_ID" "TB_PM_ACTION"."CITY_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "A4"@"SEL$1" ("TB_BA_ACTION_HIST"."SUBS_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "ORDER_ITEM_HIST"@"SEL$2" ("ORDER_ITEM_HIST"."STAT" "ORDER_ITEM_HIST"."STATUS_DATE")) OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") MERGE(@"SEL$2") OUTLINE_LEAF(@"SEL$F5BB74E1") ALL_ROWS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A3"."SERV_ID"=3745498320 OR "A3"."SERV_ID"=3751978420 OR "A3"."SERV_ID"=3751978950 OR "A3"."SERV_ID"=3751990561 OR "A3"."SERV_ID"=3751991941 OR "A3"."SERV_ID"=3751992281 OR "A3"."SERV_ID"=3751992431 OR "A3"."SERV_ID"=3751992831 OR "A3"."SERV_ID"=3751994541 OR "A3"."SERV_ID"=3751994561 OR "A3"."SERV_ID"=3753622431 OR "A3"."SERV_ID"=3753633921 OR "A3"."SERV_ID"=3753633981 OR "A3"."SERV_ID"=3753634021 OR "A3"."SERV_ID"=3753634041 OR "A3"."SERV_ID"=3753634111 OR "A3"."SERV_ID"=3753634271 OR "A3"."SERV_ID"=3753644141 OR "A3"."SERV_ID"=3753645051 OR "A3"."SERV_ID"=3753645261 OR "A3"."SERV_ID"=3753647021) 8 - access("STAT"='S0C' AND "STATUS_DATE">=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 10 - access("ORDER_ITEM_ID"="A4"."SUBS_ID") 11 - filter("A2"."ACTION_TYPE"='NEW' AND "A4"."ACTION_ID"<>14030 AND "A4"."ACTION_ID"<>14266 AND "A4"."ACTION_ID"<>7021 OR "A4"."ACTION_ID"=6448 OR "A4"."ACTION_ID"=14099 OR "A4"."ACTION_ID"=14260) 12 - access("A4"."ACTION_ID"="A2"."ACTION_ID" AND "A2"."CITY_ID"='200') 13 - filter("A1"."MODI_DATE"(+)>=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 14 - access("ORDER_ITEM_ID"="A1"."SUBS_ID"(+)) 16 - access("ORDER_ITEM_ID"="A3"."SUBS_ID") 认真观察了下qb name和outline,因为a表是视图,指向ORDER_ITEM_HIST。很显然a表的qb name就是ORDER_ITEM_HIST@SEL$2 c表是执行计划id=1的地方,对应的qb name是1 - SEL$F5BB74E1 / A3@SEL$1,因此可用下面的方法优化。 --优化后: SELECT <span style="color:#ff0000;">/*+leading(A3@SEL$1,ORDER_ITEM_HIST@SEL$2)*/</span> XSY_CODE,--发展销售员编码 SLY_CODE,--受理销售员编码 XSD_CODE,--销售点编码 DZS_CODE,--店中商编码 JYZT_CODE--销售员所属经营主体编码 FROM (select a.subs_id,c.serv_id,b.action_id,d.action_type,a.stat,a.stat_date from crm_gz.tb_ba_subscription_hist@togzodsd a, crm_gz.tb_ba_action_hist@togzodsd b, crm_gz.tb_ba_serv_hist@togzodsd c, crm_gz.tb_pm_action@togzodsd d where a.subs_id=b.subs_id and a.subs_id=c.subs_id and b.action_id=d.action_id and d.city_id='200' and a.stat='S0C') a, (select * from crm_gz.tb_ba_channelstaff@togzodsd where modi_date>=to_date('20141123','yyyymmdd')) b WHERE a.subs_id=b.subs_id(+) and a.stat_date>=to_date('20141123','yyyymmdd') and ((a.action_type='NEW' and a.action_id not in(14030,14266,7021)) or a.action_id in(14099,14260,6448)) AND SERV_ID in ('3751990561', '3751991941', '3751992281', '3751992431', '3751992831', '3751994541', '3751994561', '3753633921', '3753633981', '3753634021', '3753634041', '3753634111', '3753634271', '3753622431', '3753644141', '3753645051', '3753645261', '3753647021', '3745498320', '3751978420', '3751978950'); --执行计划和outline -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | Inst | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE | | 2 | 232 | 325 (1)| 00:00:05 | | | | | 1 | NESTED LOOPS OUTER | | 2 | 232 | 325 (1)| 00:00:05 | | | | | 2 | NESTED LOOPS | | 2 | 128 | 322 (1)| 00:00:05 | | | | | 3 | NESTED LOOPS | | 12 | 576 | 310 (1)| 00:00:05 | | | | |* 4 | HASH JOIN | | 12 | 420 | 274 (1)| 00:00:04 | | | | | 5 | PARTITION RANGE ALL | | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 | | | 6 | INLIST ITERATOR | | | | | | | | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_BA_SERV_HIST | 98 | 1470 | 185 (1)| 00:00:03 | 1 | 39 | GZODSD | |* 8 | INDEX RANGE SCAN | IX_BA_SERVSERVID_HIST | 98 | | 100 (1)| 00:00:02 | 1 | 39 | GZODSD | | 9 | PARTITION RANGE ALL | | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | | | 10 | TABLE ACCESS BY LOCAL INDEX ROWID | ORDER_ITEM_HIST | 12 | 240 | 88 (0)| 00:00:02 | 1 | 39 | GZODSD | |* 11 | INDEX RANGE SCAN | IXH_ORDERITEM_STATDATE | 12 | | 79 (0)| 00:00:02 | 1 | 39 | GZODSD | | 12 | TABLE ACCESS BY INDEX ROWID | TB_BA_ACTION_HIST | 1 | 13 | 3 (0)| 00:00:01 | | | GZODSD | |* 13 | INDEX RANGE SCAN | IX_BA_ACT_SUBSID_HIST | 1 | | 2 (0)| 00:00:01 | | | GZODSD | |* 14 | TABLE ACCESS BY INDEX ROWID | TB_PM_ACTION | 1 | 16 | 1 (0)| 00:00:01 | | | GZODSD | |* 15 | INDEX UNIQUE SCAN | PK_PM_ACTION | 1 | | 0 (0)| 00:00:01 | | | GZODSD | |* 16 | TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF | 1 | 52 | 2 (0)| 00:00:01 | | | GZODSD | |* 17 | INDEX UNIQUE SCAN | PK_CHANNELSTAFF_SUBS_ID | 1 | | 1 (0)| 00:00:01 | | | GZODSD | -------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$F5BB74E1 7 - SEL$F5BB74E1 / A3@SEL$1 8 - SEL$F5BB74E1 / A3@SEL$1 10 - SEL$F5BB74E1 / ORDER_ITEM_HIST@SEL$2 11 - SEL$F5BB74E1 / ORDER_ITEM_HIST@SEL$2 12 - SEL$F5BB74E1 / A4@SEL$1 13 - SEL$F5BB74E1 / A4@SEL$1 14 - SEL$F5BB74E1 / A2@SEL$1 15 - SEL$F5BB74E1 / A2@SEL$1 16 - SEL$F5BB74E1 / A1@SEL$1 17 - SEL$F5BB74E1 / A1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$F5BB74E1" "A1"@"SEL$1") USE_NL(@"SEL$F5BB74E1" "A2"@"SEL$1") USE_NL(@"SEL$F5BB74E1" "A4"@"SEL$1") USE_HASH(@"SEL$F5BB74E1" "ORDER_ITEM_HIST"@"SEL$2") LEADING(@"SEL$F5BB74E1" "A3"@"SEL$1" "ORDER_ITEM_HIST"@"SEL$2" "A4"@"SEL$1" "A2"@"SEL$1" "A1"@"SEL$1") INDEX_RS_ASC(@"SEL$F5BB74E1" "A1"@"SEL$1" ("TB_BA_CHANNELSTAFF"."SUBS_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "A2"@"SEL$1" ("TB_PM_ACTION"."ACTION_ID" "TB_PM_ACTION"."CITY_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "A4"@"SEL$1" ("TB_BA_ACTION_HIST"."SUBS_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "ORDER_ITEM_HIST"@"SEL$2" ("ORDER_ITEM_HIST"."STAT" "ORDER_ITEM_HIST"."STATUS_DATE")) INDEX_RS_ASC(@"SEL$F5BB74E1" "A3"@"SEL$1" ("TB_BA_SERV_HIST"."SERV_ID")) OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") MERGE(@"SEL$2") OUTLINE_LEAF(@"SEL$F5BB74E1") ALL_ROWS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("ORDER_ITEM_ID"="A3"."SUBS_ID") 8 - access("A3"."SERV_ID"=3745498320 OR "A3"."SERV_ID"=3751978420 OR "A3"."SERV_ID"=3751978950 OR "A3"."SERV_ID"=3751990561 OR "A3"."SERV_ID"=3751991941 OR "A3"."SERV_ID"=3751992281 OR "A3"."SERV_ID"=3751992431 OR "A3"."SERV_ID"=3751992831 OR "A3"."SERV_ID"=3751994541 OR "A3"."SERV_ID"=3751994561 OR "A3"."SERV_ID"=3753622431 OR "A3"."SERV_ID"=3753633921 OR "A3"."SERV_ID"=3753633981 OR "A3"."SERV_ID"=3753634021 OR "A3"."SERV_ID"=3753634041 OR "A3"."SERV_ID"=3753634111 OR "A3"."SERV_ID"=3753634271 OR "A3"."SERV_ID"=3753644141 OR "A3"."SERV_ID"=3753645051 OR "A3"."SERV_ID"=3753645261 OR "A3"."SERV_ID"=3753647021) 11 - access("STAT"='S0C' AND "STATUS_DATE">=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 13 - access("ORDER_ITEM_ID"="A4"."SUBS_ID") 14 - filter("A2"."ACTION_TYPE"='NEW' AND "A4"."ACTION_ID"<>14030 AND "A4"."ACTION_ID"<>14266 AND "A4"."ACTION_ID"<>7021 OR "A4"."ACTION_ID"=6448 OR "A4"."ACTION_ID"=14099 OR "A4"."ACTION_ID"=14260) 15 - access("A4"."ACTION_ID"="A2"."ACTION_ID" AND "A2"."CITY_ID"='200') 16 - filter("A1"."MODI_DATE"(+)>=TO_DATE(' 2014-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 17 - access("ORDER_ITEM_ID"="A1"."SUBS_ID"(+)) Note ----- - fully remote statement 优化前3分钟多才出结果,优化后27秒出结果了。至此,已经没有必要优化了。
dblink导致执行计划出错,hint也无效
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。