Hi folks,
I have a situation like this:
10.2.0.3 EE on Solaris 10.
SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name
15:01:15 2 FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('ATBI_TDS')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;
SID BLOCKER EVENT TY LMODE REQUEST OBJECT_NAME
---------- ---------- ------------------------------ -- ---------- ---------- --------------------
224 251 enq: TM - contention TM 0 6 CDR
251 direct path read temp TM 6 0 CDR
251 direct path read temp TX 6 0
251 direct path read temp XR 2 0 TAB$
I have two sessions which are inserting records into a ranged based partitioned table CDR in parallel. There is no foreign key constraint on CDR table and there is no index on it. The SID 224 is waiting on event enq: TM - contention for more than 4 hours.
Following is the query for insertion:
INSERT /*+ APPEND PARALLEL("CDR") */ INTO "CDR"
("CLNG_NMBR", "CLD_NMBR", "RDRCTNG_NMBR", "DRTN", "RVNU", "INTRCNCT_RVNU", "ARTM_RVNU", "TM", "DT_KY", "TM_KY", "HRLY_KY", "DRCTN_KY", "SBSCRBR_TP_KY", "CLNG_OPRTR_KY", "CLD_OPRTR_KY", "LCTN_RLTNSHP_KY", "ACTVTY_KY", "CLNG_LCTN_KY", "CLD_LCTN_KY", "DT", "CLNG_CLD_OPRTR_KY", "SBSCRBR_NMBR", "BSNS_DT", "BTCH_ID", "PRCS_ID", "INS_DT", "CL_TYP", "SRC_CLNG_NMBR", "SRC_CLD_NMBR", "SRC_SRVC_INV_TM", "CDR_FILE_NM", "ACTVTY_CTGRY", "ACTVTY_TP", "ACTVTY_CLASS", "ACTVTY_IS_VAS_IND", "ACTVTY_CL_TP")
(SELECT TO_NUMBER( "PREPAID_MT_SMS_DATA"."CALLING_NUMBER" ) "CALLING_NUMBER", TO_NUMBER( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ) "CALLED_NUMBER", "PREPAID_MT_SMS_DATA"."RDRCTNG_NMBR" "RDRCTNG_NMBR", "PREPAID_MT_SMS_DATA"."ACTUAL_DURATION" "ACTUAL_DURATION", "PREPAID_MT_SMS_DATA"."AMOUNT_CHARGED" "AMOUNT_CHARGED", CASE WHEN "PREPAID_MT_SMS_DATA"."LOCAL_INT_FLAG" = 1 THEN 0 WHEN (CASE WHEN (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN
"GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) IN (0,-7777) THEN CASE WHEN "PREPAID_MT_SMS_DATA"."LOCAL_INT_FLAG" = 2 AND "PREPAID_MT_SMS_DATA"."TARIFF_GROUP" IN ('ATHTPSTN','ATHMT') THEN 9 ELSE -7777 END ELSE (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN "GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) END ) NOT IN ('8','6','-7777')
THEN .03 ELSE 0 END "INTER_RVNU", "PREPAID_MT_SMS_DATA"."AMOUNT_CHARGED" - (CASE WHEN "PREPAID_MT_SMS_DATA"."LOCAL_INT_FLAG" = 1 THEN 0
WHEN (CASE WHEN (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN "GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) IN (0,-7777) THEN CASE WHEN "PREPAID_MT_SMS_DATA"."LOCAL_INT_FLAG" = 2 AND "PREPAID_MT_SMS_DATA"."TARIFF_GROUP" IN ('ATHTPSTN','ATHMT') THEN 9 ELSE -7777 END ELSE (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN "GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) END ) NOT IN (8,-7777) AND "PREPAID_MT_SMS_DATA"."CALL_TYPE" IN (0,2,5) THEN "CRG_TDS_LD_MTSMS_PREPAID"."GET_CONSTANT_4_INTERCONN" * ("PREPAID_MT_SMS_DATA"."ACTUAL_DURATION" / 60) ELSE 0 END ) "AIR_TIME", "PREPAID_MT_SMS_DATA"."CALL_TIME" "CALL_TIME", NVL("DT_LKP"."DAY_ID", NULL) "DAY_ID", CASE WHEN "PREPAID_MT_SMS_DATA"."PEAKOFFPEAK" = 'A' THEN 1 WHEN "PREPAID_MT_SMS_DATA"."PEAKOFFPEAK" = 'B'
THEN 2 ELSE -7777 END "PK_OFPK_KY", NVL("HRLY_KR"."HRLY_KY", -7777) "HRLY_KY", CASE WHEN "PREPAID_MT_SMS_DATA"."CALL_TYPE" IN (0,2,5) THEN 1 WHEN "PREPAID_MT_SMS_DATA"."CALL_TYPE" IN (1,6) THEN 2 END "DRCTN", "CRG_TDS_LD_MTSMS_PREPAID"."GET_CONSTANT_2_SBS_TYPE_KY" "SBS_TYPE_KY", "CRG_TDS_LD_MTSMS_PREPAID"."GET_CONSTANT_1_CLNG_OPRTR_KY" "CLNG_OPRTR_KY", CASE WHEN (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN "GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) IN (0,-7777) THEN CASE WHEN "PREPAID_MT_SMS_DATA"."LOCAL_INT_FLAG" = 2 AND "PREPAID_MT_SMS_DATA"."TARIFF_GROUP" IN ('ATHTPSTN','ATHMT') THEN 9 ELSE -7777 END ELSE (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN "GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) END "CLD_OPRTR_KY", "PREPAID_MT_SMS_DATA"."LOCAL_INT_FLAG" "LOCAL_INT_FLAG", '76' "ACTVTY_KY", "CRG_TDS_LD_MTSMS_PREPAID"."GET_CONSTANT_3_CLNG_LCTN_KY" "CLNG_LCTN_KY", "PREPAID_MT_SMS_DATA"."COUNTRY_CODE" "COUNTRY_CODE", "PREPAID_MT_SMS_DATA"."CALL_DATE" "CALL_DATE", NVL("CLNG_CLD_KY"."CLNG_CLD_OPRTR_KY", -7777) "CLNG_CLD_OPRTR_KY", TO_NUMBER( "PREPAID_MT_SMS_DATA"."CALLING_NUMBER" ) "CALLING_NUMBER$1", :B1 "BSNS_DT$1", :B3 "BATCH_ID$1", :B2 "PRCS_ID$1", :B1 "BSNS_DT$2", "PREPAID_MT_SMS_DATA"."CALL_TYPE" "CALL_TYPE", "PREPAID_MT_SMS_DATA"."CALLING_NUMBER" "CALLING_NUMBER$2", "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" "CALLED_NUMBER$1", "PREPAID_MT_SMS_DATA"."CALL_DATE" "CALL_DATE$1", "CRG_TDS_LD_MTSMS_PREPAID"."GET_CONSTANT_0_FL_NM" "FL_NM", 'MT SMS' "CTGRY", 'MT SMS' "TP", 'Data' "CLASS", 'N' "IS_VAS", 'COMMON CALL' "ACTVTY_CL_TP" FROM "ATBI_DDS"."DT_DIM" "DT_LKP" RIGHT OUTER JOIN "PREPAID_MT_SMS_DATA" "PREPAID_MT_SMS_DATA" ON ( ( "DT_LKP"."DAY" = (TRUNC( "PREPAID_MT_SMS_DATA"."CALL_DATE" ) ) ) ) LEFT OUTER JOIN "ATBI_LDS"."HRLY_BRKP_LKP" "HRLY_KR" ON ( ( "HRLY_KR"."HR" = (SUBSTR( "PREPAID_MT_SMS_DATA"."CALL_TIME" ,1,2) ) ) ) LEFT OUTER JOIN "ATBI_LDS"."ACTVTY_LKP" "ATVTY_LKP_CODE" ON ( ( "ATVTY_LKP_CODE"."ACTVTY_CD" = (TO_NUMBER( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ) ) ) ) LEFT OUTER JOIN "ATBI_LDS"."ACTVTY_LKP" "ACTVTY_LKP_CL_TYP" ON ( ( "ACTVTY_LKP_CL_TYP"."ACTVTY_CD" = "PREPAID_MT_SMS_DATA"."CALL_TYPE" ) ) LEFT OUTER JOIN "ATBI_LDS"."CLNG_CLD_OPRTRS_LKP" "CLNG_CLD_KY" ON ( ( "CLNG_CLD_KY"."CD" = ("CRG_TDS_LD_MTSMS_PREPAID"."GET_CONSTANT_1_CLNG_OPRTR_KY" * 10 + (CASE WHEN (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN "GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) IN (0,-7777) THEN CASE WHEN "PREPAID_MT_SMS_DATA"."LOCAL_INT_FLAG" = 2 AND "PREPAID_MT_SMS_DATA"."TARIFF_GROUP" IN ('ATHTPSTN','ATHMT') THEN 9 ELSE -7777 END ELSE (CASE WHEN SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER",0),1,3) = '964' THEN "GET_OPR_KY" (TO_NUMBER(SUBSTR(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0),4) )) ELSE "GET_OPR_KY"(TO_NUMBER(LTRIM( "PREPAID_MT_SMS_DATA"."CALLED_NUMBER" ,0) )) END ) END ) ) ) ) )
Any suggestions please?
regards
Edited by: Fahd Mirza on May 3, 2010 6:17 PM
Edited by: Fahd Mirza on May 3, 2010 6:18 PM