This discussion is archived
3 Replies Latest reply: Jul 29, 2013 7:22 AM by Mishra RSS

need to tune sql

Mishra Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points