Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Question Unanswered: Procedure Issues - Urgent

    Hi,

    We have a procedure that was running fine once we dropped and recreated its performance dropped drastically from milli seconds to 20 seconds, this in turn is causing the application slow down.

    Here is the procedure:

    CREATE PROCEDURE "MMS"."SP_EQIP_LOC_VIS_GBL_POS" (
    IN "@MACHINES_ID" VARCHAR(15000),
    IN "@ORG_ID" INTEGER,
    IN "@START_TIME" TIMESTAMP,
    IN "@END_TIME" TIMESTAMP )
    SPECIFIC "SP_EQIP_LOC_VIS_GBL_POS"
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    NOT DETERMINISTIC
    NO EXTERNAL ACTION
    READS SQL DATA
    OLD SAVEPOINT LEVEL
    BEGIN
    DECLARE RES CURSOR WITH RETURN FOR

    WITH TESTDATA(ITEM_NUMBERS) AS (VALUES CAST(@MACHINES_ID AS CLOB(15 K)))
    ,RECURSIVE_CTE(K, MACHID, ITEM_NUMBERS) AS
    (
    SELECT 0
    , CAST('' AS VARCHAR(10))
    , ITEM_NUMBERS||','
    FROM TESTDATA
    UNION ALL
    SELECT K
    , CAST(SUBSTR(ITEM_NUMBERS, 1, N-1) AS VARCHAR(10))
    , SUBSTR(ITEM_NUMBERS, N+1)
    FROM (
    SELECT K + 1 AS K , LOCATE(',', ITEM_NUMBERS) AS N , ITEM_NUMBERS
    FROM RECURSIVE_CTE WHERE K < 1000000
    AND LENGTH(ITEM_NUMBERS) > 0
    ) S
    )


    SELECT DISTINCT C.ID as ID, C.EQIP_ID as EQIP_ID, C.CALL_IN_TS as CALL_IN_TS, C.GTWY_TYP_ID as GTWY_TYP_ID, C.GBL_POS_ID as GBL_POS_ID,
    C.TRM_ID as TRM_ID,GBLPOS.LAT_QTY as LAT_QTY,GBLPOS.LONG_QTY as LONG_QTY--,GBLPOS.ALT_QTY as ALT_QTY,GBLPOS.GPS_FIX_TS as GPS_FIX_TS
    FROM (SELECT A.MACH_ID,
    A.CO_ID,
    A.VIS_BEG_TS AS VIS_BEG_TS,
    COALESCE (
    VIS_END_TS,
    (SELECT current timestamp - current timezone
    FROM sysibm.sysdummy1))
    AS VIS_END_TS
    FROM SHRDM.MACH_CO_VIS A
    WHERE A.mach_co_id IN (SELECT INTEGER(MACHID) FROM RECURSIVE_CTE WHERE K > 0 )) B,
    MMS.EQIP_CALL_IN_INFO C, MMS.GBL_POS GBLPOS
    WHERE B.MACH_ID = C.EQIP_ID
    AND C.CALL_IN_TS >= B.VIS_BEG_TS
    AND C.CALL_IN_TS <= B.VIS_END_TS
    AND C.CALL_IN_TS >= TIMESTAMP(@START_TIME)
    AND C.CALL_IN_TS <= TIMESTAMP(@END_TIME)
    AND GBLPOS.GBL_POS_ID=C.GBL_POS_ID
    ORDER BY C.EQIP_ID, C.CALL_IN_TS DESC
    WITH UR FOR READ ONLY;

    OPEN RES;
    RETURN;

    END;


    Please suggest any changes also it will be helpful if you can help identify the bottlenecks for any procedures in general and how to go about debugging and fine tuning Procedure.

    If i would like to retrieve SQL from the procedure to find explain how do i do for any procedure is there a tool or something that can be done.

    So far we have run reorg, runstats and rebind also rebind with reopt on the base tables and packages associated to the above procedure.

    Appreciate your help...

    Kavitha

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    My guess is that the access plan changed when you recreated the stored procedure. Determine the package the was created when you created the stored procedure and use db2expln to show the access plan.

    Andy

  3. #3
    Join Date
    Apr 2011
    Posts
    3

    Urgent

    We have rebinded with re opt to ensure the access plan is good.

    Kavitha

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Have you looked at the access plan to make sure it is OK?

    Andy

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    Access plan looks good.

    Are there any options or parameters on Server/Database that we can set ?

    What is the general rule of thumb to debug procedures or in general performance issues.

    Any document any one can provide will be great help.

    Thanks,
    Kavitha

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Andy pointed you in the right direction for the issue you had posted.

    If you are looking for generic performance tuning guidelines, developerworks will be a good place to start.

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •