问题描述
你好，团队，
我有一个特定的sql查询，平均每天运行约40分钟。今天，它自4:50 BST以来一直在运行，根本没有完成。
我分析了今天出了什么问题，并详细说明了我可以做的事情:
现在，按照以前的计划，它应该是一个哈希连接，一个并行化的查询，而不是上面的序列化查询，并且在任何地方都没有扫描索引。
同一天对sql id进行了如此多的更改似乎很奇怪。
我已经检查了统计信息，并在发现它们过时的地方进行了刷新，检查了sql代码中是否有更改-根本没有更改。
我知道此查询可能需要更多详细信息，但会要求您接受此情况，然后我可以根据需要提供所有相关详细信息。
非常感谢
罗希特
我有一个特定的sql查询，平均每天运行约40分钟。今天，它自4:50 BST以来一直在运行，根本没有完成。
我分析了今天出了什么问题，并详细说明了我可以做的事情:
SQL Query :
--------------
INSERT INTO ald_extract_temp_GEA_R_CMP_2 (record_value)
SELECT FAULT_ID
|| '¦'
|| TO_CHAR (REC_EFF_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| CUPID
|| '¦'
|| CUST_ACC_NAME
|| '¦'
|| TO_CHAR (FAULT_RCVD_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| CARELEVEL_CODE
|| '¦'
|| TRIM (TO_CHAR (SLA_CLOCK, '9999999999999999999999'))
|| '¦'
|| TO_CHAR (KCI3_CLEAR_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| TO_CHAR (FIRST_KCI3_CLEAR_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| BILLING_ACC_NUM
|| '¦'
|| DUNS_NUM
|| '¦'
|| CIRCUIT_ID
|| '¦'
|| MDF_SITE_CODE
|| '¦'
|| DISTRICT_CODE
|| '¦'
|| EXCH_GRP_CODE
|| '¦'
|| PROD_CODE
|| '¦'
|| TEST_PRODUCT
|| '¦'
|| FAULT_SRC
|| '¦'
|| FAULT_STATUS
|| '¦'
|| FAULT_SUB_STATUS
|| '¦'
|| FAULT_DESC
|| '¦'
|| TO_CHAR (COMPLTD_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| CANCLTN_RSN_TXT
|| '¦'
|| USER_ID
|| '¦'
|| BUYERS_ID
|| '¦'
|| DIRECTORY_NUM
|| '¦'
|| TEST_REF
|| '¦'
|| CLEAR_CODE
|| '¦'
|| CANCLTN_FLAG
|| '¦'
|| RJCTD_FLAG
|| '¦'
|| CSS_FAULT_ID
|| '¦'
|| TO_CHAR (KCI1_ACCPT_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| TO_CHAR (REJECT_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| TO_CHAR (CANCEL_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| KCI_CODE
|| '¦'
|| TO_CHAR (KCI_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| APNTMT_REF
|| '¦'
|| TO_CHAR (APNTMT_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| TO_CHAR (EARLIEST_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| APNTMT_SLOT
|| '¦'
|| EARLIEST_SLOT
|| '¦'
|| TO_CHAR (EARLIEST_AVAILABLE_APPOINTMENT,
'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| TO_CHAR (CLOSE_DATE, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| CLEAR_CODE_ADD_IND
|| '¦'
|| ENGINEER_ID
|| '¦'
|| TEMP_CLEAR_FLAG
|| '¦'
|| TO_CHAR (PARKED_TIME, '99999999.99')
|| '¦'
|| ASSET_CARE_LEVEL
|| '¦'
|| TO_CHAR (LATEST_ACCESS_TIME, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| /* ver1.0 TO_CHAR HAS BEEN ADDED */
TO_CHAR (EARLIEST_NEXT_ACCESS_TIME, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| ACCESS_FLAG_24_HR
|| '¦'
|| FOLLOW_ON_FLT_FLAG
|| '¦'
|| EXPIDITE_FAULT_FLAG
|| '¦'
|| TO_CHAR (ORIGINAL_COMMIT_TIME, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| CASE
WHEN (KCI3_CLEAR_DATE IS NULL)
AND (KCI_CODE NOT IN ('3302', '3300')) /* 28/01/2011 Changes in RCT calculation. */
THEN
NULL
ELSE
TO_CHAR (NVL (FCT, ORIGINAL_COMMIT_TIME),
'DD-MM-YYYY HH24:MI:SS')
END
|| '¦'
|| UPSTRM_BANDWIDTH
|| '¦'
|| DOWNSTRM_BANDWIDTH
|| '¦'
|| MBORC_REF
|| '¦'
|| TO_CHAR (MBORC_START_TIME, 'DD-MM-YYYY HH24:MI:SS')
|| '¦'
|| MBORC_CLEAR_TIME
|| '¦'
|| TO_CHAR (CARE_SERVICE_START_DATE, 'DD-MON-YYYY')
|| '¦'
|| CASE
WHEN CARE_SERVICE_START_DATE IS NOT NULL
THEN
TO_CHAR (CARE_SERVICE_START_DATE + 365, 'DD-MON-YYYY')
ELSE
NULL
END
|| '¦'
|| FAULT_TYPE
FROM (SELECT DISTINCT
FLT1.FAULT_ID,
ACT.START_DATE_TIME REC_EFF_DATE,
FLT1.CUPID,
C.CUST_ACC_NAME,
FLT1.FAULT_RCVD_DATE,
FLT1.CARELEVEL_CODE,
FLT1.SLA_CLOCK,
FLT1.KCI3_CLEAR_DATE,
FLT1.FIRST_KCI3_CLEAR_DATE,
FLT1.BILLING_ACC_NUM,
FLT1.DUNS_NUM,
FLT1.CIRCUIT_ID,
FLT1.MDF_SITE_CODE,
FLT1.PROD_CODE,
FLT1.TEST_PRODUCT,
FLT1.FAULT_SRC,
FLT1.FAULT_STATUS,
FLT1.FAULT_SUB_STATUS,
FLT1.FAULT_DESC,
FLT1.COMPLTD_DATE,
FLT1.CANCLTN_RSN_TXT,
FLT1.USER_ID,
FLT1.BUYERS_ID,
FLT1.DIRECTORY_NUM,
FLT1.TEST_REF,
FLT1.CLEAR_CODE,
CASE
WHEN FLT1.CANCEL_DATE IS NOT NULL THEN 'Y'
ELSE 'N'
END
CANCLTN_FLAG,
CASE
WHEN FLT1.REJECT_DATE IS NOT NULL THEN 'Y'
ELSE 'N'
END
RJCTD_FLAG,
FLT1.CSS_FAULT_ID,
FLT1.KCI1_ACCPT_DATE,
FLT1.REJECT_DATE,
FLT1.CANCEL_DATE,
FLT1.KCI_CODE,
FLT1.KCI_DATE,
FLT1.APNTMT_REF,
FLT1.APNTMT_DATE,
FLT1.EARLIEST_DATE,
FLT1.APNTMT_SLOT,
FLT1.EARLIEST_SLOT,
FLT1.EARLIEST_AVAILABLE_APPOINTMENT,
FLT1.KCI4_CLOSE_DATE CLOSE_DATE,
FLT1.CLEAR_CODE_ADD_IND,
FLT1.ENGINEER_ID,
CASE
WHEN UPPER (CLEAR_TYPE_FLAG) = 'TEMPORARY CLEARED'
THEN
'Y'
ELSE
'N'
END
TEMP_CLEAR_FLAG,
CASE
WHEN UPPER (FLT1.MDF_SITE_CODE) <> 'NO_DATA'
THEN
SUBSTR (FLT1.MDF_SITE_CODE, 3)
ELSE
NULL
END
EXCH_GRP_CODE,
CASE
WHEN UPPER (FLT1.MDF_SITE_CODE) <> 'NO_DATA'
THEN
SUBSTR (FLT1.MDF_SITE_CODE, 1, 2)
ELSE
NULL
END
DISTRICT_CODE,
FLT1.PARKED_TIME,
FLT1.TIME_TO_CLEAR,
FLT1.PROD_NAME,
FLT1.ASSET_CARE_LEVEL,
FLT1.LATEST_ACCESS_TIME,
FLT1.EARLIEST_NEXT_ACCESS_TIME,
FLT1.ACCESS_FLAG_24_HR,
FLT1.FOLLOW_ON_FLT_FLAG,
FLT1.EXPIDITE_FAULT_FLAG,
CASE
WHEN FLT1.FAULT_RCVD_DATE <
(SELECT TO_DATE (code_desc, 'dd-mm-yyyy')
FROM compensation_ref
WHERE code_type = '006' AND code_value = '18')
THEN
FLT1.ORIGINAL_COMMIT_TIME
ELSE
CASE
WHEN O.ASSET_CARE_LEVEL IN ('1', '2', '2.5')
THEN
TRUNC (
FN_FAULT_TRGT_DATE_FLT_BH (
O.ASSET_CARE_LEVEL,
O.FAULT_RCVD_DATE,
NULL))
+ 1
- 1 / 86400
WHEN O.ASSET_CARE_LEVEL = '3'
THEN
CASE
WHEN O.FAULT_RCVD_DATE >
( TRUNC (O.FAULT_RCVD_DATE)
+ (1 / 2)
+ 1 / 24
- 1 / 86400)
THEN
( (TRUNC (O.FAULT_RCVD_DATE) + (1 / 2))
+ 1
+ (1 / 24 - 1 / 86400))
ELSE
(TRUNC (O.FAULT_RCVD_DATE) + 1)
- (1 / 86400)
END
WHEN O.ASSET_CARE_LEVEL = '4'
THEN
CASE
WHEN PKG_SH_RCT_CALC.FN_RET_COMMIT_DATE (
O.FAULT_ID,
'010') <>
TO_DATE ('01-JAN-1753',
'dd-mon-yyyy')
THEN
PKG_SH_RCT_CALC.FN_RET_COMMIT_DATE (
O.FAULT_ID,
'010')
WHEN ( (O.LATEST_ACCESS_TIME >=
O.FAULT_RCVD_DATE)
AND ( ( O.LATEST_ACCESS_TIME
- O.FAULT_RCVD_DATE) > (1 / 4)))
THEN --- 28/01/2011 Changes in OCT calculation.
O.FAULT_RCVD_DATE + (1 / 4)
WHEN ( (O.LATEST_ACCESS_TIME >=
O.FAULT_RCVD_DATE)
AND (O.EARLIEST_NEXT_ACCESS_TIME >
O.FAULT_RCVD_DATE)
AND ( ( O.LATEST_ACCESS_TIME
- O.FAULT_RCVD_DATE) < (1 / 4)))
THEN --- 28/01/2011 Changes in OCT calculation.
O.EARLIEST_NEXT_ACCESS_TIME + (1 / 4)
WHEN O.ACCESS_FLAG_24_HR = 'Y'
THEN
O.FAULT_RCVD_DATE + (1 / 4)
ELSE
O.FAULT_RCVD_DATE + (1 / 4)
END
END
END
ORIGINAL_COMMIT_TIME,
RCT.REVISED_COMMITMENT_DATE FCT,
ASSET.UPSTRM_BANDWIDTH,
ASSET.DOWNSTRM_BANDWIDTH,
FLT1.MBORC_REF,
PORTL.START_TIME MBORC_START_TIME,
FLT1.MBORC_CLEAR_TIME,
ASSET.CARE_SERVICE_START_DATE,
FLT1.FAULT_TYPE
FROM GEA_FAULT_DETAIL_FACT FLT1
INNER JOIN ALD_CONTROL_TABLE ACT
ON FLT1.REC_EFF_DATE = ACT.START_DATE_TIME
AND FLT1.PROD_CODE IN (SELECT PROD_CODE
FROM NGA_PROD_CODE
WHERE GEA_R_ALL = 'Y') /*ORC2M-19559*/
AND FLT1.CLOSE_FLAG = 'Y'
INNER JOIN os_gea_faults O
ON FLT1.FAULT_ID = O.FAULT_ID
AND ( UPPER (O.fault_status) = 'CLOSED'
OR UPPER (O.fault_status) = 'REJECTED')
LEFT OUTER JOIN CUPID C
ON FLT1.CUPID = C.CUPID
AND C.REC_END_DATE =
TO_DATE ('31/12/9999 23:59:59',
'DD/MM/YYYY HH24:MI:SS')
LEFT OUTER JOIN
(SELECT *
FROM GEA_ASSET
WHERE REC_END_DATE =
TO_DATE ('31/12/9999 23:59:59',
'DD/MM/YYYY HH24:MI:SS')) ASSET
ON FLT1.CIRCUIT_ID = ASSET.CIRCUIT_ID
LEFT OUTER JOIN
(SELECT FAULT_ID, REVISED_COMMITMENT_DATE, INITIAL_MFL
FROM (SELECT FAULT_ID,
REVISED_COMMITMENT_DATE,
INITIAL_MFL,
ROW_NUMBER ()
OVER (PARTITION BY FAULT_ID
ORDER BY SRC_ACTVTY_DATE DESC)
ROW_IND
FROM WORK_ARRANGE_GEA_FAULTS)
WHERE ROW_IND = 1) RCT
ON RCT.FAULT_ID = FLT1.FAULT_ID
LEFT OUTER JOIN PORTL_MBORC_DTLS PORTL
ON FLT1.MBORC_REF = PORTL.MBORC_ID)
Now the plans for today starkly differ from what it was yesterday :
Todays' sub-optimal plan :
-----------------------------
Plan hash value: 3268238723
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2256 | 63756 (1)| 00:00:05 | | |
| 1 | LOAD TABLE CONVENTIONAL | ALD_EXTRACT_TEMP_GEA_R_CMP_2 | | | | | | |
|* 2 | TABLE ACCESS FULL | COMPENSATION_REF | 1 | 69 | 7 (0)| 00:00:01 | | |
| 3 | VIEW | | 1 | 2256 | 63756 (1)| 00:00:05 | | |
| 4 | HASH UNIQUE | | 1 | 1520 | 63756 (1)| 00:00:05 | | |
|* 5 | HASH JOIN SEMI | | 1 | 1520 | 63755 (1)| 00:00:05 | | |
| 6 | NESTED LOOPS OUTER | | 1 | 1501 | 63751 (1)| 00:00:05 | | |
|* 7 | HASH JOIN OUTER | | 1 | 1484 | 63751 (1)| 00:00:05 | | |
| 8 | NESTED LOOPS OUTER | | 1 | 1428 | 63737 (1)| 00:00:05 | | |
|* 9 | HASH JOIN | | 1 | 1358 | 63737 (1)| 00:00:05 | | |
| 10 | NESTED LOOPS OUTER | | 1 | 1349 | 63734 (1)| 00:00:05 | | |
| 11 | NESTED LOOPS | | 1 | 1064 | 63477 (1)| 00:00:05 | | |
| 12 | PARTITION RANGE ALL | | 1 | 38 | 62454 (1)| 00:00:05 | 1 | 127 |
|* 13 | TABLE ACCESS FULL | OS_GEA_FAULTS | 1 | 38 | 62454 (1)| 00:00:05 | 1 | 127 |
| 14 | PARTITION RANGE ALL | | 2 | 2052 | 1023 (0)| 00:00:01 | 1 | 127 |
| 15 | PARTITION LIST ALL | | 2 | 2052 | 1023 (0)| 00:00:01 | 1 | 4 |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID| GEA_FAULT_DETAIL_FACT | 2 | 2052 | 1023 (0)| 00:00:01 | 1 | 508 |
|* 17 | INDEX RANGE SCAN | GEA_FAULT_DETAIL_FACT_PK | 7 | | 1016 (0)| 00:00:01 | 1 | 508 |
| 18 | PARTITION RANGE ALL | | 1 | 285 | 257 (0)| 00:00:01 | 1 | 127 |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID | GEA_ASSET | 1 | 285 | 257 (0)| 00:00:01 | 1 | 127 |
|* 20 | INDEX RANGE SCAN | FTTC_ASSET_IDX | 2 | | 254 (0)| 00:00:01 | 1 | 127 |
| 21 | TABLE ACCESS FULL | ALD_CONTROL_TABLE | 1 | 9 | 3 (0)| 00:00:01 | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | CUPID | 1 | 70 | 0 (0)| 00:00:01 | | |
|* 23 | INDEX RANGE SCAN | CUPID_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 24 | VIEW | | 1903 | 104K| 13 (8)| 00:00:01 | | |
|* 25 | WINDOW SORT PUSHED RANK | | 1903 | 98956 | 13 (8)| 00:00:01 | | |
| 26 | TABLE ACCESS FULL | WORK_ARRANGE_GEA_FAULTS | 1903 | 98956 | 12 (0)| 00:00:01 | | |
| 27 | TABLE ACCESS BY GLOBAL INDEX ROWID | PORTL_MBORC_DTLS | 2 | 34 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 28 | INDEX RANGE SCAN | PORTL_MBORC_DTLS_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS FULL | NGA_PROD_CODE | 3 | 57 | 4 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
现在，按照以前的计划，它应该是一个哈希连接，一个并行化的查询，而不是上面的序列化查询，并且在任何地方都没有扫描索引。
同一天对sql id进行了如此多的更改似乎很奇怪。
我已经检查了统计信息，并在发现它们过时的地方进行了刷新，检查了sql代码中是否有更改-根本没有更改。
我知道此查询可能需要更多详细信息，但会要求您接受此情况，然后我可以根据需要提供所有相关详细信息。
非常感谢
罗希特
专家解答
看看这里
http://www.oracle.com/technetwork/issue-archive/2008/08-may/o38asktom-085659.html
看看v $ sql_shared_cursor中是否有多个条目
如果不是，那么最可能的原因是基数反馈，优化器试图改进初始次优计划。
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534360500346333173
http://www.oracle.com/technetwork/issue-archive/2008/08-may/o38asktom-085659.html
看看v $ sql_shared_cursor中是否有多个条目
如果不是，那么最可能的原因是基数反馈，优化器试图改进初始次优计划。
https://blogs.oracle.com/optimizer/entry/cardinality_feedback
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534360500346333173
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
