1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please Help anybody. Below query is taking long time to run(Exraction)

Discussion in 'SQL PL/SQL' started by malothgopal@gmail.com, Aug 6, 2012.

Thread Status:
Not open for further replies.
  1. malothgopal@gmail.com

    malothgopal@gmail.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I am new to tunnings
    please help any body below query taking long time to run( extraction). How to reduce the time of this query ?


    SELECT SH.SHP_NBR "SHP_NBR"
    , SS.ID "ID"
    , TO_CHAR(SSI.REQUIRED_DATE, 'DD-MON-YYYY') "REQUIRED_DATE"
    , TO_CHAR(SI.SHIPPED_QTY) "SHIPPED_QTY"
    , NULL "INV_NBR"
    , NULL "LOCAL_DATE"
    , SI.CUSTOMER_PART_NR__CODE "CUSTOMER_PART_NR__CODE"
    , DECODE(SH.SHP_STATUS,'CL',TO_CHAR(SH.SHP_STATUS_DATE_MODIFIED , 'DD-MON-YYYY'),'') "SHP_STATUS_DATE_MODIFIED"
    , SH.SHP_STATUS "SHP_STATUS"
    , SI.ITEM_NBR "ITEM_NBR"
    , NULL "INVOICE_TYPE"
    , SI.OC_NBR "OC_NBR"
    , DECODE(SH.SHP_STATUS,'CL',TO_CHAR(SH.SHP_STATUS_DATE_MODIFIED , 'YYYY-MM-DD'),'') "SHP_STATUS_DATE_MODIFIED1"
    , ON_CONSIGNMENT_FLAG "ON_CONSIGNMENT_FLAG"
    , SH.GMT_CREATE_DATE "GMT_CREATE_DATE"
    FROM SHP_HEADERS SH,
    SHP_ITEMS SI,
    SO_SUB_ITEM SSI,
    SO_SCHEDULED SS
    WHERE SH.ID = SI.SHPH_ID
    AND SSI.ID = SS.SO_SUB_ITEM__ID
    AND SS.SO__CODE = SI.OC_NBR
    AND SS.ITEM_NR = SI.ITEM_NBR
    AND ROWNUM < 10;



    EXPLAIN PLAN OF THIS QUERY IS :



    Plan hash value: 830561891

    -------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
    -------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 9 | 1350 | | 217K (1)| 00:43:29 | | | |
    |* 1 | COUNT STOPKEY | | | | | | | | | |
    | 2 | PX COORDINATOR | | | | | | | | | |
    | 3 | PX SEND QC (RANDOM) | :TQ10006 | 7814K| 1117M| | 217K (1)| 00:43:29 | Q1,06 | P->S | QC (RAND) |
    |* 4 | COUNT STOPKEY | | | | | | | Q1,06 | PCWC | |
    |* 5 | HASH JOIN BUFFERED | | 7814K| 1117M| 65M| 217K (1)| 00:43:29 | Q1,06 | PCWP | |
    | 6 | BUFFER SORT | | | | | | | Q1,06 | PCWC | |
    | 7 | PX RECEIVE | | 5709K| 196M| | 52027 (1)| 00:10:25 | Q1,06 | PCWP | |
    | 8 | PX SEND HASH | :TQ10001 | 5709K| 196M| | 52027 (1)| 00:10:25 | | S->P | HASH |
    | 9 | TABLE ACCESS FULL | SHP_HEADERS | 5709K| 196M| | 52027 (1)| 00:10:25 | | | |
    | 10 | PX RECEIVE | | 7785K| 846M| | 150K (1)| 00:30:06 | Q1,06 | PCWP | |
    | 11 | PX SEND HASH | :TQ10005 | 7785K| 846M| | 150K (1)| 00:30:06 | Q1,05 | P->P | HASH |
    |* 12 | HASH JOIN BUFFERED | | 7785K| 846M| 85M| 150K (1)| 00:30:06 | Q1,05 | PCWP | |
    | 13 | BUFFER SORT | | | | | | | Q1,05 | PCWC | |
    | 14 | PX RECEIVE | | 7592K| 253M| | 75256 (1)| 00:15:04 | Q1,05 | PCWP | |
    | 15 | PX SEND HASH | :TQ10000 | 7592K| 253M| | 75256 (1)| 00:15:04 | | S->P | HASH |
    | 16 | TABLE ACCESS FULL | SHP_ITEMS | 7592K| 253M| | 75256 (1)| 00:15:04 | | | |
    | 17 | PX RECEIVE | | 7988K| 601M| | 62338 (1)| 00:12:29 | Q1,05 | PCWP | |
    | 18 | PX SEND HASH | :TQ10004 | 7988K| 601M| | 62338 (1)| 00:12:29 | Q1,04 | P->P | HASH |
    |* 19 | HASH JOIN BUFFERED | | 7988K| 601M| 52M| 62338 (1)| 00:12:29 | Q1,04 | PCWP | |
    | 20 | PX RECEIVE | | 5956K| 142M| | 17193 (1)| 00:03:27 | Q1,04 | PCWP | |
    | 21 | PX SEND HASH | :TQ10002 | 5956K| 142M| | 17193 (1)| 00:03:27 | Q1,02 | P->P | HASH |
    | 22 | PX BLOCK ITERATOR | | 5956K| 142M| | 17193 (1)| 00:03:27 | Q1,02 | PCWC | |
    | 23 | TABLE ACCESS FULL| SO_SUB_ITEM | 5956K| 142M| | 17193 (1)| 00:03:27 | Q1,02 | PCWP | |
    | 24 | PX RECEIVE | | 7984K| 411M| | 36273 (1)| 00:07:16 | Q1,04 | PCWP | |
    | 25 | PX SEND HASH | :TQ10003 | 7984K| 411M| | 36273 (1)| 00:07:16 | Q1,03 | P->P | HASH |
    | 26 | PX BLOCK ITERATOR | | 7984K| 411M| | 36273 (1)| 00:07:16 | Q1,03 | PCWC | |
    | 27 | TABLE ACCESS FULL| SO_SCHEDULED | 7984K| 411M| | 36273 (1)| 00:07:16 | Q1,03 | PCWP | |
    -------------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter(ROWNUM<10)
    4 - filter(ROWNUM<10)
    5 - access("SH"."ID"="SI"."SHPH_ID")
    12 - access("SS"."SO__CODE"="SI"."OC_NBR" AND "SS"."ITEM_NR"="SI"."ITEM_NBR")
    19 - access("SSI"."ID"="SS"."SO_SUB_ITEM__ID")
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Duplicate post.

    You have already posted this question here. Why you have posted the same question again and again. This kind of things lead the people in wrong way to search the things.

    Duplicate post link
     
Thread Status:
Not open for further replies.