A CRM database system has been running a batch of SQL for 24 hours, which has not been completed
The Problem SQL has been located. The table information and execution plan in SQL are as follows:
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') --open
and T9.ENDDATE > TO_DATE('20180525', 'YYYYMMDD') --close
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;
---The query table information is as follows---
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
----------Collect the statistical information of related tables again. After running for 3 hours, no results are found. The implementation plan is as follows-----------
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' )
According to the above execution plan, Cartesian product occurs for ID=8
When will Cartesian product occur
1) There is no direct association condition between tables
2) Expiration of table statistics results in optimizer estimate returning a result set of 1
The son node with ID=8 has two optimizers, i.e. 9 and 10 ID=9. The optimizer estimates that the result set returned by the full table scan of C ﹣ EB ﹣ TBL ﹣ fac is rows=1.
This step of SQL ID=9 is executed first. Since rows is counted as 1 by mistake, all the subsequent rows will change to 1 by mistake
In fact, this table returns 1114978 rows of C ﹣ s ﹣ EB ﹣ TBL ﹣ CIF table with 1614490 rows of ID=11,
The result of their Cartesian product is 1614490 * 1114978 = 1800120831220, which will produce 1.8 trillion row intermediate result sets. So it's normal not to run.
The Operation with ID=9 is REMOTE, so this is a dblink. Just now, developers can only collect statistics on their own computers, but they are helpless with REMOTE databases
So we only need a hint / * + CARDINALITY(T5 1000000) * / to let the optimizer think that the result set returned by T5 is 1000000 lines, OK!!!
The optimized execution plan is as follows:
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' )It can be seen that if ID=12 Rows is 1000k, the execution plan returns to normal, and Cartesian product disappears. SQL takes only 50 seconds to produce results