4 Replies Latest reply on Jan 25, 2013 3:22 PM by riedelme

    Query taking long time

    Mishra
      Hi,

      Please suggest,query is taking longer time to execute.

      Database version: 11.2.0.1.0
      OS: IBM AIX

      Query
      SELECT wrk_cntnr_itm_dtl.rcpt_dt_key, wrk_cntnr_itm_dtl.itm_key,
                      wrk_cntnr_itm_dtl.loc_key, wrk_cntnr_itm_dtl.appt_hdr_key,
                      wrk_cntnr_itm_dtl.asn_key, wrk_cntnr_itm_dtl.po_key,
                      wrk_cntnr_itm_dtl.crtn_key, wrk_cntnr_itm_dtl.cncpt_key,
                      wrk_cntnr_itm_dtl.expctd_itm_qty,
                      wrk_cntnr_itm_dtl.rcvd_itm_qty, wrk_cntnr_itm_dtl.excess_qty,
                      wrk_cntnr_itm_dtl.shrtage_qty,
                      wrk_cntnr_itm_dtl.itm_rcpt_wght,
                      wrk_cntnr_itm_dtl.wght_uom_cd, wrk_cntnr_itm_dtl.sec_grp_cd,
                      wrk_cntnr_itm_dtl.intgrtn_id, wrk_cntnr_itm_dtl.src_sys_cd,
                      wrk_cntnr_itm_dtl.btch_id, wrk_cntnr_itm_dtl.crt_dttm,
                      wrk_cntnr_itm_dtl.lst_modfd_dttm, wrk_cntnr_itm_dtl.itm_cd,
                      fct_asn_itm_dtl.splr_key, dim_itm_splr.itm_splr_key,
                      fct_cntnr_itm_dtl.cntnr_itm_dtl_key,
                      fct_cntnr_itm_dtl.btch_id, wrk_cntnr_itm_dtl.cntnr_dtl_key
                 FROM wrk_cntnr_itm_dtl LEFT OUTER JOIN fct_cntnr_itm_dtl
                      ON fct_cntnr_itm_dtl.intgrtn_id = wrk_cntnr_itm_dtl.intgrtn_id
                    AND fct_cntnr_itm_dtl.src_sys_cd = wrk_cntnr_itm_dtl.src_sys_cd
      /*LEFT OUTER JOIN FCT_ASN_ITM_DTL ON  WRK_CNTNR_ITM_DTL.ASN_KEY= FCT_ASN_ITM_DTL.ASN_KEY AND WRK_CNTNR_ITM_DTL.ITM_KEY=FCT_ASN_ITM_DTL.ITM_KEY */
                      LEFT OUTER JOIN
                      (SELECT   fct_asn_itm_dtl.asn_key, fct_asn_itm_dtl.itm_key,
                                fct_asn_itm_dtl.splr_key
                           FROM fct_asn_itm_dtl
                          WHERE fct_asn_itm_dtl.splr_key <> 0
                       GROUP BY fct_asn_itm_dtl.asn_key,
                                fct_asn_itm_dtl.itm_key,
                                fct_asn_itm_dtl.splr_key) fct_asn_itm_dtl
                      ON wrk_cntnr_itm_dtl.asn_key = fct_asn_itm_dtl.asn_key
                    AND wrk_cntnr_itm_dtl.itm_key = fct_asn_itm_dtl.itm_key
                      LEFT OUTER JOIN dim_splr
                      ON fct_asn_itm_dtl.splr_key = dim_splr.splr_key
                    AND dim_splr.eff_end_dt IS NULL
                      LEFT OUTER JOIN dim_itm_splr
                      ON dim_itm_splr.intgrtn_id =
                                 wrk_cntnr_itm_dtl.itm_cd || '~' || dim_splr.splr_cd
                    AND wrk_cntnr_itm_dtl.src_sys_cd = dim_itm_splr.src_sys_cd
                    AND dim_itm_splr.eff_end_dt IS NULL
      explain plan
      
      Plan hash value: 1817782238
      
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                            | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                     |                   |  8315K|  3592M|       | 22114   (1)| 00:04:26 |       |       |        |      |            |
      |   1 |  PX COORDINATOR                      |                   |       |       |       |            |          |       |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)                | :TQ10009          |  8315K|  3592M|       | 22114   (1)| 00:04:26 |       |       |  Q1,09 | P->S | QC (RAND)  |
      |*  3 |    HASH JOIN RIGHT OUTER BUFFERED    |                   |  8315K|  3592M|       | 22114   (1)| 00:04:26 |       |       |  Q1,09 | PCWP |            |
      |   4 |     PX RECEIVE                       |                   |    16M|   514M|       |   473   (2)| 00:00:06 |       |       |  Q1,09 | PCWP |            |
      |   5 |      PX SEND HASH                    | :TQ10007          |    16M|   514M|       |   473   (2)| 00:00:06 |       |       |  Q1,07 | P->P | HASH       |
      |   6 |       PX BLOCK ITERATOR              |                   |    16M|   514M|       |   473   (2)| 00:00:06 |       |       |  Q1,07 | PCWC |            |
      |*  7 |        TABLE ACCESS FULL             | DIM_ITM_SPLR      |    16M|   514M|       |   473   (2)| 00:00:06 |       |       |  Q1,07 | PCWP |            |
      |   8 |     PX RECEIVE                       |                   |  8315K|  3338M|       | 21638   (1)| 00:04:20 |       |       |  Q1,09 | PCWP |            |
      |   9 |      PX SEND HASH                    | :TQ10008          |  8315K|  3338M|       | 21638   (1)| 00:04:20 |       |       |  Q1,08 | P->P | HASH       |
      |  10 |       VIEW                           |                   |  8315K|  3338M|       | 21638   (1)| 00:04:20 |       |       |  Q1,08 | PCWP |            |
      |* 11 |        HASH JOIN RIGHT OUTER BUFFERED|                   |  8315K|  2046M|       | 21638   (1)| 00:04:20 |       |       |  Q1,08 | PCWP |            |
      |  12 |         PX RECEIVE                   |                   |   176K|  2756K|       |   246   (1)| 00:00:03 |       |       |  Q1,08 | PCWP |            |
      |  13 |          PX SEND HASH                | :TQ10005          |   176K|  2756K|       |   246   (1)| 00:00:03 |       |       |  Q1,05 | P->P | HASH       |
      |  14 |           PX BLOCK ITERATOR          |                   |   176K|  2756K|       |   246   (1)| 00:00:03 |       |       |  Q1,05 | PCWC |            |
      |* 15 |            TABLE ACCESS FULL         | DIM_SPLR          |   176K|  2756K|       |   246   (1)| 00:00:03 |       |       |  Q1,05 | PCWP |            |
      |  16 |         PX RECEIVE                   |                   |  8315K|  1919M|       | 21391   (1)| 00:04:17 |       |       |  Q1,08 | PCWP |            |
      |  17 |          PX SEND HASH                | :TQ10006          |  8315K|  1919M|       | 21391   (1)| 00:04:17 |       |       |  Q1,06 | P->P | HASH       |
      |* 18 |           HASH JOIN OUTER BUFFERED   |                   |  8315K|  1919M|       | 21391   (1)| 00:04:17 |       |       |  Q1,06 | PCWP |            |
      |  19 |            PX RECEIVE                |                   |  8315K|  1609M|       | 12239   (2)| 00:02:27 |       |       |  Q1,06 | PCWP |            |
      |  20 |             PX SEND HASH             | :TQ10003          |  8315K|  1609M|       | 12239   (2)| 00:02:27 |       |       |  Q1,03 | P->P | HASH       |
      |* 21 |              HASH JOIN OUTER BUFFERED|                   |  8315K|  1609M|       | 12239   (2)| 00:02:27 |       |       |  Q1,03 | PCWP |            |
      |  22 |               PX RECEIVE             |                   |  8315K|  1149M|       | 10393   (2)| 00:02:05 |       |       |  Q1,03 | PCWP |            |
      |  23 |                PX SEND HASH          | :TQ10000          |  8315K|  1149M|       | 10393   (2)| 00:02:05 |       |       |  Q1,00 | P->P | HASH       |
      |  24 |                 PX BLOCK ITERATOR    |                   |  8315K|  1149M|       | 10393   (2)| 00:02:05 |       |       |  Q1,00 | PCWC |            |
      |  25 |                  TABLE ACCESS FULL   | WRK_CNTNR_ITM_DTL |  8315K|  1149M|       | 10393   (2)| 00:02:05 |       |       |  Q1,00 | PCWP |            |
      |  26 |               PX RECEIVE             |                   |    48M|  2657M|       |  1841   (1)| 00:00:23 |       |       |  Q1,03 | PCWP |            |
      |  27 |                PX SEND HASH          | :TQ10001          |    48M|  2657M|       |  1841   (1)| 00:00:23 |       |       |  Q1,01 | P->P | HASH       |
      |  28 |                 PX BLOCK ITERATOR    |                   |    48M|  2657M|       |  1841   (1)| 00:00:23 |     1 |    12 |  Q1,01 | PCWC |            |
      |  29 |                  TABLE ACCESS FULL   | FCT_CNTNR_ITM_DTL |    48M|  2657M|       |  1841   (1)| 00:00:23 |     1 |    12 |  Q1,01 | PCWP |            |
      |  30 |            PX RECEIVE                |                   |    70M|  2638M|       |  9147   (1)| 00:01:50 |       |       |  Q1,06 | PCWP |            |
      |  31 |             PX SEND HASH             | :TQ10004          |    70M|  2638M|       |  9147   (1)| 00:01:50 |       |       |  Q1,04 | P->P | HASH       |
      |  32 |              VIEW                    |                   |    70M|  2638M|       |  9147   (1)| 00:01:50 |       |       |  Q1,04 | PCWP |            |
      |  33 |               HASH GROUP BY          |                   |    70M|  1150M|  1904M|  9147   (1)| 00:01:50 |       |       |  Q1,04 | PCWP |            |
      |  34 |                PX RECEIVE            |                   |    70M|  1150M|       |  6066   (1)| 00:01:13 |       |       |  Q1,04 | PCWP |            |
      |  35 |                 PX SEND HASH         | :TQ10002          |    70M|  1150M|       |  6066   (1)| 00:01:13 |       |       |  Q1,02 | P->P | HASH       |
      |  36 |                  PX BLOCK ITERATOR   |                   |    70M|  1150M|       |  6066   (1)| 00:01:13 |     1 |    12 |  Q1,02 | PCWC |            |
      |* 37 |                   TABLE ACCESS FULL  | FCT_ASN_ITM_DTL   |    70M|  1150M|       |  6066   (1)| 00:01:13 |     1 |    12 |  Q1,02 | PCWP |            |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("WRK_CNTNR_ITM_DTL"."SRC_SYS_CD"="DIM_ITM_SPLR"."SRC_SYS_CD"(+) AND
                    "DIM_ITM_SPLR"."INTGRTN_ID"(+)="WRK_CNTNR_ITM_DTL"."ITM_CD"||'~'||"DIM_SPLR"."SPLR_CD")
         7 - filter("DIM_ITM_SPLR"."EFF_END_DT"(+) IS NULL)
        11 - access("FCT_ASN_ITM_DTL"."SPLR_KEY"="DIM_SPLR"."SPLR_KEY"(+))
        15 - filter("DIM_SPLR"."EFF_END_DT"(+) IS NULL)
        18 - access("WRK_CNTNR_ITM_DTL"."ITM_KEY"="FCT_ASN_ITM_DTL"."ITM_KEY"(+) AND "WRK_CNTNR_ITM_DTL"."ASN_KEY"="FCT_ASN_ITM_DTL"."ASN_KEY"(+))
        21 - access("FCT_CNTNR_ITM_DTL"."SRC_SYS_CD"(+)="WRK_CNTNR_ITM_DTL"."SRC_SYS_CD" AND
                    "FCT_CNTNR_ITM_DTL"."INTGRTN_ID"(+)="WRK_CNTNR_ITM_DTL"."INTGRTN_ID")
        37 - filter("FCT_ASN_ITM_DTL"."SPLR_KEY"<>0)
      
      57 rows selected.
      Kindly suggest to tune this query.

      Thanks,

      Edited by: Mishra on Jan 25, 2013 2:42 AM
        • 1. Re: Query taking long time
          moreajays
          Hi Mishra,

          Create index on FCT_ASN_ITM_DTL table column SPLR_KEY & try/share the explain plan

          Thanks,
          Ajay More
          http://www.moreajays.com
          • 2. Re: Query taking long time
            JohnWatson
            Just one point: this could be sub-optimal,
            (SELECT   fct_asn_itm_dtl.asn_key, fct_asn_itm_dtl.itm_key,
                                      fct_asn_itm_dtl.splr_key
                                 FROM fct_asn_itm_dtl
                                WHERE fct_asn_itm_dtl.splr_key  0
                             GROUP BY fct_asn_itm_dtl.asn_key,
                                      fct_asn_itm_dtl.itm_key,
                                      fct_asn_itm_dtl.splr_key)
            if you look at the plan,
            32 |              VIEW                    |                   |    70M|  2638M|       |  9147   (1)| 00:01:50 |       |       |  Q1,04 | PCWP |            |
            |  33 |               HASH GROUP BY          |                   |    70M|  1150M|  1904M|  9147   (1)| 00:01:50 |       |       |  Q1,04 | PCWP |            |
            |  34 |                PX RECEIVE            |                   |    70M|  1150M|       |  6066   (1)| 00:01:13 |       |       |  Q1,04 | PCWP |            |
            |  35 |                 PX SEND HASH         | :TQ10002          |    70M|  1150M|       |  6066   (1)| 00:01:13 |       |       |  Q1,02 | P->P | HASH       |
            |  36 |                  PX BLOCK ITERATOR   |                   |    70M|  1150M|       |  6066   (1)| 00:01:13 |     1 |    12 |  Q1,02 | PCWC |            |
            |* 37 |                   TABLE ACCESS FULL  | FCT_ASN_ITM_DTL   |    70M|  1150M|       |  6066   (1)| 00:01:13 |     1 |    12 |  Q1,02 | PCWP |            |
            the optimizer thinks it will get 70m rows at id 37, and after the grouping it still has 70m rows. So the grouping is doing nothing, except taking resources and (much worse) preventing Oracle from merging the sub-query: it is forced to instantiated the sub-query as a view. Do you need that group by? Or was it inserted by some lazy programmer who doesn't understand his data?
            • 3. Re: Query taking long time
              ji li
              It seems you have an expensive full table scan on WRK_CNTNR_ITM_DTL.
              You might want to place an appropriate index on the column(s) in this section of your query.
              if you already have an index, check the table stats, and also, you can use a hint to force using the index.
              • 4. Re: Query taking long time
                riedelme
                Large number of bytes being read in parallel. Moderate cost which may/may not mean anything (probably doesn't since you said the query is slow). No parallel hint so the tables are probably set for parallism automaticdally. You don't seem to be reading the same tables more than once which is good but you are doing full table scans which may not be a problem since the SQL is using parallelism. Lots of outer joins which can affect performance. a GROUP BY subqery which might affect performance.

                How long does the SQL take? How long does the SQL take when run without the PQO? Are the reported statistics (particularly rows) in the plan accurate?

                The cost jumps at step 18 indicating a lot of expected work. The operation on this step was expected to be resource-intensive by the optimizer

                The view is using a lot of temp space. As per JohnWatson's suggestion look carefully at the view and what it is doing. Could a materialized view using automated query rewrite containing the subquery's data simplfy the query and run quicker at the back end?

                How does the query perform when run in serial?