Home » RDBMS Server » Performance Tuning » Please help for improving execution plan (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Please help for improving execution plan [message #638023] |
Mon, 01 June 2015 09:20  |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Dear Team,
I Stuck for my query which is getting used in view.Please suggest me for improving my execution plan.Please find below mentioned execution plan.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10135 | 92 (8)| 00:00:02 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | SERVICE_MASTR_EQMPT_PLMODE00 | 1 | 50 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SERVICE_MASTR_EQMPT_PLACNUM | 1 | | 1 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | SORT GROUP BY | | 1 | 43 | 2 (50)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | DCP_RESEND_REQ_SADCPL | 4 | 172 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | DCP_RESEND_REQ_SADCPL_ACCS | 6 | | 1 (0)| 00:00:01 |
| 13 | SORT AGGREGATE | | 1 | 38 | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | DCP_RESEND_REQ_SADCPL | 4 | 152 | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | DCP_RESEND_REQ_SADCPL_ACCS | 6 | | 1 (0)| 00:00:01 |
|* 16 | VIEW | | 1 | 10135 | 92 (8)| 00:00:02 |
|* 17 | WINDOW SORT PUSHED RANK | | 1 | 108 | 92 (8)| 00:00:02 |
|* 18 | FILTER | | | | | |
|* 19 | HASH JOIN | | 1 | 108 | 25 (4)| 00:00:01 |
| 20 | NESTED LOOPS | | | | | |
| 21 | NESTED LOOPS | | 1 | 84 | 3 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 60 | 2 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 3 | | 1 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | SERVICE_MASTER_BLSUBI | 1 | 21 | 1 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | BLSUBI_SERVICE_ACC_IX | 1 | | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | SERVICE_EQ_BLUSOC_SRVC_NO_IX | 6 | | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | SERVICE_EQUIPMENT_BLUSOC | 2 | 48 | 1 (0)| 00:00:01 |
|* 29 | TABLE ACCESS STORAGE FULL | FAULT_CODE_RELATION_FMSSFX00 | 1562 | 37488 | 21 (0)| 00:00:01 |
|* 30 | FILTER | | | | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | SERVICE_EQUIPMENT_BLUSOC | 1 | 24 | 1 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | SERVICE_EQ_BLUSOC_SRVC_NO_IX | 6 | | 1 (0)| 00:00:01 |
| 33 | SORT UNIQUE | | 20 | 56 | 65 (82)| 00:00:01 |
| 34 | UNION-ALL | | | | | |
|* 35 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 24 | 12 (0)| 00:00:01 |
|* 36 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
|* 37 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
|* 38 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
|* 39 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | NET_MISC_SWT_PLM_ACC_NUM_IX | 1 | 21 | 1 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | | | | |
| 42 | NESTED LOOPS | | 1 | 63 | 2 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID | PLANT_LINE_CARD_PLTLCD | 1 | 46 | 1 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | PLANT_LINE_CARD_PLTLCD_IX1 | 1 | | 1 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | PLANT_SRVCAREA_MASTER_BUL_IX | 1 | | 1 (0)| 00:00:01 |
|* 46 | TABLE ACCESS BY INDEX ROWID | PLANT_SERVICEAREA_MASTER_PLSAM | 19 | 323 | 1 (0)| 00:00:01 |
|* 47 | TABLE ACCESS BY INDEX ROWID | PLANT_LINE_CARD_PLTLCD | 1 | 38 | 1 (0)| 00:00:01 |
|* 48 | INDEX RANGE SCAN | PLANT_LINE_CARD_PLTLCD_IX1 | 1 | | 1 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | NET_MISC_SWT_PLM_ACC_NUM_IX | 1 | 21 | 1 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID | PLANT_LINE_CARD_PLTLCD | 1 | 38 | 1 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | PLANT_LINE_CARD_PLTLCD_IX1 | 1 | | 1 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | NET_MISC_SWT_PLM_ACC_NUM_IX | 1 | 21 | 1 (0)| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID | PLANT_LINE_CARD_PLTLCD | 1 | 38 | 1 (0)| 00:00:01 |
|* 54 | INDEX RANGE SCAN | PLANT_LINE_CARD_PLTLCD_IX1 | 1 | | 1 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 27 | 5 (0)| 00:00:01 |
|* 56 | TABLE ACCESS BY INDEX ROWID | SERVICE_EQUIPMENT_BLUSOC | 1 | 16 | 1 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | SERVICE_EQ_BLUSOC_SRVC_NO_IX | 6 | | 1 (0)| 00:00:01 |
|* 58 | TABLE ACCESS STORAGE FULL FIRST ROWS| DSL_SE_SPEED_PRMTR_SADQSC | 1 | 11 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<2)
4 - filter("SERVICE_CODE"=:B1 AND "EXPIRY_DATE">SYSDATE@!)
5 - access("ACCESS_NUMBER"=:B1)
9 - filter("CREATION_TIME_STAMP"= (SELECT MAX("CREATION_TIME_STAMP") FROM "DM_ICMS_LL"."DCP_RESEND_REQ_SADCPL"
"DCP_RESEND_REQ_SADCPL" WHERE "ACCESS_NUMBER"=:B1 AND ("ACTIVATION_TYPE"='N' OR "ACTIVATION_TYPE"='R' OR
"ACTIVATION_TYPE"='U')))
11 - filter("ACTIVATION_TYPE"='N' OR "ACTIVATION_TYPE"='R' OR "ACTIVATION_TYPE"='U')
12 - access("ACCESS_NUMBER"=:B1)
14 - filter("ACTIVATION_TYPE"='N' OR "ACTIVATION_TYPE"='R' OR "ACTIVATION_TYPE"='U')
15 - access("ACCESS_NUMBER"=:B1)
16 - filter("DENSERANK"=1)
17 - filter(DENSE_RANK() OVER ( PARTITION BY "SUBXTL"."ACCESS_NUMBER" ORDER BY CASE "FMSSFX"."MAIN_Y_N" WHEN 'Y'
THEN 1 ELSE 0 END DESC ,"FMSSFX"."EFFECTIVE_DATE")<=1)
18 - filter("FMSSFX"."FAULT_TYPE"=CASE WHEN EXISTS (SELECT 0 FROM "DM_ICMS_LL"."SERVICE_EQUIPMENT_BLUSOC"
"SERVICE_EQUIPMENT_BLUSOC" WHERE EXISTS ( (SELECT TRIM("PRODUCT_ID_S_E_OF_STB1") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB1")=:B1)UNION (SELECT TRIM("PRODUCT_ID_S_E_OF_STB2") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB2")=:B2)UNION (SELECT TRIM("PRODUCT_ID_S_E_OF_STB3") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB3")=:B3)UNION (SELECT TRIM("PRODUCT_ID_S_E_OF_STB4") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB4")=:B4)UNION (SELECT TRIM("PRODUCT_ID_S_E_OF_STB5") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB5")=:B5)) AND "SERVICE_NUMBER"=:B6 AND "SERVICE_CODE"=:B7 AND "OUT_DATE">SYSDATE@!)
THEN CASE WHEN EXISTS (SELECT 0 FROM "DM_ICMS_LL"."NET_MISCELL_SWITCH_PLMISW00" "NET_MISCELL_SWITCH_PLMISW00"
WHERE "ACCESS_NUMBER"=:B8) THEN 'I' WHEN EXISTS (SELECT 0 FROM "DM_ICMS_LL"."PLANT_SERVICEAREA_MASTER_PLSAM"
"PLSAM","DM_ICMS_LL"."PLANT_LINE_CARD_PLTLCD" "PLTLCD" WHERE "PLTLCD"."MEMBER_ACCESS_NUMBER"=:B9 AND
"PLTLCD"."SERVICE_CODE"=:B10 AND "PLTLCD"."DISCONNECT_DATE">SYSDATE@! AND
"PLTLCD"."BUILDING_NAME"="PLSAM"."BUILDING_NAME" AND "PLSAM"."TAX_DISTRICT"='FT') THEN 'I' WHEN EXISTS (SELECT 0
FROM "DM_ICMS_LL"."PLANT_LINE_CARD_PLTLCD" "PLTLCD" WHERE "PLTLCD"."MEMBER_ACCESS_NUMBER"=:B11 AND
"PLTLCD"."SERVICE_CODE"=:B12 AND "PLTLCD"."SERVICE_TYPE"='VDSL' AND "PLTLCD"."DISCONNECT_DATE">SYSDATE@!) THEN 'I'
END ELSE CASE WHEN ( EXISTS (SELECT 0 FROM "DM_ICMS_LL"."NET_MISCELL_SWITCH_PLMISW00"
"NET_MISCELL_SWITCH_PLMISW00" WHERE "ACCESS_NUMBER"=:B13) AND EXISTS (SELECT 0 FROM
"DM_ICMS_LL"."PLANT_LINE_CARD_PLTLCD" "PLTLCD" WHERE "PLTLCD"."MEMBER_ACCESS_NUMBER"=:B14 AND
"PLTLCD"."SERVICE_CODE"=:B15 AND "PLTLCD"."SERVICE_TYPE"='VDSL' AND "PLTLCD"."DISCONNECT_DATE">SYSDATE@!)) THEN
'V' WHEN ( EXISTS (SELECT 0 FROM "DM_ICMS_LL"."NET_MISCELL_SWITCH_PLMISW00" "NET_MISCELL_SWITCH_PLMISW00" WHERE
"ACCESS_NUMBER"=:B16) AND EXISTS (SELECT 0 FROM "DM_ICMS_LL"."PLANT_LINE_CARD_PLTLCD" "PLTLCD" WHERE
"PLTLCD"."MEMBER_ACCESS_NUMBER"=:B17 AND "PLTLCD"."SERVICE_CODE"=:B18 AND "PLTLCD"."SERVICE_TYPE"<>'VDSL' AND
"PLTLCD"."DISCONNECT_DATE">SYSDATE@!) AND EXISTS (SELECT 0 FROM "DM_ICMS_LL"."SERVICE_EQUIPMENT_BLUSOC"
"BLUSOC_IN","DM_ICMS_LL"."DSL_SE_SPEED_PRMTR_SADQSC" "SADQSC" WHERE "SPEED_S_E"='Y' AND
"SADQSC"."SERVICE_CODE"=:B19 AND "BLUSOC_IN"."ITEM_NUMBER"="SADQSC"."S_E_ITEM_CODE" AND
"BLUSOC_IN"."SERVICE_NUMBER"=:B20 AND "BLUSOC_IN"."SERVICE_CODE"=:B21)) THEN 'D' ELSE 'S' END END )
19 - access("FMSSFX"."SERVICE_TYPE"="BLSUBI"."SERVICE_TYPE" AND "FMSSFX"."S_E_ITEM"="BLUSOC"."ITEM_NUMBER")
23 - filter("SUBXTL"."DISCONNECTION_DATE">SYSDATE@!)
24 - access("SUBXTL"."ACCESS_NUMBER"='00000000000112000013')
25 - filter("BLSUBI"."EXPIRY_DATE">SYSDATE@! AND "BLSUBI"."SERVICE_CODE"="SUBXTL"."SERVICE_CODE")
26 - access("BLSUBI"."SERVICE_NUMBER"="SUBXTL"."SERVICE_NUMBER")
27 - access("BLUSOC"."SERVICE_NUMBER"="SUBXTL"."SERVICE_NUMBER")
28 - filter("BLUSOC"."OUT_DATE">SYSDATE@! AND "BLUSOC"."SERVICE_CODE"="SUBXTL"."SERVICE_CODE")
29 - storage("FMSSFX"."SERVICE_CODE"='TL')
filter("FMSSFX"."SERVICE_CODE"='TL')
30 - filter( EXISTS ( (SELECT TRIM("PRODUCT_ID_S_E_OF_STB1") FROM "DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00"
"IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE TRIM("PRODUCT_ID_S_E_OF_STB1")=:B1)UNION (SELECT
TRIM("PRODUCT_ID_S_E_OF_STB2") FROM "DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00"
WHERE TRIM("PRODUCT_ID_S_E_OF_STB2")=:B2)UNION (SELECT TRIM("PRODUCT_ID_S_E_OF_STB3") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB3")=:B3)UNION (SELECT TRIM("PRODUCT_ID_S_E_OF_STB4") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB4")=:B4)UNION (SELECT TRIM("PRODUCT_ID_S_E_OF_STB5") FROM
"DM_ICMS_LL"."IPTV_PRDCT_PARAM_FILE_TVPSE00" "IPTV_PRDCT_PARAM_FILE_TVPSE00" WHERE
TRIM("PRODUCT_ID_S_E_OF_STB5")=:B5)))
31 - filter("SERVICE_CODE"=:B1 AND "OUT_DATE">SYSDATE@!)
32 - access("SERVICE_NUMBER"=:B1)
35 - filter(TRIM("PRODUCT_ID_S_E_OF_STB1")=:B1)
36 - filter(TRIM("PRODUCT_ID_S_E_OF_STB2")=:B1)
37 - filter(TRIM("PRODUCT_ID_S_E_OF_STB3")=:B1)
38 - filter(TRIM("PRODUCT_ID_S_E_OF_STB4")=:B1)
39 - filter(TRIM("PRODUCT_ID_S_E_OF_STB5")=:B1)
40 - access("ACCESS_NUMBER"=:B1)
43 - filter("PLTLCD"."SERVICE_CODE"=:B1 AND "PLTLCD"."DISCONNECT_DATE">SYSDATE@!)
44 - access("PLTLCD"."MEMBER_ACCESS_NUMBER"=:B1)
45 - access("PLTLCD"."BUILDING_NAME"="PLSAM"."BUILDING_NAME")
46 - filter("PLSAM"."TAX_DISTRICT"='FT')
47 - filter("PLTLCD"."SERVICE_CODE"=:B1 AND "PLTLCD"."SERVICE_TYPE"='VDSL' AND
"PLTLCD"."DISCONNECT_DATE">SYSDATE@!)
48 - access("PLTLCD"."MEMBER_ACCESS_NUMBER"=:B1)
49 - access("ACCESS_NUMBER"=:B1)
50 - filter("PLTLCD"."SERVICE_CODE"=:B1 AND "PLTLCD"."SERVICE_TYPE"='VDSL' AND
"PLTLCD"."DISCONNECT_DATE">SYSDATE@!)
51 - access("PLTLCD"."MEMBER_ACCESS_NUMBER"=:B1)
52 - access("ACCESS_NUMBER"=:B1)
53 - filter("PLTLCD"."SERVICE_CODE"=:B1 AND "PLTLCD"."SERVICE_TYPE"<>'VDSL' AND
"PLTLCD"."DISCONNECT_DATE">SYSDATE@!)
54 - access("PLTLCD"."MEMBER_ACCESS_NUMBER"=:B1)
56 - filter("BLUSOC_IN"."SERVICE_CODE"=:B1)
57 - access("BLUSOC_IN"."SERVICE_NUMBER"=:B1)
58 - storage("SPEED_S_E"='Y')
filter("SPEED_S_E"='Y' AND "SADQSC"."SERVICE_CODE"=:B1 AND
"BLUSOC_IN"."ITEM_NUMBER"="SADQSC"."S_E_ITEM_CODE")
|
|
|
|
|
|
|
|
|
|
| Re: Please help for improving execution plan [message #638046 is a reply to message #638023] |
Mon, 01 June 2015 12:24  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It might help if actually posted the query, you know. However, this looks pretty odd:
34 | UNION-ALL | | | | | |
|* 35 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 24 | 12 (0)| 00:00:01 |
|* 36 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
|* 37 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
|* 38 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
|* 39 | TABLE ACCESS STORAGE FULL | IPTV_PRDCT_PARAM_FILE_TVPSE00 | 4 | 8 | 12 (0)| 00:00:01 |
Looking at the predicates:
35 - filter(TRIM("PRODUCT_ID_S_E_OF_STB1")=:B1)
36 - filter(TRIM("PRODUCT_ID_S_E_OF_STB2")=:B1)
37 - filter(TRIM("PRODUCT_ID_S_E_OF_STB3")=:B1)
38 - filter(TRIM("PRODUCT_ID_S_E_OF_STB4")=:B1)
39 - filter(TRIM("PRODUCT_ID_S_E_OF_STB5")=:B1)
you are scanning that table five times, when once would do.
|
|
|
|
| Re: Please help for improving execution plan [message #638061 is a reply to message #638046] |
Tue, 02 June 2015 01:42 |
|
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Dear John,
Please find query for this one :
SELECT access_number, fault_code, fault_count AS no_of_repeated_faults,
cpe_serial_num, rtts_tt_count, node_name, equipment_number,
speed_profile
FROM (SELECT subxtl.access_number, fmssfx.main_y_n, fmssfx.fault_type,
fmssfx.effective_date, blsubi.service_type,
subxtl.service_code, subxtl.service_number,
blusoc.item_number,
TRIM
((SELECT edi_views.ov_get_fault_code_fn
(subxtl.access_number)
FROM DUAL)
) AS fault_code,
TRIM
((SELECT edi_views.ov_get_rep_fault_count_fn
(subxtl.access_number)
FROM DUAL)
) fault_count,
(SELECT modem_serial_#
FROM dm_icms_ll.service_mastr_eqmpt_plmode00
WHERE expiry_date > SYSDATE
AND access_number = subxtl.access_number
AND service_code = fmssfx.service_code
AND ROWNUM < 2) AS cpe_serial_num,
TRIM
((SELECT SUBSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
1,
INSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
1
)
- 1
)
FROM DUAL)
) rtts_tt_count,
TRIM
((SELECT SUBSTR
(SUBSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
INSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
1
)
+ 1
),
1,
INSTR
(SUBSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
INSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
1
)
+ 1
),
'|',
1,
1
)
- 1
)
FROM DUAL)
) AS node_name,
TRIM
((SELECT SUBSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
( INSTR
(edi_views.ov_get_node_en_tt_fn
(subxtl.access_number),
'|',
1,
2
)
+ 1
)
)
FROM DUAL)
) equipment_number,
(SELECT dsl_speed
---ROW_NUMBER () OVER (PARTITION BY access_number ORDER BY creation_time_stamp DESC) rnk
FROM dm_icms_ll.dcp_resend_req_sadcpl
WHERE activation_type IN
('N', 'R', 'U')
AND access_number = subxtl.access_number
GROUP BY dsl_speed, creation_time_stamp
HAVING creation_time_stamp =
(SELECT MAX (creation_time_stamp)
FROM dm_icms_ll.dcp_resend_req_sadcpl
WHERE activation_type IN ('N', 'R', 'U')
AND access_number = subxtl.access_number))
AS speed_profile,
DENSE_RANK () OVER (PARTITION BY subxtl.access_number ORDER BY (CASE
WHEN fmssfx.main_y_n = 'Y'
THEN 1
ELSE 0
END) DESC,
fmssfx.effective_date ASC) AS denserank
FROM dm_icms_ll.customer_services_ll_subxtl subxtl,
dm_icms_ll.fault_code_relation_fmssfx00 fmssfx,
dm_icms_ll.service_master_blsubi blsubi,
dm_icms_ll.service_equipment_blusoc blusoc
WHERE subxtl.disconnection_date > SYSDATE
AND blsubi.service_number = subxtl.service_number
AND blsubi.service_code = subxtl.service_code
AND blsubi.expiry_date > SYSDATE
AND fmssfx.service_code = 'TL'
AND fmssfx.service_type = blsubi.service_type
AND blusoc.service_code = subxtl.service_code
AND blusoc.service_number = subxtl.service_number
AND fmssfx.s_e_item = blusoc.item_number
--AND fmssfx.main_y_n(+) = 'Y'
AND blusoc.out_date > SYSDATE
AND fmssfx.fault_type =
(CASE
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.service_equipment_blusoc
WHERE service_number = blusoc.service_number
AND service_code = blusoc.service_code
AND item_number IN (
SELECT TRIM (product_id_s_e_of_stb1)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb2)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb3)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb4)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00
UNION
SELECT TRIM (product_id_s_e_of_stb5)
FROM dm_icms_ll.iptv_prdct_param_file_tvpse00)
AND out_date > SYSDATE)
THEN (CASE
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.net_miscell_switch_plmisw00
WHERE access_number =
subxtl.access_number)
THEN 'I'
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd,
dm_icms_ll.plant_servicearea_master_plsam plsam
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.disconnect_date >
SYSDATE
AND pltlcd.building_name =
plsam.building_name
AND plsam.tax_district = 'FT')
THEN 'I'
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.service_type =
'VDSL'
AND pltlcd.disconnect_date >
SYSDATE)
THEN 'I'
END
)
ELSE (CASE
WHEN ( EXISTS (
SELECT 1
FROM dm_icms_ll.net_miscell_switch_plmisw00
WHERE access_number =
subxtl.access_number)
AND EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.service_type =
'VDSL'
AND pltlcd.disconnect_date >
SYSDATE)
)
THEN 'V'
WHEN EXISTS (
SELECT 1
FROM dm_icms_ll.net_miscell_switch_plmisw00
WHERE access_number =
subxtl.access_number)
AND EXISTS (
SELECT 1
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd
WHERE pltlcd.service_code =
subxtl.service_code
AND pltlcd.member_access_number =
subxtl.access_number
AND pltlcd.service_type <> 'VDSL'
AND pltlcd.disconnect_date >
SYSDATE)
AND EXISTS (
SELECT 1
FROM dm_icms_ll.dsl_se_speed_prmtr_sadqsc sadqsc,
dm_icms_ll.service_equipment_blusoc blusoc_in
WHERE blusoc_in.service_number =
subxtl.service_number
AND blusoc_in.service_code =
subxtl.service_code
AND blusoc_in.item_number =
sadqsc.s_e_item_code
AND blusoc.service_code =
sadqsc.service_code
AND speed_s_e = 'Y')
THEN 'D'
ELSE 'S'
END
)
END
))
WHERE 1 = 1 AND denserank = 1;
|
|
|
|
|
|
| Re: Please help for improving execution plan [message #638075 is a reply to message #638070] |
Tue, 02 June 2015 02:28 |
|
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Dear John,
As per your suggestion.I checked that part but not getting any performance issue.As per our analysis,We are getting performance issue because we have used function in .sql query:
CREATE OR REPLACE FUNCTION ABINITIO.OV_GET_NODE_EN_TT_FN( access_number VARCHAR2)
RETURN VARCHAR2
IS
-------------------Variable Declaration-----------------------------------------------------------------
vi_access_number VARCHAR2(20) DEFAULT '0';
vi_dsl_node_name dm_icms_ll.dsl_node_name_relation_sadsnr.dsl_node_name%TYPE DEFAULT NULL;
vi_vdsl_node_name dm_icms_ll.office_coe_extention_plofcoe.vdsl_node_name%TYPE DEFAULT NULL;
vi_dsl_node_name_2 dm_icms_ll.dsl_node_name_relation_sadsnr.dsl_node_name%TYPE DEFAULT NULL;
vi_return_val VARCHAR2(20) DEFAULT '0';
vi_single_node_count NUMBER DEFAULT 0;
vi_multiple_node_count NUMBER DEFAULT 0;
vi_tot_count NUMBER DEFAULT 0;
vi_miscellaneous_en dm_icms_ll.net_miscell_switch_plmisw00.miscellaneous_en%TYPE DEFAULT NULL;
vi_node_name dm_icms_ll.dsl_node_name_relation_sadsnr.dsl_node_name%TYPE DEFAULT NULL;
BEGIN
vi_access_number := access_number;
BEGIN
/* DSL Logic for considering dsl_node_name*/
SELECT
DISTINCT sadsnr.dsl_node_name,
plmisw.miscellaneous_en
INTO vi_dsl_node_name,
vi_miscellaneous_en
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd,
dm_icms_ll.net_miscell_switch_plmisw00 plmisw,
dm_icms_ll.office_c_o_e_plofco plofco,
dm_icms_ll.dsl_node_name_relation_sadsnr sadsnr
WHERE pltlcd.service_code = plmisw.service_code
AND pltlcd.member_access_number = plmisw.access_number
AND pltlcd.disconnect_date > SYSDATE
AND plofco.switch_block = plmisw.miscellaneous_switch
AND plofco.en = plmisw.miscellaneous_en
AND plofco.card_id = plmisw.card_id
AND plofco.en_type_code <> 'VS'
AND pltlcd.exchange_code = sadsnr.EXCHANGE
AND plmisw.miscellaneous_switch = sadsnr.dsl_switch
AND sadsnr.shelf = SUBSTR (plmisw.miscellaneous_en, 3, 1)
AND sadsnr.dsl_equipment_type = SUBSTR (plmisw.card_id, 3, 1)
AND sadsnr.expiry_date > SYSDATE
AND sadsnr.dsl_node_name NOT IN (
SELECT node_name
FROM dm_icms_ll.dsl_name_rlatnshp_param_sadsne sadsne)
AND pltlcd.member_access_number = vi_access_number
AND pltlcd.service_code = 'TL'
AND rownum < 2;
EXCEPTION
WHEN no_data_found THEN
vi_dsl_node_name := '0';
WHEN others THEN
vi_dsl_node_name := '0';
END;
/* VDSL Logic for considering dsl_node_name Query1*/
IF vi_dsl_node_name = '0'
THEN
BEGIN
SELECT
DISTINCT plofcoe.VDSL_NODE_NAME,
plmisw.miscellaneous_en
INTO vi_vdsl_node_name,
vi_miscellaneous_en
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd,
dm_icms_ll.net_miscell_switch_plmisw00 plmisw,
dm_icms_ll.office_c_o_e_plofco plofco,
dm_icms_ll.office_coe_extention_plofcoe plofcoe
WHERE pltlcd.service_code = plmisw.service_code
AND pltlcd.member_access_number = plmisw.access_number
AND pltlcd.disconnect_date > SYSDATE
AND plofco.switch_block = plmisw.miscellaneous_switch
AND plofco.en = plmisw.miscellaneous_en
AND plofco.card_id = plmisw.card_id
AND plofco.en_type_code = 'VS'
and plofcoe.switch_block = plmisw.miscellaneous_switch
AND plofcoe.en = plmisw.miscellaneous_en
AND pltlcd.member_access_number = vi_access_number
AND pltlcd.service_code = 'TL'
AND rownum < 2;
EXCEPTION
WHEN no_data_found THEN
vi_vdsl_node_name := '0';
WHEN others THEN
vi_vdsl_node_name := '0';
END;
END IF;
/* VDSL Logic for considering dsl_node_name Query2*/
IF vi_vdsl_node_name = '0' and vi_dsl_node_name = '0'
THEN
BEGIN
SELECT
DISTINCT sadsnr.dsl_node_name,
plmisw.miscellaneous_en
INTO vi_dsl_node_name_2,
vi_miscellaneous_en
FROM dm_icms_ll.plant_line_card_pltlcd pltlcd,
dm_icms_ll.net_miscell_switch_plmisw00 plmisw,
dm_icms_ll.office_c_o_e_plofco plofco,
dm_icms_ll.OFFICE_COE_EXTENTION_PLOFCOE PLOFCOE,
dm_icms_ll.dsl_node_name_relation_sadsnr sadsnr,
dm_icms_ll.DSL_NAME_RLATNSHP_PARAM_SADSNE SADSNE
WHERE pltlcd.service_code = plmisw.service_code
AND pltlcd.member_access_number = plmisw.access_number
AND pltlcd.disconnect_date > SYSDATE
AND plofco.switch_block = plmisw.miscellaneous_switch
AND plofco.en = plmisw.miscellaneous_en
AND plofco.card_id = plmisw.card_id
AND plofco.en_type_code = 'VS'
and plofcoe.switch_block = plmisw.miscellaneous_switch
AND plofcoe.en = plmisw.miscellaneous_en
AND pltlcd.exchange_code = sadsnr.EXCHANGE
AND plmisw.miscellaneous_switch = sadsnr.dsl_switch
AND sadsnr.shelf = SUBSTR (plmisw.miscellaneous_en, 3, 1)
AND sadsnr.dsl_equipment_type = SUBSTR (plmisw.card_id, 3, 1)
AND sadsnr.expiry_date > SYSDATE
and sadsne.EXCHANGE_CODE = sadsnr.EXCHANGE
and sadsne.DSL_SWITCH_CODE = sadsnr.DSL_SWITCH
and sadsne.DSL_EN_TYPE = sadsnr.DSL_EQUIPMENT_TYPE
and sadsne.NODE_NAME = sadsnr.DSL_NODE_NAME
AND pltlcd.member_access_number = vi_access_number
AND rownum < 2;
EXCEPTION
WHEN no_data_found THEN
vi_dsl_node_name_2 := '0';
WHEN others THEN
vi_dsl_node_name_2 := '0';
END;
END IF;
/* Logic for considering DSL Node Name Value */
IF vi_dsl_node_name != '0'
THEN
vi_return_val := vi_dsl_node_name;
vi_node_name := vi_dsl_node_name;
ELSIF vi_vdsl_node_name != '0'
THEN
vi_return_val := vi_vdsl_node_name;
vi_node_name := vi_vdsl_node_name;
ELSIF vi_dsl_node_name_2 != '0'
THEN
vi_return_val := vi_dsl_node_name_2;
vi_node_name := vi_dsl_node_name_2;
ELSE
vi_return_val := '0';
END IF;
IF vi_dsl_node_name != '0'
THEN
/* Logic for Total Count in case of DSL */
SELECT count(1)
INTO vi_single_node_count
FROM dm_icms_ll.fault_master_fmftmx fmftmx,
dm_icms_ll.network_fault_master_fmnfmx fmnfmx
WHERE fmftmx.fault_status <> 'D'
AND fmftmx.reported_date BETWEEN SYSDATE - 90 AND SYSDATE
AND fmftmx.fault_type = 'N'
AND fmftmx.fault_number = fmnfmx.fault_number
AND TRIM (fmnfmx.from_en) IS NULL
AND TRIM (fmnfmx.to_en) IS NULL
AND fmnfmx.service_classification = 'DSL'
AND NOT EXISTS (
SELECT 1
FROM dm_icms_ll.network_fault_mltpl_dsl_fmnmdn fmnmdn
WHERE fmnfmx.fault_number = fmnmdn.network_fault_number)
AND fmnfmx.dsl_node_name = vi_return_val;
-- count of multi node trouble tickets (step 2b)
SELECT /*+ full(fmftmx) parallel(fmftmx,16) */ count(1)
INTO vi_multiple_node_count
FROM dm_icms_ll.fault_master_fmftmx fmftmx,
dm_icms_ll.network_fault_master_fmnfmx fmnfmx
WHERE fmftmx.fault_status <> 'D'
AND fmftmx.reported_date BETWEEN SYSDATE - 90 AND SYSDATE
AND fmftmx.fault_type = 'N'
AND fmftmx.fault_number = fmnfmx.fault_number
AND TRIM (fmnfmx.from_en) IS NULL
AND TRIM (fmnfmx.to_en) IS NULL
AND fmnfmx.service_classification = 'DSL'
AND EXISTS (
SELECT 1
FROM dm_icms_ll.network_fault_mltpl_dsl_fmnmdn fmnmdn
WHERE fmnfmx.fault_number = fmnmdn.network_fault_number)
AND fmnfmx.dsl_node_name = vi_return_val;
ELSE
/* Logic for Total Count in case of VDSL */
SELECT count(1)
INTO vi_single_node_count
FROM dm_icms_ll.fault_master_fmftmx fmftmx,
dm_icms_ll.network_fault_master_fmnfmx fmnfmx
WHERE fmftmx.fault_status <> 'D'
AND fmftmx.reported_date BETWEEN SYSDATE - 90 AND SYSDATE
AND fmftmx.fault_type = 'N'
AND fmftmx.fault_number = fmnfmx.fault_number
AND TRIM (fmnfmx.from_en) IS NULL
AND TRIM (fmnfmx.to_en) IS NULL
AND fmnfmx.service_classification = 'VDSL'
AND NOT EXISTS (
SELECT 1
FROM dm_icms_ll.network_fault_mltpl_dsl_fmnmdn fmnmdn
WHERE fmnfmx.fault_number = fmnmdn.network_fault_number)
AND fmnfmx.dsl_node_name = vi_return_val;
SELECT /*+ full(fmftmx) parallel(fmftmx,16) */ count(1)
INTO vi_multiple_node_count
FROM dm_icms_ll.fault_master_fmftmx fmftmx,
dm_icms_ll.network_fault_master_fmnfmx fmnfmx
WHERE fmftmx.fault_status <> 'D'
AND fmftmx.reported_date BETWEEN SYSDATE - 90 AND SYSDATE
AND fmftmx.fault_type = 'N'
AND fmftmx.fault_number = fmnfmx.fault_number
AND TRIM (fmnfmx.from_en) IS NULL
AND TRIM (fmnfmx.to_en) IS NULL
AND fmnfmx.service_classification = 'VDSL'
AND EXISTS (
SELECT 1
FROM dm_icms_ll.network_fault_mltpl_dsl_fmnmdn fmnmdn
WHERE fmnfmx.fault_number = fmnmdn.network_fault_number)
AND fmnfmx.dsl_node_name = vi_return_val;
END IF;
vi_tot_count := nvl(vi_single_node_count,0) + nvl(vi_multiple_node_count,0); /* Total Count */
RETURN vi_tot_count||'|'||vi_node_name||'|'||vi_miscellaneous_en;
END;
|
|
|
|
| Re: Please help for improving execution plan [message #638079 is a reply to message #638075] |
Tue, 02 June 2015 02:35 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
So...remove the function and put the logic into the main body so the optimizer can actually have a stab at it.
PL/SQL functions in sql are the devil. Don't do it unless you have absolutely NO other choice (and even then, the requirement is probably wrong).
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 13:11:17 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
