1 Reply Latest reply on Jul 29, 2013 9:58 AM by 985871

    Query Running slow

    985871

      Hi ,

       

      I am on 11.2.0.3 with 2 node rac .

      Below is the query which is taking 1 min and 30 seconds to complete and needs to be tunned .

      Gather stats is upto date

       

      SELECT service_id, alternative_id, asset_creation_date, asset_type_cd,asset_billing_accnt_id, billing_account_number,
      billing_account_name, asset_owner_accnt_id, company_name, account_identity_value, product_scode,
      product_name, asset_id, asset_status, addrfrom_integration_id, addrto_integration_id,
      addrfrom_addr_line, addrto_addr_line, addrto_zipcode, addrfrom_zipcode, parent_asset_id,source_last_upd
      FROM
      (SELECT /*+ FIRST_ROWS PARALLEL(6) */ count(1) over() cnt,ast.serial_num service_id, serpt.serial_num alternative_id,
                ast.created asset_creation_date, ast.type_cd asset_type_cd,ast.bill_accnt_id asset_billing_accnt_id,
                billact.ou_num billing_account_number, billact.NAME billing_account_name, ast.owner_accnt_id asset_owner_accnt_id,
                cug.NAME company_name,ast.integration_id account_identity_value, prod.part_num product_scode, prod.NAME product_name,
                ast.row_id asset_id,ast.status_cd asset_status,
                DECODE( addrfrom.integration_id,NULL,addr.integration_id, addrfrom.integration_id) addrfrom_integration_id,
                DECODE( addrfrom.integration_id,NULL,NULL,addrto.integration_id) addrto_integration_id,      
                RTRIM(DECODE( addrfrom.integration_id,NULL,addr.addr
                || DECODE (addr.addr, NULL, NULL, ',')
                || addr.addr_line_2
                || DECODE (addr.addr_line_2, NULL, NULL, ',')
                || addr.addr_line_3
                || DECODE (addr.addr_line_3, NULL, NULL, ',')
                || addr.addr_num
                || DECODE (addr.addr_num, NULL, NULL, ',')
                || addr.x_premise_name
                || DECODE (addr.x_premise_name, NULL, NULL, ',')
                || addr.city
                || DECODE (addr.city, NULL, NULL, ',')
                || addr.country,
                   addrfrom.addr
                || DECODE (addrfrom.addr, NULL, NULL, ',')
                || addrfrom.addr_line_2
                || DECODE (addrfrom.addr_line_2, NULL, NULL, ',')
                || addrfrom.addr_line_3
                || DECODE (addrfrom.addr_line_3, NULL, NULL, ',')
                || addrfrom.addr_num
                || DECODE (addrfrom.addr_num, NULL, NULL, ',')
                || addrfrom.x_premise_name
                || DECODE (addrfrom.x_premise_name, NULL, NULL, ',')
                || addrfrom.city
                || DECODE (addrfrom.city, NULL, NULL, ',')
                || addrfrom.country),',') addrfrom_addr_line,         
                   RTRIM(DECODE( addrfrom.integration_id,NULL,NULL,addrto.addr
                || DECODE (addrto.addr, NULL, NULL, ',')
                || addrto.addr_line_2
                || DECODE (addrto.addr_line_2, NULL, NULL, ',')
                || addrto.addr_line_3
                || DECODE (addrto.addr_line_3, NULL, NULL, ',')
                || addrto.addr_num
                || DECODE (addrto.addr_num, NULL, NULL, ',')
                || addrto.x_premise_name
                || DECODE (addrto.x_premise_name, NULL, NULL, ',')
                || addrto.city
                || DECODE (addrto.city, NULL, NULL, ',')
                || addrto.country),',') addrto_addr_line,
                DECODE( addrfrom.integration_id,NULL,NULL,addrto.zipcode) addrto_zipcode,
                DECODE( addrfrom.integration_id,NULL,addr.zipcode,addrfrom.zipcode) addrfrom_zipcode,
                ast.par_asset_id parent_asset_id,
                ast.last_upd source_last_upd      
           FROM s_asset ast,
                s_asset serpt,
                s_asset_om astom,
                s_org_ext cug,
                s_org_ext billact,
                s_org_ext frmsrv,
                s_org_ext sac,
                s_org_ext tosrv,
                s_prod_int prod,
                s_addr_per addr,
                s_addr_per addrfrom,
                s_addr_per addrto,
                s_bu bu,
                s_org_ext srv,
                (SELECT start_execution_time, last_execution_time
                   FROM odi_last_execution_details
                  WHERE scenario ='LOAD_ONE_SIEBEL_TO_WCDS_DS_INVENTORY_PKG') odi_exec
        WHERE bu.NAME                   = 'BT Wholesale Markets'
        AND ast.bu_id                   = bu.row_id
        AND ast.prod_id                 = prod.row_id
        AND ast.owner_accnt_id          = cug.par_row_id
        AND ast.bill_accnt_id           = billact.par_row_id
        AND cug.par_ou_id               = sac.par_row_id
        AND ast.service_point_id        = serpt.row_id(+)
        AND ast.serv_acct_id            = srv.par_row_id(+)
        AND srv.pr_addr_id              = addr.row_id(+)
        AND frmsrv.pr_addr_id           = addrfrom.row_id(+)
        AND tosrv.pr_addr_id            = addrto.row_id(+)
        AND ast.row_id                  = astom.par_row_id(+)
        AND astom.to_srv_accnt_id       = tosrv.par_row_id(+)
        AND ast.x_from_node_service_accnt_id = frmsrv.par_row_id(+)
        AND ((ast.process_timestamp         >= odi_exec.start_execution_time AND ast.process_timestamp      < odi_exec.last_execution_time)
           OR (serpt.process_timestamp    >= odi_exec.start_execution_time AND serpt.process_timestamp    < odi_exec.last_execution_time)
           OR (astom.process_timestamp    >= odi_exec.start_execution_time AND astom.process_timestamp    < odi_exec.last_execution_time)
           OR (cug.process_timestamp      >= odi_exec.start_execution_time AND cug.process_timestamp      < odi_exec.last_execution_time)
           OR (billact.process_timestamp  >= odi_exec.start_execution_time AND billact.process_timestamp  < odi_exec.last_execution_time)
           OR (srv.process_timestamp      >= odi_exec.start_execution_time AND srv.process_timestamp      < odi_exec.last_execution_time)
           OR (frmsrv.process_timestamp      >= odi_exec.start_execution_time AND frmsrv.process_timestamp      < odi_exec.last_execution_time)
           OR (tosrv.process_timestamp      >= odi_exec.start_execution_time AND tosrv.process_timestamp      < odi_exec.last_execution_time)
           OR (sac.process_timestamp      >= odi_exec.start_execution_time AND sac.process_timestamp       < odi_exec.last_execution_time)
           OR (prod.process_timestamp     >= odi_exec.start_execution_time AND prod.process_timestamp     < odi_exec.last_execution_time)
           OR (addrfrom.process_timestamp >= odi_exec.start_execution_time AND addrfrom.process_timestamp < odi_exec.last_execution_time)
           OR (addrto.process_timestamp   >= odi_exec.start_execution_time AND addrto.process_timestamp   < odi_exec.last_execution_time)
           OR (bu.process_timestamp       >= odi_exec.start_execution_time AND bu.process_timestamp       < odi_exec.last_execution_time)
          ))
          WHERE rownum <=cnt;
      

       

       

       

      below is the explain plan

       

       

       

      PLAN_TABLE_OUTPUT

      ------------------------------------------------------------------------------------------------------------------------

      Plan hash value: 68803991

       

       

      ------------------------------------------------------------------------------------------------------------------------

      ----------------------------------------

       

       

      | Id  | Operation                                            | Name                       | Rows  | Bytes | Cost (%CPU)|

      Time     |    TQ  |IN-OUT| PQ Distrib |

       

       

      ------------------------------------------------------------------------------------------------------------------------

      ----------------------------------------

       

       

      |   0 | SELECT STATEMENT                                     |                            |  1929K|  7262M|    12M  (1)| 07:11:40 |        |      |            |

       

       

      |   1 |  COUNT                                               |                            |       |    |               |          |        |      |            |

       

       

      |*  2 |   FILTER                                             |                            |       |    |               |          |        |      |            |

       

       

      |   3 |    PX COORDINATOR                                    |                            |       |    |               |          |        |      |            |

       

       

      |   4 |     PX SEND QC (RANDOM)                              | :TQ10001                   |  1929K|  7262M|    12M  (1)| 07:11:40 |  Q1,01 | P->S | QC (RAND)  |

       

       

      |   5 |      VIEW                                            |                            |  1929K|  7262M|    12M  (1)| 07:11:40 |  Q1,01 | PCWP |            |

       

       

      |   6 |       WINDOW BUFFER                                  |                            |  1929K|  1542M|    12M  (1)| 07:11:40 |  Q1,01 | PCWP |            |

       

       

      |*  7 |        FILTER                                        |                            |       |    |               |          |  Q1,01 | PCWC |            |

       

       

      |   8 |         NESTED LOOPS OUTER                           |                            |  1929K|  1542M|    12M  (1)| 07:11:40 |  Q1,01 | PCWP |            |

       

       

      |   9 |          NESTED LOOPS OUTER                          |                            |  3524K|  2473M|    11M  (1)| 06:28:30 |  Q1,01 | PCWP |            |

       

       

      |  10 |           NESTED LOOPS OUTER                         |                            |  3524K|  2359M|    10M  (1)| 05:44:58 |  Q1,01 | PCWP |            |

       

       

      |  11 |            NESTED LOOPS OUTER                        |                            |  3524K|  2245M|    10M  (1)| 05:44:50 |  Q1,01 | PCWP |            |

       

       

      |  12 |             NESTED LOOPS OUTER                       |                            |  3524K|  2167M|  9038K  (1)| 05:01:18 |  Q1,01 | PCWP |            |

       

       

      |  13 |              NESTED LOOPS                            |                            |  3524K|  1825M|  7743K  (1)| 04:18:08 |  Q1,01 | PCWP |            |

       

       

      |  14 |               NESTED LOOPS OUTER                     |                            |  3680K|  1828M|  6379K  (1)| 03:32:40 |  Q1,01 | PCWP |            |

       

       

      |  15 |                NESTED LOOPS OUTER                    |                            |  3680K|  1470M|  5027K  (1)| 02:47:35 |  Q1,01 | PCWP |            |

       

       

      |  16 |                 NESTED LOOPS OUTER                   |                            |  3680K|  1351M|  4442K  (1)| 02:28:06 |  Q1,01 | PCWP |            |

       

       

      |  17 |                  NESTED LOOPS                        |                            |  3680K|  1231M|  4438K  (1)| 02:27:57 |  Q1,01 | PCWP |            |

       

       

      |  18 |                   NESTED LOOPS                       |                            |  3680K|  1074M|  3074K  (1)| 01:42:29 |  Q1,01 | PCWP |            |

       

       

      |  19 |                    NESTED LOOPS                      |                            |  3680K|   919M|  1710K  (1)| 00:57:02 |  Q1,01 | PCWP |            |

       

       

      |  20 |                     BUFFER SORT                      |                            |       |    |               |          |  Q1,01 | PCWC |            |

       

       

      |  21 |                      PX RECEIVE                      |                            |       |    |               |          |  Q1,01 | PCWP |            |

       

       

      |  22 |                       PX SEND ROUND-ROBIN            | :TQ10000                   |       |    |               |          |        | S->P | RND-ROBIN  |

       

       

      |  23 |                        NESTED LOOPS                  |                            |  3680K|   744M|  1028K  (1)| 00:34:17 |        |      |            |

       

       

      |  24 |                         MERGE JOIN CARTESIAN         |                            |     1 |    96 |     5   (0)| 00:00:01 |        |      |            |

       

       

      |  25 |                          TABLE ACCESS BY INDEX ROWID | ODI_LAST_EXECUTION_DETAILS |     1 |    55 |     2   (0)| 00:00:01 |        |      |            |

       

       

      |* 26 |                           INDEX FULL SCAN            | PAS_S_EXECUTION_DETAILS_PK |     1 |    |        1   (0)| 00:00:01 |        |      |            |

       

       

      |  27 |                          BUFFER SORT                 |                            |     1 |    41 |     3   (0)| 00:00:01 |        |      |            |

       

       

      |  28 |                           TABLE ACCESS BY INDEX ROWID| S_BU                       |     1 |    41 |     2   (0)| 00:00:01 |        |      |            |

       

       

      |* 29 |                            INDEX RANGE SCAN          | S_BU_NAME                  |     1 |    |        1   (0)| 00:00:01 |        |      |            |

       

       

      |* 30 |                         TABLE ACCESS BY INDEX ROWID  | S_ASSET                    |  4784K|   529M|   190K  (1)| 00:06:21 |        |      |            |

       

       

      |* 31 |                          INDEX FULL SCAN             | S_ASSET_23960M_X           |  9749K|    | 14129   (1)| 00:00:29 |        |      |            |

       

       

      |  32 |                     TABLE ACCESS BY INDEX ROWID      | S_PROD_INT                 |     1 |    50 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 33 |                      INDEX UNIQUE SCAN               | S_PROD_INT_P1              |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  34 |                    TABLE ACCESS BY INDEX ROWID       | S_ORG_EXT                  |     1 |    44 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 35 |                     INDEX UNIQUE SCAN                | S_ORG_EXT_U3               |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  36 |                   TABLE ACCESS BY INDEX ROWID        | S_ORG_EXT                  |     1 |    45 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 37 |                    INDEX UNIQUE SCAN                 | S_ORG_EXT_U3               |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  38 |                  TABLE ACCESS BY INDEX ROWID         | S_ORG_EXT                  |     1 |    34 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 39 |                   INDEX UNIQUE SCAN                  | S_ORG_EXT_U3               |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  40 |                 TABLE ACCESS BY INDEX ROWID          | S_ORG_EXT                  |     1 |    34 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 41 |                  INDEX UNIQUE SCAN                   | S_ORG_EXT_U3               |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  42 |                TABLE ACCESS BY INDEX ROWID           | S_ADDR_PER                 |     1 |   102 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 43 |                 INDEX UNIQUE SCAN                    | S_ADDR_PER_P1              |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  44 |               TABLE ACCESS BY INDEX ROWID            | S_ORG_EXT                  |     1 |    22 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 45 |                INDEX UNIQUE SCAN                     | S_ORG_EXT_U3               |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  46 |              TABLE ACCESS BY INDEX ROWID             | S_ADDR_PER                 |     1 |   102 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 47 |               INDEX UNIQUE SCAN                      | S_ADDR_PER_P1              |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  48 |             TABLE ACCESS BY INDEX ROWID              | S_ASSET_OM                 |     1 |    23 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 49 |              INDEX UNIQUE SCAN                       | S_ASSET_OM_U1              |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  50 |            TABLE ACCESS BY INDEX ROWID               | S_ORG_EXT                  |     1 |    34 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 51 |             INDEX UNIQUE SCAN                        | S_ORG_EXT_U3               |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  52 |           TABLE ACCESS BY INDEX ROWID                | S_ASSET                    |     1 |    34 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 53 |            INDEX UNIQUE SCAN                         | S_ASSET_P1                 |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |  54 |          TABLE ACCESS BY INDEX ROWID                 | S_ADDR_PER                 |     1 |   102 |     0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

      |* 55 |           INDEX UNIQUE SCAN                          | S_ADDR_PER_P1              |     1 |    |        0   (0)| 00:00:01 |  Q1,01 | PCWP |            |

       

       

       

       

      Message was edited by: 985871