3 Replies Latest reply: Jul 29, 2013 9:22 AM by Mishra RSS

    need to tune sql

    Mishra

      Hi,

       

      Query:

      SELECT   dim.dim_itm_loc_prcng_expld_key AS dim_itm_loc_prcng_expld_key,

               dim.btch_id AS btch_id, dim.intgrtn_id AS intgrtn_id,

               dim.src_sys_cd AS src_sys_cd

          FROM dim_itm_loc_prcng_expld dim,wrk_dim_itm_loc_prcng_exp_id wrk

         WHERE  dim.src_sys_cd = wrk.src_sys_cd and dim.intgrtn_id = wrk.intgrtn_id

      ORDER BY intgrtn_id, src_sys_cd, dim_itm_loc_prcng_expld_key, btch_id

       

      [code]

       

      PLAN_TABLE_OUTPUT

       

       

      Plan hash value: 816184190

       

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

      | Id  | Operation                  | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

      |   0 | SELECT STATEMENT           |                              |   127M|  8419M|       |   260K  (2)| 00:52:09 |       |       |        |      |            |

      |   1 |  PX COORDINATOR            |                              |       |       |       |            |          |       |       |        |      |            |

      |   2 |   PX SEND QC (ORDER)       | :TQ10003                     |   127M|  8419M|       |   260K  (2)| 00:52:09 |       |       |  Q1,03 | P->S | QC (ORDER) |

      |   3 |    SORT ORDER BY           |                              |   127M|  8419M|    10G|   260K  (2)| 00:52:09 |       |       |  Q1,03 | PCWP |            |

      |   4 |     PX RECEIVE             |                              |   127M|  8419M|       |   212K  (2)| 00:42:27 |       |       |  Q1,03 | PCWP |            |

      |   5 |      PX SEND RANGE         | :TQ10002                     |   127M|  8419M|       |   212K  (2)| 00:42:27 |       |       |  Q1,02 | P->P | RANGE      |

      |*  6 |       HASH JOIN BUFFERED   |                              |   127M|  8419M|    88M|   212K  (2)| 00:42:27 |       |       |  Q1,02 | PCWP |            |

      |   7 |        BUFFER SORT         |                              |       |       |       |            |          |       |       |  Q1,02 | PCWC |            |

      |   8 |         PX RECEIVE         |                              |   127M|  2806M|       |   170K  (2)| 00:34:07 |       |       |  Q1,02 | PCWP |            |

      |   9 |          PX SEND HASH      | :TQ10000                     |   127M|  2806M|       |   170K  (2)| 00:34:07 |       |       |        | S->P | HASH       |

      |  10 |           TABLE ACCESS FULL| WRK_DIM_ITM_LOC_PRCNG_EXP_ID |   127M|  2806M|       |   170K  (2)| 00:34:07 |       |       |        |      |            |

      |  11 |        PX RECEIVE          |                              |   179M|  7881M|       | 26856   (1)| 00:05:23 |       |       |  Q1,02 | PCWP |            |

      |  12 |         PX SEND HASH       | :TQ10001                     |   179M|  7881M|       | 26856   (1)| 00:05:23 |       |       |  Q1,01 | P->P | HASH       |

      |  13 |          PX BLOCK ITERATOR |                              |   179M|  7881M|       | 26856   (1)| 00:05:23 |     1 |    12 |  Q1,01 | PCWC |            |

      |  14 |           TABLE ACCESS FULL| DIM_ITM_LOC_PRCNG_EXPLD      |   179M|  7881M|       | 26856   (1)| 00:05:23 |     1 |   180 |  Q1,01 | PCWP |            |

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

       

      Predicate Information (identified by operation id):

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

       

         6 - access("DIM"."INTGRTN_ID"="WRK"."INTGRTN_ID" AND "DIM"."SRC_SYS_CD"="WRK"."SRC_SYS_CD")

       

      Note

      -----

         - dynamic sampling used for this statement (level=6)

         - automatic DOP: skipped because of IO calibrate statistics are missing

       

      [/code]

       

      How I can tune this query to reduce the cost?

       

      Thanks,

        • 1. Re: need to tune sql
          vr849uce

          Hi Mishra,

           

          Think it will be better if you can provide some more details about the underlying tables of the query, like

           

          a.) The Indexes  on the tables, since from the query it appears that you are doing full table scan, which unless your query is not intended to bring most of the data from the underlying tables is not good.

          b.) The freshness of stats on the table, in case the stats are not being gathered on these table think it will be a good idea to collect stats using DBMS_STATS package.

           

          Cheers

          Vishad

          • 2. Re: need to tune sql
            SomeoneElse

            You're not doing any filtering in your query.  Just joining in the where clause.

             

            So unless one table is highly selective (and it doesn't appear to be according to the plan), I would expect two full table scans, a join and a sort.


            And you're selecting (supposedly) 127 million rows.  Where are these rows going?  Across the network?  Scrolling by on your laptop?

            • 3. Re: need to tune sql
              Mishra

              Hi Vishad,

               

              Table: wrk_dim_itm_loc_prcng_exp_id

              WRK_DIM_ITM_LOCEXP_IDX (INTGRTN_ID,SRC_SYS_CD )


              Table : dim_itm_loc_prcng_expld


              U_DIM_ITM_LOC_PRCNGS_1 (SRC_SYS_CD,INTGRTN_ID). 


              Here I Observed index columns order. I changed the order as similar to other table but no luck.


              Any clue?


              Thanks,