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 Go to next message
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 #638029 is a reply to message #638023] Mon, 01 June 2015 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why is 1 second as shown in PLAN not fast enough?
Re: Please help for improving execution plan [message #638031 is a reply to message #638029] Mon, 01 June 2015 09:39 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
No Blackswan,

View performance is not good and execution time is approx 5 mins for single record.
Re: Please help for improving execution plan [message #638032 is a reply to message #638031] Mon, 01 June 2015 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Please help for improving execution plan [message #638046 is a reply to message #638023] Mon, 01 June 2015 12:24 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 #638070 is a reply to message #638061] Tue, 02 June 2015 02:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have already ignored my suggestion that you remove the unnecessary compound query, that makes up most of the cost.
Re: Please help for improving execution plan [message #638075 is a reply to message #638070] Tue, 02 June 2015 02:28 Go to previous messageGo to next message
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 Go to previous message
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).
Previous Topic: properly tune parallel parameters?
Next Topic: Oracle Performance Issue linked to Concurrency
Goto Forum:
  


Current Time: Fri Mar 29 03:03:19 CDT 2024