dblink远端数据库统计信息过期问题的处理方法
某CRM数据库系统跑批一条SQL执行了24小时 还没执行完
问题SQL已经定位到，SQL中表信息以及执行计划如下：
SELECT t.CRM_DT,
......
......
SUM(nvl(t.OUTSTD_AMT, 0) * T9.HL) AS amt,
T7.CB_HOST_NO as CB_CUST_ID
from A_O_DEP_ACCT_FIN_EVTE_DET_temp t
inner JOIN C_s_EB_TBL_FAC@db_link_ccrm T5------------
ON t.FAC_ID = T5.ID
inner JOIN c_s_eb_tbl_cdt_file_cif@db_link_ccrm T6
ON T5.CDT_FILE_ID = T6.CDT_FILE_ID
inner JOIN C_S_EB_TBL_CIF@db_link_ccrm T7------------
ON T7.ID = T6.CIF_ID
AND T7.MT_CIF_TYP_CD = 'CRM_MT_CIF_TYP_CD_14'
LEFT JOIN C_s_CB_BBHL@db_link_ccrm T9
on T9.yb = T.MT_CUR_CD
and T9.STARTDATE <= TO_DATE('20180525', 'YYYYMMDD') --开
and T9.ENDDATE > TO_DATE('20180525', 'YYYYMMDD') --闭
LEFT JOIN C_S_EB_TBL_MT_BR@DB_LINK_CCRM T10
ON T.MT_BR_CD = T10.cd
WHERE T6.MT_FAC_REL_CD = 'CRM_MT_FAC_REL_CD_04'
and T7.MT_CIF_TYP_CD = 'CRM_MT_CIF_TYP_CD_14'
GROUP BY t.crm_dt,t.open_branch_no, t7.no,t7.CB_HOST_NO,T10.IS_SME,T7.NM;
---查询表信息如下---
select count(1) from A_O_DEP_ACCT_FIN_EVTE_DET_TEMP t;--709930
select count(1) from C_s_EB_TBL_FAC@db_link_ccrm t;--1614490
select count(1) from c_s_eb_tbl_cdt_file_cif@db_link_ccrm;--1114978
select count(1) from C_S_EB_TBL_CIF@db_link_ccrm;--609038
select count(1) from C_s_CB_BBHL@db_link_ccrm;--1459
select count(1) from C_S_EB_TBL_MT_BR@DB_LINK_CCRM;--3313
----------重新收集相关表的统计信息，跑了3个小时还是没出结果，执行计划如下-----------
Plan hash value: 3812144039
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2183 | 8375 (2)| 00:01:41 | | |
| 1 | HASH GROUP BY | | 1 | 2183 | 8375 (2)| 00:01:41 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 2183 | 8374 (2)| 00:01:41 | | |
| 3 | VIEW | | 1 | 2095 | 8373 (2)| 00:01:41 | | |
|* 4 | HASH JOIN OUTER | | 1 | 2246 | 8373 (2)| 00:01:41 | | |
| 5 | VIEW | | 1 | 2208 | 8369 (2)| 00:01:41 | | |
|* 6 | HASH JOIN | | 1 | 807 | 8369 (2)| 00:01:41 | | |
| 7 | NESTED LOOPS | | 1 | 746 | 6611 (2)| 00:01:20 | | |
| 8 | MERGE JOIN CARTESIAN| | 1 | 591 | 6610 (2)| 00:01:20 | | |
| 9 | REMOTE | C_S_EB_TBL_FAC | 1 | 204 | 2 (0)| 00:00:01 | DB_LI~ | R->S |
| 10 | BUFFER SORT | | 304K| 112M| 6608 (2)| 00:01:20 | | |
| 11 | REMOTE | C_S_EB_TBL_CIF | 304K| 112M| 6608 (2)| 00:01:20 | DB_LI~ | R->S |
| 12 | REMOTE | C_S_EB_TBL_CDT_FILE_CIF | 1 | 155 | 1 (0)| 00:00:01 | DB_LI~ | R->S |
| 13 | TABLE ACCESS FULL | A_O_DEP_ACCT_FIN_EVTE_DET_TEMP | 709K| 41M| 1753 (1)| 00:00:22 | | |
| 14 | REMOTE | C_S_CB_BBHL | 11 | 418 | 4 (0)| 00:00:01 | DB_LI~ | R->S |
| 15 | REMOTE | C_S_EB_TBL_MT_BR | 1 | 88 | 1 (0)| 00:00:01 | DB_LI~ | R->S |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T9"."YB"(+)="T"."MT_CUR_CD")
6 - access("T"."FAC_ID"="T5"."ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
9 - SELECT "ID","CDT_FILE_ID" FROM "C_S_EB_TBL_FAC" "T5" (accessing 'DB_LINK_CCRM' )
11 - SELECT "ID","NO","MT_CIF_TYP_CD","NM","CB_HOST_NO" FROM "C_S_EB_TBL_CIF" "T7" WHERE
"MT_CIF_TYP_CD"='CRM_MT_CIF_TYP_CD_14' (accessing 'DB_LINK_CCRM' )
12 - SELECT "CDT_FILE_ID","CIF_ID","MT_FAC_REL_CD" FROM "C_S_EB_TBL_CDT_FILE_CIF" "T6" WHERE
"MT_FAC_REL_CD"='CRM_MT_FAC_REL_CD_04' AND :1="CIF_ID" AND :2="CDT_FILE_ID" (accessing 'DB_LINK_CCRM' )
14 - SELECT "STARTDATE","ENDDATE","YB","HL" FROM "C_S_CB_BBHL" "T9" WHERE "ENDDATE">TO_DATE(' 2018-05-25 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "STARTDATE"<=TO_DATE(' 2018-05-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing
'DB_LINK_CCRM' )
15 - SELECT "CD","IS_SME" FROM "C_S_EB_TBL_MT_BR" "T10" WHERE :1="CD" (accessing 'DB_LINK_CCRM' )
由上面执行计划可知ID=8发生了笛卡尔积
什么情况下会发生笛卡尔积
1)表与表之间没有直接关联条件
2)表统计信息过期导致优化器估算返回的结果集为1
ID=8的儿子节点有两个分别是9和10 ID=9优化器估算C_S_EB_TBL_FAC全表扫描返回的结果集是rows=1行。
这个SQL ID=9这一步最先执行，由于rows算错为1，导致后面的rows全部都跟着错变为1
其实这个表返回1614490行 ID=11的C_S_EB_TBL_CIF表返回1114978行，
他们笛卡尔积的结果是1614490*1114978=1800120831220，会产生1.8万亿行中间结果集。所以跑不出结果很正常。
ID=9的Operation是REMOTE，所以这是一个dblink。刚刚开发人员收集统计信息只能收集本机，对远端数据库却束手无策
所以这里只需要一个hint /*+CARDINALITY(T5 1000000) */ 让优化器认为T5返回的结果集是1000000行，搞定！！！
优化后的执行计划如下：
Plan hash value: 1122908073
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 965K| 2010M| | 503K (1)| 01:40:45 | | |
| 1 | HASH GROUP BY | | 965K| 2010M| 2514M| 503K (1)| 01:40:45 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 965K| 2010M| | 63645 (1)| 00:12:44 | | |
| 3 | REMOTE | C_S_EB_TBL_MT_BR | 3313 | 284K| | 23 (0)| 00:00:01 | DB_LI~ | R->S |
| 4 | VIEW | | 965K| 1929M| | 63616 (1)| 00:12:44 | | |
|* 5 | HASH JOIN RIGHT OUTER| | 965K| 2068M| | 63616 (1)| 00:12:44 | | |
| 6 | REMOTE | C_S_CB_BBHL | 11 | 418 | | 4 (0)| 00:00:01 | DB_LI~ | R->S |
| 7 | VIEW | | 886K| 1867M| | 63606 (1)| 00:12:44 | | |
|* 8 | HASH JOIN | | 886K| 682M| 49M| 63606 (1)| 00:12:44 | | |
| 9 | TABLE ACCESS FULL | A_O_DEP_ACCT_FIN_EVTE_DET_TEMP | 709K| 41M| | 1753 (1)| 00:00:22 | | |
|* 10 | HASH JOIN | | 559K| 398M| 161M| 39328 (1)| 00:07:52 | | |
| 11 | REMOTE | | 304K| 112M| | 6608 (2)| 00:01:20 | DB_LI~ | R->S |
| 12 | REMOTE | C_S_EB_TBL_FAC | 1000K| 194M| | 2 (0)| 00:00:01 | DB_LI~ | R->S |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."MT_BR_CD"="T10"."CD"(+))
5 - access("T9"."YB"(+)="T"."MT_CUR_CD")
8 - access("T"."FAC_ID"="T5"."ID")
10 - access("T5"."CDT_FILE_ID"="T6"."CDT_FILE_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT /*+ */ "CD","IS_SME" FROM "C_S_EB_TBL_MT_BR" "T10" (accessing 'DB_LINK_CCRM' )
6 - SELECT /*+ */ "STARTDATE","ENDDATE","YB","HL" FROM "C_S_CB_BBHL" "T9" WHERE "ENDDATE">TO_DATE(' 2018-05-25
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "STARTDATE"<=TO_DATE(' 2018-05-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing
'DB_LINK_CCRM' )
11 - SELECT "A1"."ID","A1"."NO","A1"."MT_CIF_TYP_CD","A1"."NM","A1"."CB_HOST_NO","A2"."CDT_FILE_ID","A2"."CIF_ID","A2"."M
T_FAC_REL_CD" FROM "C_S_EB_TBL_CIF" "A1","C_S_EB_TBL_CDT_FILE_CIF" "A2" WHERE "A2"."MT_FAC_REL_CD"='CRM_MT_FAC_REL_CD_04'
AND "A1"."ID"="A2"."CIF_ID" AND "A1"."MT_CIF_TYP_CD"='CRM_MT_CIF_TYP_CD_14' (accessing 'DB_LINK_CCRM' )
12 - SELECT /*+ OPT_ESTIMATE (TABLE "T5" ROWS=1000000.000000 ) */ "ID","CDT_FILE_ID" FROM "C_S_EB_TBL_FAC" "T5"
(accessing 'DB_LINK_CCRM' )
可以看出ID=12Rows为1000k 执行计划恢复正常，笛卡尔积消失。SQL用了仅仅50秒就出结果
相关文章
图文推荐
- 文章
- 推荐
- 热门新闻