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 around 30 mins

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

  1. malothgopal@gmail.com

    malothgopal@gmail.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    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 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
    Does the query taking long time to execute for the first time or all the time? Are there any views used in the query?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Provide the execution plan Oracle is using; we cannot assist in tuning this until we know what Oracle is doing with it now.
     
  4. malothgopal@gmail.com

    malothgopal@gmail.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Backlog:I:Started Processing 18-Jul-2012 11:05:43
    Backlog:I:Customer Level : [C]
    Backlog:I:Commercial Product Code : [UL65-TR]
    Backlog:I:So Sub Type : ['01','05']
    Backlog:I:Customer Part Nr Code : ['1277625369']
    Backlog:I:Customer Part Nr Descr : ['1277625369']
    Backlog:I:Backlog History Days : [15]
    Backlog:I:FlagSRC : [N ]
    Backlog:I:FlagSCC : [Y ]
    Backlog:I:sDelfType : [4]
    Backlog:I:sBothDelfor : [N]
    Backlog:I:sDrpFlag : [N]
    Backlog:I:sFlagBM : [N]
    Backlog:I:Cust Ship To : [4003554007]
    Backlog:I:Assocns : [1]
    Backlog:I:Adding Assocn : [BOS4]
    Backlog:I:Adding Assocn : [BOWW]
    Backlog:I:Adding Assocn : [V004]
    Backlog:I:Adding Assocn : []
    Backlog:I:WW_Flag : N
    Backlog:I:L_PartShipBlockConf [0]
    Backlog:I:L_PartShipBlockFlg []
    Backlog:I:Open Cursor [3]|18-Jul-2012 11:05:43
    Backlog:I:prod|ShipTo|0|1|2 [UL65-TR][4003554007][18-JUL-2012]['1277625369']['1277625369']
    Backlog:I:Opened Cursor 18-Jul-2012 11:07:47
    Backlog:I: rows fetched : 15
    Backlog:I: ....rows fetched : 15


    ===============================================================================================

    ShipInfo:I:Started : 18-Jul-2012 11:06:49
    ShipInfo:I:CP[LM2901DT]
    ShipInfo:I:ShpTo[8715534001]
    ShipInfo:I:CalcGitNettingDate[N]
    ShipInfo:I:ShipleFrameTypeCode[BAS]
    ShipInfo:I:DelfType[4]
    ShipInfo:I:CPNrCode[121-000313-0000]
    ShipInfo:I:Inserting Data into Temp Table[18-Jul-2012 11:06:49]
    ShipInfo:I:Insertion Completed[18-Jul-2012 11:09:43]...
    ShipInfo:I:preparing SQL for Cursor
    ShipInfo:I:B open cursor 18-Jul-2012 11:09:43
    ShipInfo:I:Making Cursor 2
    ShipInfo:I:eek:pen cursor 18-Jul-2012 11:09:43
    ShipInfo:I:Fetching cursor.
    ShipInfo:I:Rows Fetched 2


    ShipInfo:I:Returning Success 18-Jul-2012 11:19:44
    ShipInfo:I:Started : 18-Jul-2012 11:22:21
    ShipInfo:I:CP[LM2903DT]
    ShipInfo:I:ShpTo[8715534001]
    ShipInfo:I:CalcGitNettingDate[N]
    ShipInfo:I:ShipleFrameTypeCode[BAS]
    ShipInfo:I:DelfType[4]
    ShipInfo:I:CPNrCode[121-000777-0000]
    ShipInfo:I:Inserting Data into Temp Table[18-Jul-2012 11:22:22]
    ShipInfo:I:Insertion Completed[18-Jul-2012 11:24:48]...
    ShipInfo:I:preparing SQL for Cursor
    ShipInfo:I:B open cursor 18-Jul-2012 11:24:48
    ShipInfo:I:Making Cursor 2
    ShipInfo:I:eek:pen cursor 18-Jul-2012 11:24:48


    ShipInfo:I:Started : 18-Jul-2012 11:25:36
    ShipInfo:I:CP[M24C16-WMN6TP]
    ShipInfo:I:ShpTo[8017283001]
    ShipInfo:I:CalcGitNettingDate[N]
    ShipInfo:I:ShipleFrameTypeCode[BSH]
    ShipInfo:I:DelfType[4]
    ShipInfo:I:CPNrCode[1819-0181-LF-2]
    ShipInfo:I:Inserting Data into Temp Table[18-Jul-2012 11:25:36]
    ShipInfo:I:Insertion Completed[18-Jul-2012 11:27:45]...
    ShipInfo:I:preparing SQL for Cursor
    ShipInfo:I:B open cursor 18-Jul-2012 11:27:45


    EXEC SQL INSERT INTO GLB_TMP_SHIPINFO
    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.CUSTOMER_SHIP_TO__CODE = :L_CustomerShipToId
    AND SH.ID = SI.SHPH_ID
    AND SI.COMMERCIAL_PRODUCT__CODE = :L_CommercialProductCode
    AND SSI.ID = SS.SO_SUB_ITEM__ID
    AND SS.SO__CODE = SI.OC_NBR
    AND SS.ITEM_NR = SI.ITEM_NBR;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have NOT posted any Oracle query plan for this query -- we CANNOT assist you without such information. This is the second time you've been asked to POST the execution plan for the above query; you can find it in a round-about way following these instructions (for releases 10.2 and later):

    Code (SQL):
     
    COLUMN sql_id new_value sqlid
     
    SELECT sql_id FROM v$sql_text WHERE sql_text LIKE '%SELECT SH.SHP_NBR "SHP_NBR"%';
     
    @plan_query &sqlid
     
    where plan_query.sql would be:

    Code (SQL):
     
    COLUMN "Operation" format A40
    COLUMN "Object Name" format A30
    COLUMN "PStart" format A6
    COLUMN "PStop" format A6
    COLUMN "I" format A3
    COLUMN ACCESS_PREDICATES format A80
    COLUMN FILTER_PREDICATES format A80
    SET LINES 9999
    SET pages 99
    SET trimspool ON
    SET verify off
    SET heading ON
    SELECT  /*+ NO_MERGE */ rownum  ||
    decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
            substr(lpad(' ',2*(depth-1)) || operation,1,20) ||
            decode(options,NULL,'',' (' || options || ')') "Operation",
            substr(object_name,1,30) "Object Name",
            cardinality "# Rows",
            bytes,
            cost,
            partition_start "PStart",
            partition_stop "PStop"
      FROM (SELECT * FROM v$sql_plan WHERE sql_id = '&&1') a
      START WITH id = 0
      CONNECT BY prior id = parent_id
          AND prior nvl(sql_id, 0 ) = nvl(sql_id, 0 )
      ORDER BY id, POSITION
    /
    SET heading off
    SELECT 'Access Predicates .....' FROM dual;
    SELECT * FROM (
    SELECT  /*+ NO_MERGE */ rownum  ||
    decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
        decode(access_predicates,NULL,'###',access_predicates) "ACCESS_PREDICATES"
      FROM (SELECT * FROM v$sql_plan WHERE sql_id = '&&1') a
      START WITH id = 0
      CONNECT BY prior id = parent_id
          AND prior nvl(sql_id, 0 ) = nvl(sql_id, 0 )
      ORDER BY id, POSITION
    ) WHERE access_predicates <> '###'
    /
    SET heading off
    SELECT 'Filter predicates .....' FROM dual;
    SELECT * FROM (
    SELECT  /*+ NO_MERGE */ rownum  ||
    decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
        decode(filter_predicates,NULL,'###',filter_predicates) "FILTER_PREDICATES"
      FROM (SELECT * FROM v$sql_plan WHERE sql_id = '&&1') a
      START WITH id = 0
      CONNECT BY prior id = parent_id
          AND prior nvl(sql_id, 0 ) = nvl(sql_id, 0 )
      ORDER BY id, POSITION
    ) WHERE filter_predicates <> '###'
    /
     
    Post the output from that to this thread so we can see what Oracle is currently doing. Only then can we help you improve the performance.