Hello Team,
I have a particular sql query which runs on a daily basis for around 40 mins on an average. Today it has been running since 4:50 BST and has not completed at all.
I analysed what went wrong today and have detailed out what I could :
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 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Now as per the previous plan it should have been a hash join throughout, a parallelized query instead of a serialized one above and there were no indexes being scanned anywhere at all.
So many changes to a sql id on the same day seems odd.
I have checked the stats and refreshed wherever found them as stale, checked if there were changes in the sql code -- no changes at all.
I know there may be more details required for this query but would request you to accept this case and I can then provide all the relevant details as required.
Many thanks
Rohit